Error while executing SSIS package from JOB

Question

Message

Executed as user: UKDBT91DB05V\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 14:58:34 Error: 2013-04-01 14:58:34.45 Code: 0xC0016016
Source: Description: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2013-04-01 14:58:34.76 Code: 0xC001000E Source: ReceiveDGDinformation Description: The connection "stock" is not found. This error is thrown by Connections collection when the specific connection element is not found. End Error Error: 2013-04-01 14:58:34.80 Code: 0xC0202009 Source: ReceiveDGDinformation Connection manager "Stock" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E4D Description: "Login failed for user 'DOTCOM\UKDBT91DB05V$'.". End Error Error: 2013-04-01 14:58:34.80 Code: 0xC020801C Source: Populate Staginng Table Populate DGD Staging table [34] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Stock" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2013-04-01 14:58:34.80 Code: 0xC0047017 Source: Populate Staginng Table SSIS.Pipeline Description: component "Populate DGD Staging table" (34) failed validation and returned error code 0xC020801C. End Error Error: 2013-04-01 14:58:34.80 Code: 0xC004700C Source: Populate Staginng Table SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2013-04-01 14:58:34.80 Code: 0xC0024107 Source: Populate Staginng Table Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 14:58:34 Finished: 14:58:34 Elapsed: 0.624 seconds. The package execution failed. The step failed.

Meaningful bits of the error message

• Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error
• Login failed for user 'DOTCOM\UKDBT91DB05V$'

Answer 1

This is probably a problem with your ProtectionLevel setting and how you are configuring the job.

If ProtectionLevel is at the default "EncryptSensitiveWithUserKey" setting, then the development environment is encrypting things like your connection string database password using a key that depends on the user who is doing the development. After you deploy it to the production server and schedule a SQL Server Agent job, it will most likely run under a different user account, and then it will be unable to decrypt the database password. You will get this error.

One possibility would be to make sure the development user and the job execution user are the same user account, but this is not a good idea. Usually you want your production user accounts to be separate from your development user accounts.

Instead, you should set ProtectionLevel to "DontSaveSensitive". Then the password won't get saved with the SSIS package at all. You create a configuration file for the connection string, but the password won't get saved to the configuration file either. You will have to edit the configuration file manually if you want it to include a password. But the best way to do this is to configure the password when you schedule the job that executes the SSIS package. That keeps the password in a safe place, and it isn't floating around all over the place with the SSIS package.

Some useful links here and here.

Good luck!