SSIS update to SQL Server 2008 R2 fails with Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "key not valid for use in specified state."

Posted on 2012-08-13
Last Modified: 2012-08-14
I'm attempting to take a BIDS solution that was created on a developer's PC using SQL Server 2005 and get it running on a server running SQL Server 2008 R2.  I receive this error every step of the way:

Error loading XXXXPackage.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.        

The developer who wrote the solution is no longer here.  There is only one developer who can get the solution to run on her PC in debug mode, and we are no longer certain what was done to get it to run there.

I've gone through the archives here, and I've attempted to change the protectionlevel, the password, etc., but I seem to be stuck in an endless loop.  If I copy the folders from the 'working' developers PC to the server and open the solution it wants to upgrade the package.  The upgrade fails with the same error in all but 3 of the packages in the solution.  When I then attempt to edit any of the packages I receive an error that the package may be corrupts.

If I go to the PC where the package was developed and attempt to save a copy of each package to the server changing the protection level at the same time, I receive an error that it could not find msdb.dbo.sp_dts_listpackages."  And to be honest, even if this worked, I wouldn't know how to tie all these packages back into a usable solution on the server.

Not having had a great deal of SSIS experience, I'd appreciate it if someone could point me to some step-by-step instructions on how to migrate this package and get it running on the server.

Or if that is not possible, is there a good way for me to recreate the package on the server using pieces of the original?  The developer worked for many weeks on this solution, and I don't have that kind of time.

Question by:bryanlaframboise
    LVL 11

    Expert Comment

    First thing is like .. suppose your packagedelpoyed  with "Save all with password " option in the security section .. then you cannot open the package unless you found the exact password.

    and you can try the below links:  (This will find your package password)
    LVL 11

    Expert Comment


    Author Comment

    Thank you.  The links you provided would be helpful if I were dealing with a deployed package, but the SSIS solution I'm attempting to move and upgrade has not been deployed, nor is it scheduled to run as a job.  I have found no evidence that the solution is password protected other than that it seems to have saved the developer's credentials as it was being written/saved.

    My goal is to get the solution upgraded and deployed to the server, so that I can schedule it.  Right now it is being run manually in debug mode on someone's PC.

    LVL 11

    Accepted Solution

    Hi ,

      Thanks for your update ..

       can you refer this link ... this may help your problem

    Author Closing Comment

    While I had seen these explanations before, this presentation provided me with enough insight to realize that not only did I need to change the ProtectionLevel, I needed to change the OLEDB connection string as well.  So I set the Protection Level on each package to DontSaveSensitive and the OLEDB to use Windows Authentication.  The packages now allow me to save, modify and best of all move them from 2005 to 2008.  I may still have some issues when I go to actually deploy the solution on the server, but at least I can now work with it.  Thank you.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Suggested Solutions

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now