Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B
Hi,I have developed several SSIS packages with the last Beta of VS2005 / SQL Server CTP. After the public release I tried to uninstall the CTP-Versions to install the msdn finals but this time I got lost and was not able to satisfy the requirements of the final setup of VS2005. So I decided to install the whole pc again and after some hours I had a clean machine (XP with latest SQL Server 2005 Standard and VS2005 Professional). Now I have tried to open my SSIS-Project but getting the following error: Error loading ImpNetqNewsRss.dtsx: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Schlssel ist im angegebenen Status nicht gltig.". 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. After some googleing I found this thread: http://forums.microsoft.com/msdn/showpost.aspx?postid=22739&siteid=1 If Im right the solution should be to use a Package Password, but I cant figure out where I have to go enter/change a password. I even cant remember I that ever used a password on my old installation for a dtsx-package?? Any help is welcome Regards, Dirk
November 21st, 2005 11:23am
I had the same problem and was able to resolve it by changing how I stored the package protection level when saving the package to SQL server. Only when saving the package to SQL server did it allow me to change this option. In DTS designer it will not let you save the package with a protection level of ServerStorage. Who knows why as this is just a designer with a deployment option. Anyways:- Select File > Save Copy of <package> As- The bottom box is greyed out called protection level. Click on the weird box with a dot in the middle on the right and a dialog will pop-up.- Change the package protection level to the last option "Rely on server storage and roles for access control" This allows any one with access to execute the package defined by SQL server roles to run the package. Hope this works for you as well. I am using dtexec from xp_cmdshell to run packages from some stored procs after a SQL 2000 migration.
April 8th, 2006 5:10pm
this is a very very very good workaround. In addition if you would like to edit it after in the VS, you have to open the .sln (backup it for safe...)that refers to the package that that you cant open. and delete the package, then add the package that you have in the server (the one that you do it on the up post...). then save it and close it to check it works.
August 6th, 2006 4:25pm
In case anyone is still looking for this, here is the way to solve the problem in Visual Studio:http://www.cubido.at/Blog/tabid/176/EntryID/71/Default.aspxAs it turns out, the protection level of the document is a property of the package.S
September 11th, 2006 4:24pm
This has resolved my problem - I have spent a great deal of time running the MS turorial on SSIS package deployment - configuration options etc - but could not get a package to execute from a SQL scheduled task that had an embedded password in it. The documentation on this aspect is very vague and it is only your post that solved the problem - Thanks very much! Regards,
May 18th, 2007 11:19am
Is the only way to allow multiple people developing a set of packages (using source control) to Encrypt with a package password or DontSaveSensitive? I am trying to figure out a scenario where multiple people can work on a set of SSIS projects.. Thanks, Abe
January 9th, 2008 3:39pm
Abe558823 wrote: Is the only way to allow multiple people developing a set of packages (using source control) to Encrypt with a package password or DontSaveSensitive? I am trying to figure out a scenario where multiple people can work on a set of SSIS projects.. Thanks, Abe Yes. I recommend using DontSaveSensitive -Jamie
January 9th, 2008 4:08pm
Other than having to reenter passwords, are there any downsides to DontSaveSensitive? Thanks, Abe
January 10th, 2008 9:39am
Abe558823 wrote: Other than having to reenter passwords, Where are you having to re-enter passwords? If you are using ProtectionLevel='DontSaveSensitive' properly (i.e. in conjunction with configurations) then there should be no need to keep entering passwords. Abe558823 wrote: are there any downsides to DontSaveSensitive? Not that I know of. Good luck. -Jamie
January 10th, 2008 9:42am
You would have to reenter passwords for database connection strings, since they won't be retained with the package. I'd recommend using configurations to store those connection strings with the passwords, which will eliminate the issue. I use that with "DontSaveSensitive" on most of my projects without issue.
January 10th, 2008 3:02pm
Hi .. Thanks a lot .. This is really helpful.
February 21st, 2008 8:46am
Thanks It helped me to save the package to sql server with protection level setting to be relying on sql server roles. Thanks alot for this post. Neeraj
June 18th, 2008 10:08am
Confused -- was this post moved? The link above takes me to a post about ';Microsoft Big Days 2006', not about SSIS packages.I realize this post is two years old but I am running into the same problem and cannot reconstruct the solution for the rest of the thread. Thanks.
October 3rd, 2008 1:12pm
The linked worked for me. What is your exact issue?
October 3rd, 2008 1:45pm
The link in the answer: Bill Sempf wrote: In case anyone is still looking for this, here is the way to solve the problem in Visual Studio:http://www.cubido.at/Blog/tabid/176/EntryID/71/Default.aspxAs it turns out, the protection level of the document is a property of the package.S Works for you? Hmm, I don't understand. No matter I suppose, even though I see a post in German that is about a Microsoft event, not about this SSIS solution. I was able to deduce from the rest of this thread that the solution involved settinga property to'DontSaveSensitive'. I was able to find the Property: ProtectionLevel. I found the property by looking at the Package Properties accessible on the 'Control Flow' tab in the SSIS package designer in Visual Studio. This didn't work for me though, I need to save a password in the package for a remote server, and eventually schedule the package to runas a job in the Sql Server Agent. I'm working on this now, the route I have found is running the package via dtexec, but the hang up is trying to get it to run containing senitive data. ProtectionLevel=EncryptSensitiveWithPassword? Perhaps, but this still seems to fail. Any suggestions on a better solution?
October 3rd, 2008 2:02pm
There is a bunch of discussion on what is the best solution. If you EncryptSensitiveWithPassword a Package Password has to be set and used every time the package is run. You can have package level configurations and handle it there so that the password isn't saved in the package, but in a configuration.
October 3rd, 2008 3:22pm
I think I can do one better. First of all I do import the package with the "Rely on server.... blah... blah... blah...". However I import and install as an local Administrator, but the SQL Server Agent is set to run as a User, not and Administrator. As a result I execute the package which come out with the message posted below. The "funny" part is - it seems that after all the package does execute susccessfully and does everything it is supposed to do (at a glance - I'm still digging). So this message seems to be nothing but a big annoyance, which will not look good in front of the management. Any thoughts?Executed as user: LOCAL\SSISuser. Microsoft (R) SQL Server Execute Package Utility Version 10.0.1600.22 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:10:00 PM Error: 2009-02-16 23:10:03.13 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Property" 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. End Error DTExec: The package execution returned DTSER_SUCCESS (0). Started: 11:10:00 PM Finished: 11:10:36 PM Elapsed: 36.271 seconds. The package executed successfully. The step succeeded.
February 16th, 2009 6:20pm
Make sure that the lastest service pack is installed for SSIS. As of today KB955706http://support.microsoft.com/kb/918222/http://support.microsoft.com/kb/913089/http://support.microsoft.com/kb/KB955706
August 19th, 2009 9:46am
Let's Say your package name is MyPackage In Visual Studio Go to Control Flow Tab. Righ Click on an empty areainside the window not clicking "Data Flow Component". pop up menu click the the propertiesto get to the propertieswindow of MyPackage package.Under the Security Area-> You will see ProtectionLevel -- Change that to EncryptSensitiveWithPassword PackagePassword --enter password-> tempThis should do the trick however to be sure:Below you will connection managers:Database Connections (if more than one preform on all) Double Click your connection to get the property pages. Click "ALL" under the Connection Link on Left Side. Scroll Down to Security Area.Provide the followings: Password (for the sql userid being used) Persist Security Info = True----------------------------------------------------------------------Save the Package and connect to SQL Integration Srvices in SQL Manager (To Server e.g;DBServer (Integration Services) Stored Packages ->MSDB --> Right Click --> choose Import Package in the property dialog box Package Location : File System Package Path -- Choose the location of your dtsxfile.(MyPackage.dtsx)Leave everything default. Click OK.Dialog box will appearasking for the Package Password Provide the password-> temp You have successfully imported the package called MyPackage. In order to create a job. In the job Step-> Type: SQL Server Integration Services Package In the General Tab: Package Source : SSIS Package Store Server: DBServer (Where westored our package above) Click the button for the package: Choose your package (MyPackage)Click OK : It will ask the package password again : tempPackage has successfully been loaded to Job Step. Now you can schedule and do a test run on the job. Thanks for the patience of reading for those who are expert. - Azhar
September 24th, 2009 4:38pm
Hi dwith,I`m trying to save packages in SSIS server with EncryptSensitiveWithUserKey, but when I ran a job, it failed, but what you saysolves the problem. Thank you.
November 16th, 2009 12:08am
Would really appreciate if you could you propose any solution to the following scenario? http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/6679cb1f-1210-417e-89ea-53d840000b10
December 8th, 2009 10:02am
dwith..... you are a CAMP mate... thx... it worked for me great...... :) thanks for your help........sword
February 16th, 2010 5:23pm
Dwith, Thanks so much for your post. Your suggestion ended a 3 hour head scratching session for me!!!
August 4th, 2010 1:20pm
This is working for me.... Thanks Azhar and you are awsome...
August 17th, 2010 2:52pm
Azhar had it right. Thanks a bunch. I only made one small change to his process. When I import the DTSX package into SQL Server in step 2, I did not leave everything default. The last option in the Import Package dialog box is "Protection Level". I changed this option to "Rely on server storage and roles for protection level". I think that if your environment is based on Windows authentication, this is a better option anyway. And even if it's not, it also gets rid of the pesky password prompt that pops up any time you try to make changes to a job that includes the package as a step. The password prompt might not be an issue for the creator of the package (since he/she knows the password). But any developer that comes after you might find this to be a serious roadblock... ...unless you documented everything meticulously... And I know you did! -T
September 29th, 2010 12:28pm
I want to change the protection level of packages to "DontSaveSensitive" so I added these packages in to a new Integration Service Project using "SQL Server Business Intelligence Development Studio" and saved the Protection Level Property to "DontSaveSensitive" for all the packages. But Again when I am trying to add the same packages in to any other Integration Service Project it still shows protection level as "EncryptSensitiveWithUserKey" which is causing the below error while trying to Load or Promote the packge into SQL server from command promopt. Error 1 Error loading HACAppUserScopePackage 1.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:\Documents and Settings\dkanabar\My Documents\Visual Studio 2005\Projects\Integration Services Project3\Integration Services Project3\HACAppUserScopePackage 1.dtsx 1 1 Please help me to solve the problem. I want to set the Protection Level property of Package. Regards, Denish Kanabar
December 17th, 2010 5:30pm
If anyone is looking for this blog post on the Cubido website, it looks like they reorganized their blog storage a bit. Try this link instead: http://www.cubido.at/blogs/Lists/Posts/Post.aspx?ID=1402 -km
February 2nd, 2011 11:23am
Thanks KM for the 'current' link; however, I wanted to thank Azhar for his input. I had all sorts of issues with it executing in a job until I saw his step by step post. Thanks all of you. This is still relavant after all these years! Heidi
May 10th, 2011 11:37am
the link no longer works, including the updated one. Could someone just post the answer here instead of the link to that site which keeps changing. Thanks!MCSA
November 29th, 2011 7:54pm
One extra piece of information which took me ages to figure out: Use windows authentication when defining the connection in Connection Manager if using 'DonSaveSensitive'. I had specified a SQL login in the connection strings and had the problems listed above - the package ran fine in SSIS, but refused to work when run by the SQL Agent. I changed the ProtectionLevel to 'DontSaveSensitive' - but then I had the new problem of the package not containing the passwords to use the specified connection string and so the package failed since it was unable to access the DBs. The solution (obvious in hindsight) is to not use SQl Server authentication when specifying the Connection in the first place. By switching the Connection to use Windows auth, there was no password, and hence no sensitive information to lose by changing the Protection Level. When the sql agent tries to run the package, it uses it's own (domain account) credentials and so long as it has rights access the required data, the package finally works! (For what it is worth, I also had issues with the package being run by the 64bit runtime and being unable to access a data source of a specific type, but I found the solution to this online (tick use 32bit runtime in /JobstepProperties/Execution Options) - I couldn't find anything about using Windows Auth with 'DontSaveSensitive').
May 27th, 2012 5:25am