CODEWITHSUNDEEP
sqlsql-servervisual-studio-2012ssis
BamBamBeano
BamBamBeano

Reputation: 464

DTSX package runs in Visual Studio but not when called from a Database Job

I have an SSIS package that takes database backups and it runs fine in visual studio but when executed from a database job it fails with the following error. The part I can't understand is that 'GS\BOS-DBMONITOR$' isn't a user. 'BOS-DBMONITOR' is the server name.

Error:

Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 10:58:03 AM Error: 2014-07-30 10:58:04.33 Code: 0xC0024104 Source: Back Up Database Task Description: The Execute method on the task returned error code 0x80131904 (Login failed for user 'GS\BOS-DBMONITOR$'.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:58:03 AM Finished: 10:58:04 AM Elapsed: 0.593 seconds. The package execution failed. The step failed.

Upvotes: 1

Views: 1901

Answers (1)

billinkc
billinkc

Reputation: 61249

SQL Server Agent is the scheduler that comes with SQL Server. The first line of your error, Executed as user: NT Service\SQLSERVERAGENT specifies that your instance is running as the system user - not a "real" account. This machine is then trying to reach out to another computer and asks to log in to that SQL Server instance. SQL Server says "I'm not allowed to talk to strangers and GS\BOS-DBMONITOR$', you're strange."

How do you resolve it?

You need to let the foreign machines know about the account that's going to talk to them.

  1. Use SQL Server Configuration Manager to change the service account from the the local account to a domain account MyDomain\NonExpiringAccount
  2. Create a Credentialed user in the SQL Server instance on BOS-DBMONITOR and then assign them as a proxy for executing this, and any similar, SSIS packages. This allows the credentialed users' account to be presented to the foreign hosts. This has less potential impact on existing operations than changing the service account but requires more work
  3. There's probably some voodoo you can work on all the servers you need to take a backup on to allow this user to log on but I wouldn't even go down that road. It's going to be work you have to perform on a per box basis and there's probably some security implications that go over my head

Upvotes: 2

Related Questions