none
"failed to decrypt protected xml node "DTS:Password with error..."Key not valid for use in specified state." error on only one of many servers?!

    Question

  • Hi

    I have 7 Windows Servers (2003 R2 Enterprise Edition). Only one of them throws the "failed to decrypt protected xml node... "DTS:Password" with error "Key not valid for use in specified state." I've compared the installed programs from Control Panel -> Add or Remove Programs among the "bad" and "good" servers. I checked the following installed packs and programs but I could not indentify a patern.

    -image Server updates

    -Microsoft XML Parser and SDK

    -MSXML 4.0 SP2

    -MSXML 6 Service Pack 2

    -Security Update for Microsoft Visual Basic for Applications 6.5

    -Microsoft SQL Server 2008 Policies

    Then, I searched the web for registry key settings and the above error to see if I can find a correlation there. I had no luck there, either.

    I'd appreciate if some one could shed some light on the direction I should be looking. Thanks loads.

    Complete error is:

    Description: Failed to decrypt protected XML node "DTS:Password" with error 0
    x8009000B "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 err
    or. Verify that the correct key is available.


    • Edited by intrepid12 Wednesday, July 24, 2013 4:05 PM
    Wednesday, July 24, 2013 3:49 PM

Answers

  • I found the solution! I keep comparing the "good" with "bad" in terms of all the server patches, updates, oracle installation, etc. Nothing jumped at me. I opened the package in design view on the "bad" server. The oracle ole connection manager was not recognized. I started a new connection and noticed that it is not there in the dropdown. I compared some dll's in oracle\client10g\bin. They matched the "good" servers' dll's in terms of file size and date. Finally, I registered OraOLEDB10.dll and that did the job! Thank you all for your input.


    Thanks loads.

    • Marked as answer by intrepid12 Friday, July 26, 2013 1:55 PM
    Friday, July 26, 2013 1:55 PM

All replies

  • This error means that you have a protection level in your package that encrypts certain data (probably passwords for connection managers). The default is EncryptSensitiveWithUserKey, where user key is some sort of combination of the user account who created the package and details of the machine it was created on. If you deploy it to another server, the encryption will fail and hence the error.

    So take a look at the SSIS package that is failing and check the package protection level.


    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    • Proposed as answer by Harry Bal Wednesday, July 24, 2013 7:24 PM
    Wednesday, July 24, 2013 7:09 PM
  • Hi Koen

    Thank you for your reply.

    The package protection level is the default: EncryptSensitiveWithUserKey. I developed the package on my desktop computer. I've included the userID and password for the database in my connection string.

    On my desktop I am logged in with my individual account. All our servers are logged in with our service account. The userID and password for the database have nothing to do with the individual nor the service account.

    This package runs on 6 servers successfully. Only one server fails to do so. That's why I compared the "bad" server with the "good" ones to find out if any thing stands out.

    I appreciate your help.


    Thanks loads.

    Wednesday, July 24, 2013 7:57 PM
  • The error is definately caused by the protection level.
    Either you change it to DontSaveSensitive and supply username and passwords through a package configuration, or you set it to EncryptSensitiveWithPassword and supply a password when you run the package.

    No idea why it succeeds on the other servers though.


    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Wednesday, July 24, 2013 8:05 PM
  • I changed the package protection to DontSaveSensitive. The server runs fine on 6 servers but give me a new error on the problem server.

    Description: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_ERROR.  The requested OLE
     DB provider OraOLEDB.Oracle.1 is not registered. Error code: 0x00000000.
    An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  H
    result: 0x80040154  Description: "Class not registered".
    End Error
    Error: 2013-07-24 16:16:50.91
       Code: 0xC020801C
       Source: name, code, total OLE DB Source [52]
       Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAG
    ER.  The AcquireConnection method call to the connection manager "MAR1USL.mart_r
    ead" failed with error code 0xC0209302.  There may be error messages posted befo
    re this with more information on why the AcquireConnection method call failed.
    End Error

    Looking "...not registered ..." error on the web points me to the 32-bit vs 64-bit setting for Oracle. I'm conecting to an oracle db. Does this shed some light on what is going on perhaps? Is it even relevant?

    Thanks loads.

    Wednesday, July 24, 2013 8:54 PM
  • If you are making connection to Oracle in your SSIS package, then for sure (we know) you will have username/pwd to connect to Oracle. Now, I would recommend that you should have protection level of the package set to Encrypt sensitive with pasword. Provide the password in BIDS and then redeploy the package to SQL agent. If you have saved the package in SQL server, you will need to change the protection level of the package option there as well...It will again prompt you for password, provide it and then schedule your package.

    If your are using Dont save sensitive, for package encrytion, (you may know), it needs config file to read the connection string !!!! First, you have to create XML config file for the connection string, and secondly, you have to manually edit that file, to enter username and password for the Oracle connection....


    Thanks, hsbal

    Wednesday, July 24, 2013 9:05 PM
  • Is the 64-bit Oracle provider installed on the problem server?
    If you choose DontSaveSensitive as protection level, you need to supply the username and password for the Oracle connection through a package configuration (which doesn't need to be a file by the way).

    If you use EncryptSensitiveWithPassword, you indeed need to specify a password, but this can quite easily be retrieved, so it is not really secure.


    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Thursday, July 25, 2013 7:58 AM
  • I found the solution! I keep comparing the "good" with "bad" in terms of all the server patches, updates, oracle installation, etc. Nothing jumped at me. I opened the package in design view on the "bad" server. The oracle ole connection manager was not recognized. I started a new connection and noticed that it is not there in the dropdown. I compared some dll's in oracle\client10g\bin. They matched the "good" servers' dll's in terms of file size and date. Finally, I registered OraOLEDB10.dll and that did the job! Thank you all for your input.


    Thanks loads.

    • Marked as answer by intrepid12 Friday, July 26, 2013 1:55 PM
    Friday, July 26, 2013 1:55 PM