Friday 13 July 2012

Failed to decrypt protected XML node DTS:Password with error 0x8009000B

Unable to use a SQL job to execute a package designed in SSIS however it runs fine in Integration Services.  It reports the following error:
"Error Loading <>.dtsx: Failed to decrypt protected XML node "DTS:Password" 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 "

Problem was due to the username set against the SQL Server Agent being different to the username used to designed the SSIS package.  To resolve this we left the Package default protectionlevel to "EncryptSensitiveWithUserKey".  When adding the SQL Server agent job we should set the protection level to be "Rely on Server Storage and roles for access control"

The following website was an excellent resource and help me understand the error:

http://www.mssqltips.com/sqlservertip/2091/securing-your-ssis-packages-using-package-protection-level/

No comments:

Post a Comment