Powered By Blogger

Monday, June 1, 2009

SSIS package fails when scheduled as SQL server agent job

Problem: When run through the BIDS or Visual Studio, the SSIS package runs well. But when scheduled as a SQL agent job, the SSIS package fails with the error message:
" 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. "

Solution: It took me a while to figure out the problem and the same was mentioned at SSC.
The standard security on an SSIS package is "EncryptSensistiveWithUserKey" which only allows the user that created it to be able to run in as long as there is sensitive information (passwords) stored within the package.

A workaround that I am using is that I changed the package security to "EncryptAllWithPassword" which protects the entire package with a password. When you set the package as a job step, go into the properties of that job step, click on the "Command Line" tab and you should be prompted for the password. After you enter the password, the /DECRYPT switch should show up in the command line and the job should then be able to run under a different user.

Users won't be able to see the password because they will need to enter it before viewing the command line for the job step.

(The issue is addressed nicely with many inputs at http://www.sqlservercentral.com/Forums/Topic349385-148-1.aspx)

No comments:

Post a Comment

This blog is for SQL server and ArcSDE administration. Thanks for visiting !

Disclaimer: The contents provided on this blog are as is and the poster assumes no responsibility in case of any loss of data. Please test thoroughly the solution or codes provided in your environment.