none
Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state

    Question

  • we have developed packages to do the followings

    Extract data from DB2 Source and put it in MS Sql Server 2008 database (Lets Say DatabaseA).From MS Sql Server 2008 (DatabaseA) we will process the data and place it in another database MS Sql Server 2008 (DatabaseB)

    We have created packages in BIDS..We created datasource connection in Datasource folder in BIDS..Which has DB2 Connection and both Ms Sql Server connection (Windows authentication-Let say its pointing to the server -ServerA which has DatabaseA and DatabaseB).The datasource connections will be used in packages during development.

    For deployment we have created Package Configuration which will have both DB2 Connection and MS SqlServer connection in the config

    We deployed the packages in different MS SqlServer by changing the connectionstring in the config for DB2 and MS SqlServer...

    While runing the package we are getting the following error message

    Code: 0xC0016016     Source:       Description: 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.


    ilikemicrosoft

    Wednesday, August 01, 2012 8:24 AM

Answers

  • Hi Surendiran,

    This is because the package has been created by somebody else and the package is being deployed under sombody else's account. e.g. If you are the creator then the package is encryption set according to your account and the package setup in SQL server is under a different user account.

    This happens because the package protection level is set to EncryptSensitiveWithUserKey which encrypts sensitive information using creator's account name.

    As a solution:

    Either you have to set up the package in SQL server under your account (which some infrastructures do not allow).

    OR

    Set the package property Protection Level to "DontSaveSensitive" and add a configuration file to the package and set the initial values for all the variables and all the connection manager in that configuration file (which might be tedious of-course).

    OR

    The third options (which I like do) is to open the package file and delete the password encryption entries from the package. Do note that this is not supported by designer and every time you make changes to the connection managers these encryption entries come back.

    Hope this helps. 


    Please mark the post as answered if it answers your question

    • Marked as answer by Eileen Zhao Tuesday, August 07, 2012 7:42 AM
    Wednesday, August 01, 2012 12:49 PM

All replies

  • I assume you have already set up the package confugiration wherein you are supplying the user name and password to conenct to the data source

    You can try setting the DelayValidation = TRUE at the package level, also make the Protection Level to DontSaveSensitive it would be set to EncryptSensitiveWithUserKey, by default


    Abhinav
    http://bishtabhinav.wordpress.com/

    Wednesday, August 01, 2012 8:44 AM
  • during the development the connection manager in the package  will refere the connection in the datasource.But during the deployment it will refer the package configuration..So before the deployment we will edit the package cofiguration to change the connection string...we have changed the connection settings of db2 by giving the useid and password...and we will change the MS SqlServer connection settings also...

    ilikemicrosoft

    Wednesday, August 01, 2012 8:52 AM
  • Shall we know how you are executing the package
    Wednesday, August 01, 2012 10:45 AM
  • Hi,

    the answer is in this video : http://msdn.microsoft.com/en-us/library/dd440760.aspx

    Cheers

    Régis

    Wednesday, August 01, 2012 11:29 AM
  • Hi Surendiran,

    This is because the package has been created by somebody else and the package is being deployed under sombody else's account. e.g. If you are the creator then the package is encryption set according to your account and the package setup in SQL server is under a different user account.

    This happens because the package protection level is set to EncryptSensitiveWithUserKey which encrypts sensitive information using creator's account name.

    As a solution:

    Either you have to set up the package in SQL server under your account (which some infrastructures do not allow).

    OR

    Set the package property Protection Level to "DontSaveSensitive" and add a configuration file to the package and set the initial values for all the variables and all the connection manager in that configuration file (which might be tedious of-course).

    OR

    The third options (which I like do) is to open the package file and delete the password encryption entries from the package. Do note that this is not supported by designer and every time you make changes to the connection managers these encryption entries come back.

    Hope this helps. 


    Please mark the post as answered if it answers your question

    • Marked as answer by Eileen Zhao Tuesday, August 07, 2012 7:42 AM
    Wednesday, August 01, 2012 12:49 PM