SSIS Error – Failed to decrypt protected XML node “Password” with error 0x8009000B “Key not valid for use in specified state

[ZappySys]

Problem

When user designed packages on workstation using default settings all sensitive information (i.e. Password Property of connection) will be encrypted using user key. When you deploy package to other machine (E.g. Production) and run under different account you may get following error.

Failed to decrypt protected XML node “Password” with error 0x8009000B “Key not valid for use in specified state.”

Possible Cause

When you get above error most likely its due to package PackageProtectionLevel property is set to EncryptSensitiveWithUserKey (This is default). When you use default protection level any sensitive attribute (e.g. Password, ProxyPassword, SecretKey) in Connection Manager or Task is encrypted using User Key (Person who saved package last time). Because of user specific encryption key is used… when its opened / executed by other user you will see above error.

Solution

To fix above error you can perform following steps

For SSIS 2012 / 2014 / 2016 (use any of the following solutions)

Same as above with following extra ways

SSIS 2012 and higher version supports Project Deployment Mode. If you are using that then you can change Connection Attributes  at runtime by supplying Parameters via SSIS Environment.

Check this article to learn how to pass values from parameters at runtime
 

For SSIS 2005 / 2008 / 2008 R2 (use any of the following solutions)

1. Change PackageProtectionLevel to DoNotSaveSensitive and supply Sensitive information such as Passwords via Configuration File. You can set supply full connectionstring or just Sensitive attribute from Config file.
2. Change PackageProtectionLevel to EncryptSensitiveWithPassword or EncryptAllWithPassword and set package password. When this approach is used you have to execute package by supplying /De command line switch as below
   dtexec /De MyPassword123 /F c:\ssis\mypackage.dtsx
   If you are executing packages via SQL Agent Job then there is an option to edit default DTEXEC command line in the user interface.

 

[Author]

Posts