none
SSIS Package Protection Level Errors - decrypt protected XML node "DTS:Password" with error 0x8009000B ; failed with error code 0xC0202009. RRS feed

  • Question

  • Error 7 Error loading GetData.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.   c:\Packages\GetData.dtsx 1 1 

    Error 8 Validation error. Data Flow Task: OLEDB Source [5310]: The AcquireConnection method call to the connection manager "SourceServer" failed with error code 0xC0202009. GetData.dtsx 0 0 

    Environment I need to execute - BIDS
    Have Solution, with files linked with VSS. When I click on package, aforesaid errors show up and package opens in designed.

    Protection Level : Encrypt Sesitive with User Key
    This means it'd run only on machine and by user where and who created respectively.

    So I changed it to Encrypt Sesitive with Passoword and provided Passoword. No luck

    So I changed it to Do not save sensitive, as anyways in the connection manager, it has Expression saying @Variablename (with appropriate correct syntax), and variable is mapped to config file. I tired with Config file and also by hardcoding the value in the connection manager's connection string after removing the variable name, also tried by hardcoding value in variable value and disabling config file.

    Nothing works.

    I read 3 threads discussing this issue by others, and they all focus on SQL Server Agent, creating proxy accounts, etc. Within BIDS, I think I've exhausted all options. Kindly help someone!

     

    Monday, November 30, 2009 1:52 PM

All replies

  • How are you passing the credentials to connect to server? Is "Enable Package Configuration" box checked in SSIS-->Package Configurations?
    Nitesh Rai- Please mark the post as answered if it answers your question
    Monday, November 30, 2009 2:11 PM
  • Yes, it is checked. Moreover, I passed the credentials to connect to the server by :

    1) Via package config
    2) Disabling config and hardcoding in connection manager
    3) Discabling config and hardcoding in variable

    All 3 I tried.
    Monday, November 30, 2009 2:14 PM
  • Hi sqlserverdotnet,

    This behavior occurs if the value of the ProtectionLevel property in the SSIS package is set to provide the maximum amount of protection for the Password property in the SSIS package. By default, the value of the ProtectionLevel property is set to EncryptSensitiveWithUserKey. The EncryptSensitiveWithUserKey value encrypts all the properties of the SSIS package that are considered sensitive, such as the Password property. When the same user account and the same computer that were used to create the SSIS package are used to run the SSIS package, the SSIS package automatically decrypts, and no error message is generated. However, when a different user account or a different computer is used to run the SSIS package, the EncryptSensitiveWithUserKey value of the ProtectionLevel property is engaged, and the Password property of the SSIS package remains encrypted. When this occurs, an error message is generated.

    Please change the ProtectionLevel Property and that resolved this issue.

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    Wednesday, December 2, 2009 9:01 AM
    Moderator
  • Hi Jin, I am in same situation. The previous developer had used EncryptSensetiveWithUserKey and now that he no longer works in same organisation I am struggling to find solution for this. I can open all the other files fine, but its only the .dtsx that doesn't open. Is there any possible way to recover this.

     

    And on other note, I don't understand why such property is set by default? I think because it is something set by default some developers do not pay attention to it....

    Thursday, September 9, 2010 6:35 PM
  • Unfortunately, no - it's encrypted, and "a solution" for this would be to break the encryption.

    You need to open the package, re-enter all the sensitive information, change the ProtectionLevel if you wish, and save the package again.

    Yes, it can seem crazy that this is the default option.  But being the default makes it easy to safeguard sensitive information while still making it easy for the developer to get on with their job.  When (or if) the package makes it into production, this setting becomes apparent - although not as obviously as I'd like.  In shops with more formalized deployment processes, packages would go through a process of changing this property and handling sensitive information differently.


    Todd McDermid's Blog Talk to me now on
    Thursday, September 9, 2010 6:49 PM
    Moderator
  • Hi, I have the same problem.

    You suggest to change the ProtectionLevel Property: in which value do I have to change?

    Now I Have " EncryptSensitiveWithUserKey ".

     

    Thanks a lot,


    Thursday, December 16, 2010 7:36 AM
  • You need to read the docs on the ProtectionLevel property to understand what the other settings do for you.  It doesn't make sense for me to cut and paste that stuff here.
    Todd McDermid's Blog Talk to me now on
    Friday, December 17, 2010 12:08 AM
    Moderator