• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2044
  • Last Modified:

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."

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.

Thanks,
0
bryanlaframboise
Asked:
bryanlaframboise
  • 3
  • 2
1 Solution
 
SThayaCommented:
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:

http://www.sqlservercentral.com/Forums/Topic1255878-364-1.aspx#bm1255904  (This will find your package password)

http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/7ba03586-d3c0-4644-91cb-b2d34d94d62b
0
 
bryanlaframboiseAuthor Commented:
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.

Thanks!
0
 
SThayaCommented:
Hi ,


  Thanks for your update ..


   can you refer this http://www.mssqltips.com/sqlservertip/2091/securing-your-ssis-packages-using-package-protection-level/ link ... this may help your problem
0
 
bryanlaframboiseAuthor Commented:
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.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now