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

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

This topic contains 0 replies, has 1 voice, and was last updated by  ZappySys 3 years, 9 months ago.

Viewing 1 post (of 1 total)
  • Author
    Posts
  • #2021

    ZappySys
    Keymaster

    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.

     

Viewing 1 post (of 1 total)

You must be logged in to reply to this topic.