none
Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B

    Question

  • 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 "Schlüssel ist im angegebenen Status nicht gültig.". 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 I’m right the solution should be to use a Package Password, but I can’t figure out where I have to go enter/change a password. I even can’t remember I that ever used a password on my old installation for a dtsx-package??Sad

     

    Any help is welcome…

     

    Regards,

    Dirk

    Monday, November 21, 2005 4:23 PM

Answers

  • 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.aspx

    As it turns out, the protection level of the document is a property of the package.

    S
    Monday, September 11, 2006 8:24 PM

All replies

  • First of all let's try and work out what is wrong. Your package has a property called "ProtectionLevel". What is it set to?

    By the way, your package password is stored in a property of the package called PackagePassword.

    -Jamie
    • Proposed as answer by Akhtar Jahan Thursday, April 03, 2014 4:28 AM
    Monday, November 21, 2005 6:28 PM
  • 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.
    • Proposed as answer by Sam Aaron Friday, December 11, 2009 4:51 PM
    Saturday, April 08, 2006 9:10 PM


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



    Sunday, August 06, 2006 8:25 PM
  • 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.aspx

    As it turns out, the protection level of the document is a property of the package.

    S
    Monday, September 11, 2006 8:24 PM
  • 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,

     

     

    Friday, May 18, 2007 3:19 PM
  • 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

     

    Wednesday, January 09, 2008 8:39 PM
  •  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

     

    Wednesday, January 09, 2008 9:08 PM
  • Other than having to reenter passwords, are there any downsides to DontSaveSensitive?

     

    Thanks,

    Abe

     

    Thursday, January 10, 2008 2:39 PM
  •  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

     

    Thursday, January 10, 2008 2:42 PM
  • 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.

    Thursday, January 10, 2008 8:02 PM
  •  

    Hi .. Thanks a lot .. This is really helpful.
    Thursday, February 21, 2008 1:46 PM
  • 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

    Wednesday, June 18, 2008 2:08 PM
  • 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.

    Friday, October 03, 2008 5:12 PM
  • The linked worked for me.  What is your exact issue?

     

    Friday, October 03, 2008 5:45 PM
  • 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.aspx

    As 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 setting a 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 run as 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?

     

     

    Friday, October 03, 2008 6:02 PM
  • 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.

     

    Friday, October 03, 2008 7:22 PM
  • I see, hmm -- I like the sound of keeping it on a config. I'll look more into thos -- thanks for you reply!
    Friday, October 03, 2008 9:54 PM
  • 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.


    Monday, February 16, 2009 11:20 PM
  • Make sure that the lastest service pack is installed for SSIS. As of today KB955706

    http://support.microsoft.com/kb/918222/

    http://support.microsoft.com/kb/913089/

    http://support.microsoft.com/kb/KB955706
    Wednesday, August 19, 2009 1:46 PM

  •   Let's Say your package name is MyPackage
      In Visual Studio  Go to Control Flow Tab.
      Righ Click on an empty area inside the window not clicking  "Data Flow Component" .  pop up menu click the the properties to get to the properties window of MyPackage package.
     
    Under the Security Area -> You will see 
      
            ProtectionLevel              -- Change that to EncryptSensitiveWithPassword
            PackagePassword          -- enter password->  temp
       
    This 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 dtsx file.  (MyPackage.dtsx)

      Leave everything default.

      Click OK.

      Dialog box will appear asking 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 we stored our package above)

        Click the button for the package:  Choose your package  (MyPackage)

       Click OK :

        It will ask the package password again :  temp

        
                    Package 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
    Thursday, September 24, 2009 8:38 PM
  • Hi dwith,

    I`m trying to save packages in SSIS server with EncryptSensitiveWithUserKey, but when I ran a job, it failed, but what you say solves the problem. Thank you.
    Monday, November 16, 2009 5:08 AM
  • 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
    Tuesday, December 08, 2009 3:02 PM
  • dwith..... you are a CAMP mate... thx... it worked for me great...... :) thanks for your help........


    sword
    Tuesday, February 16, 2010 10:23 PM
  • Dwith,

     

    Thanks so much for your post.  Your suggestion ended a 3 hour head scratching session for me!!!

    Wednesday, August 04, 2010 5:20 PM
  • This is working for me.... Thanks Azhar and you are awsome...
    Tuesday, August 17, 2010 6:52 PM
  • 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

    Wednesday, September 29, 2010 4:28 PM
  • 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
    Friday, December 17, 2010 10:30 PM
  • 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 

    Wednesday, February 02, 2011 4:23 PM
  • 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
    Tuesday, May 10, 2011 3:37 PM
  • 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
    Wednesday, November 30, 2011 12:54 AM
  • 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').

    • Proposed as answer by SqlSam Monday, March 04, 2013 6:43 PM
    Thursday, May 24, 2012 9:19 AM
  • Hi,

    I had the same problem as everyone here seems to be having. Im sitting on SQL server 2008 r2.

    Tried the most things people here has posted. But nothing seemed to work. When I finally imported the package from the SSIS project into SQL server agent and there, and only there, changed protection level to "rely on server storage and roles for access control" like the following picture shows:

    

    After this everything worked without changing anything in the project file.

    Tuesday, October 02, 2012 2:31 PM
  • Thanks dwith... now my package is running fine.
    Thursday, October 11, 2012 7:35 AM
  • Thanks so much dwith,

    I also had a similar problem as you did & I almost couldn't find why a job that runs two of my packages on SSIS 2005 that retrieve data from Oracle server & export them into csv files cannot run successfully. Using Visual Studio 2005, I tried to enter user name & password that can access to Oracle database in my packages & saved it. But for some reasons, it didn't save at all even though the box "Save password" was selected or marked. If I run these packages manually from SSIS, they can run very well without any problem.

    Now following your instructions, then my issue was solved & I'm really happy with it. The job ran smoothly.

    Thanks again dwith,

    Edward

    Tuesday, December 10, 2013 7:41 PM
  • The link doesn't work. Anyway thanks for posting!
    Tuesday, December 10, 2013 7:43 PM
  • Hi All,

    I too got the similar error:

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

    I had XML configuration at package level to get Connection sting and Variable values. I created a new SSIS solution and added my existing package and modified few things and then next day I got above error.

    After going through the thread, I checked the "ProtectionLevel" of my package and found it is "SaveSensitivewithuserkey" then I tried to change it at package level the It asked to change it first at Project level and the at package level.

    I did this cahnge and next day there was no error or warning.



    Thanks Shiven:) If Answer is Helpful, Please Vote

    Friday, July 04, 2014 12:33 AM