none
DontSaveSensitive; Key not valid for use in specified state error

    שאלה

  • I initially had my packages set to 'EncryptSensitivewithPassword' and usedthe config files.  My job executes fine.  But I noticed I was getting THE error "......Key not valid for use in specified state.....".  So, I change the protection level to 'DontSaveSensitive' , saved packaged ,build and re-imported it.  And I still get the same result.

    What am I doing wrong??   Package is on the same server where it was built.

    I have a package in production that executes without the error.  I made a change to the package (logging) in test and now it also encounters same error as the package referenced above.

    יום שישי 30 יולי 2010 16:19

כל התגובות

  • First of all, there really is not need to 'Build' your packages like you do a VB.Net code project. SSIS packages are more like Reporting Services rdl files. You just deploy them. I never use the Build feature.

    If you have Database Connections with saved passwords, then there are only a limited number of options to get your package to run on a server:

    1. Use EncryptSensitiveWithPassword or EncryptAllWithPassword. But that requires that you supply, remember, and recall that password when you go to edit or deploy. And who wants another password to remember.
    2. Use DontSaveSensitive, and save your Connection Strings to a Config (I use a mix of XML and SQL Server Configurations). And after generating the Config entry, you need to go in and manually supply the "Password=abc123;" clause.
    3. Use EncryptSensitiveWithUserKey or EncryptAllWithUserKey and then use the same account that will be used to EXECUTE the package and log in as that account when you are DESIGNING. But what admin will give out the password to the service account(s)?

    My suggestion: set up a Configuration strategy that works for you. You can find my methodology here. Once I settled on this Configuration methodology, I NEVER run into this error.

    Oh, and the fourth option: switch to Windows Integrated Security for ALL Connection Managers. (But still use a Configuration because it's easy to move between DEV, TEST and PROD without editing the package!)


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    יום שישי 30 יולי 2010 17:29
  • Yes I understand all that now.  but when I first started with SSIS (i am referring to my first and second package that i have built with ssis) - I wasn't sure what I needed - it is a big switch from DTS . 

    I have established standards and procedures for others to use in the future.    But I need to know how to get rid of this error before I can put into a production environment. 

     

    יום שישי 30 יולי 2010 18:11
  • Answer these questions. Three "Yes" answers and yes, I would expect you to get that error.

    1. Are you using EncryptSensitiveWithUserKey as your Package Protection Level?
    2. Is the SQL Agent service account different that the account used to last edit the package?
    3. Does the package contain any Connection Manager that requires a saved password?

    Can you give us some insight into your package design? Connection Managers, flat file connection on the network, FTP tasks, all that stuff.


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    יום שישי 30 יולי 2010 18:20
  • 1.  package was initially set and save with 'encryptsensitivewithpassword'.  started using a config file when i realized this would be better when switiching environments and now have protection level set to 'dontsavesensitive

    2.  package designed with server's administrator account. sql agent service runs under a windows domain account.

    3. package contains 3 connections all of which requires passwords.  I have 2 config files established first config file contains the oracle and sql server connection info.  the second config contains only db2 connection information.  it was divided into 2 separate configs becausee all packages require the first config file and only a few will require the db2 config file.

    package is pretty simple the first few tasks retrieve information from a sql log file and oracle to establish the date range of data that needs to be pulled down.  then a dataflow task that ports data from oracle to db2 based on the aforemention date range.  then some houskeeping tasks to update the log (sql) with the last date and counts, etc.

    יום שישי 30 יולי 2010 18:34
  • Thanks for the description. 

    Things are Case Sensitive in the Config world so if your Connection Manager is named "Blah" in the package and in the Config file it is "BLAH", then you're out of luck.

    Next, have you opened the config files and inspected the Connection String(s)? They will NOT initially contain the Passwords that you specified during design time. You need to manually edit the Connection String to add the "Password=123abc;" clause.

    Finally, are you 100% certain that the server, and the SQL Agent service account can get to those Configuration files? They're not on a protected network drive, or anything like that?

    Personally, I use a SQL table for my Configurations, and each package has a Connection Manager pointing to that database. It contains EVERY Connection String ever used, EXCEPT the connection string to itself. That is handled by an XML Configuraiton file that is saved on each machine's (every developer's workstation AND every server) C:\ drive in the exact same folder.

    Here's how it works for me. Each environment (DEV, TEST, PROD) has its own SQL Configuration database, and the XML file directs the SSIS to connect to the appropriate one based on the environment. Obviously, the Connection Strings saved in the database differ, but the entries area all there.


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    יום שישי 30 יולי 2010 18:50
  • yes I know it is case sensitive - foudn that out about 3 days ago. 

    the package does execute seccessfully.   my database table is updated with the new rows and my log files is updated with the dates, counts, etc.  but  I also still receive this error.  would of thought the error would of been fatal.

    יום שישי 30 יולי 2010 19:31
  • the package does execute seccessfully.   my database table is updated with the new rows and my log files is updated with the dates, counts, etc.  but  I also still receive this error.  would of thought the error would of been fatal.


    This is new information and changes everything! I wouldn't be surprised to find that there are some lingering objects in the package that you cannot see in the designer. I have seen it happen before, but only once. It came in a package designed by another developer.

    You might try openning the package in "View Code" mode and peruse through the XML code looking for Connection Managers or other 'things' that aren't really there. Failing that, re-design the package from scratch, which is what I did.


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    יום שישי 30 יולי 2010 19:57
  • i tried rebuilding and it didn't work either.

    I found this link:  http://social.msdn.microsoft.com/Forums/en-US/crm/thread/44a86158-83da-4411-bc18-a9f4d8fe2ced and created a job executing package via the file system ; did not use proxy account and it worked fine.

    My job previous was executed via dtexec.exe when I was getting the error.

    I prefer using the latter method; however if it is just going to cause me grief - then I will go with wwhat works.

    Can anyone shed any light on a solution to my problem.

    Thanks

    יום שלישי 31 אוגוסט 2010 18:00
  • It's been a while. Can you re-state all the symptoms you are having, and paste in any error messages?
    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    יום שלישי 31 אוגוסט 2010 19:07
  • created ssis package logged in as testsrvr\admin;  tests fine.

          protection level - don't save sensitive

          use xml config files

    move to production server logged in as prodsrvr\admin; tests fine in bids.

    exec package via sql agent (runs under domain\prodsrvr account ). Both attempt same package

    job attempt #1

    • type  - cmdexec
    • run as - SQL Agent Service account
    • command - exec using  dtexec.exe /SQL "\DS\DSU002A 586SSN_TO_SQL" /SERVER SQLDRK51 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF  /REPORTING EW

     receive error

    Executed as user: ILSOS\sqldrk51. ... 9.00.4035.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  7:00:00 PM  Error: 2010-08-30 19:00:00.33     Code: 0xC0016016     Source:       Description: 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.  End Error  Error: 2010-08-30 19:00:00.35     Code: 0xC0016016     Source:       Description: 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.  End Error  Error: 2010-08-30 19:00:00.35     Code: 0xC0016016     Source:       Description: Failed to decrypt...  Process Exit Code 0.  The step succeeded.

    Job Attempt #2

    • type SSIS
    • run as SQL Agent Service account
    • package source file system
    • package - I:\SSISProjects\DSU002 Viisage Data Transfers\Viisage Data Transfers\DSU002A 586SSN_TO_SQL.dtsx

    no error

    Executed as user: ILSOS\sqldrk51. Microsoft (R) SQL Server Execute Package Utility  Version 9.00.4035.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  12:54:03 PM  DTExec: The package execution returned DTSER_SUCCESS (0).  Started:  12:54:03 PM  Finished: 12:54:08 PM  Elapsed:  4.953 seconds.  The package executed successfully.  The step succeeded.

     

    יום שלישי 31 אוגוסט 2010 19:27
  • Use DontSaveSensitive, and save your Connection Strings to a Config (I use a mix of XML and SQL Server Configurations). And after generating the Config entry, you need to go in and manually supply the "Password=abc123;" clause.

    I have tried numerous times to get the DontSaveSensitive option to work using an FTP connection and setting my password in a config file, however I always get "Error occured in the requested FTP operation. ..... The password was not allowed." Using the same config file and insuring that all variables and connections are empty (SSIS Design canvas is lit up like a christmas tree) the EncryptSensitiveWithPassword option works like a charm.

    Anyway microsoft certainly could not have made this any less intuitive.


    • נערך על-ידי MannyB יום שישי 16 מרץ 2012 20:33
    יום שישי 16 מרץ 2012 20:17