Post reply

"Key not valid for use in specified state.". You may not be authorized to access this information

  • January 27, 2009 at 8:41 am

    #128268

    Hi, I am a noob as far as SSIS is concerned so go easy on me.

    I have set up a transfer login task between two servers, both of which run under the same domain user account.

    the task runs ok but I get this error message:

    Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "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: 2009-01-27 15:22:31.48 End Error DTExec: The package execution returned DTSER_SUCCESS (0). Started: 15:22:30 Finished: 15:22:48 E. The step succeeded.

    The package is executed via a SQL Agent job.

    After a bit of research I managed to get rid of the error by changing the protectionlevel to 'dontsave sensitive', but I am not sure this is the best way round this error.

    Is there a better fix for this and what is the exact cause of this error.?

    thanks in advance

    george

    ---------------------------------------------------------------------

  • January 27, 2009 at 9:35 am

    #933294

    george sibbald (1/27/2009)

    Hi, I am a noob as far as SSIS is concerned so go easy on me.

    I have set up a transfer login task between two servers, both of which run under the same domain user account.

    the task runs ok but I get this error message:

    Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "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: 2009-01-27 15:22:31.48 End Error DTExec: The package execution returned DTSER_SUCCESS (0). Started: 15:22:30 Finished: 15:22:48 E. The step succeeded.

    The package is executed via a SQL Agent job.

    After a bit of research I managed to get rid of the error by changing the protectionlevel to 'dontsave sensitive', but I am not sure this is the best way round this error.

    Is there a better fix for this and what is the exact cause of this error.?

    thanks in advance

    george

    George,

    By default the package protection level is EncryptSensitiveWithUserKey. This means that all sensitive information is protected with a password based on the user account, which you used to create the package. This means that if you try to run your package with SQL Job Agent, using the default service account it will fail. There are 2 available solutions:

    1. You can set to encrypt your sensitive information with password and then setup your DTEXEC command line in the SQL Job Agent to decrypt using the password you have used.

    2. Setup your SQL Job Step to use a proxy account and this proxy account should be the same one as the one you used to create the package.

    You can check more about different approaches how to setup your SQL Job Step here.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • January 27, 2009 at 10:30 am

    #933362

    thanks for the reply. Please bear with me -

    I've gone for option one but have hit the following problems:

    I set protection level at encryptsensitivewithpassword and set a password, although no evidence it is retaining this in the field.

    I have exited from BIDS and to get back into the package I need to enter the password, but if I try to save the updated package to msdb i get an error 'the protection level of the package requires a password, but package password property is empty'

    I presume I will also have to manually update the command line tab in the SQL agent job step. Is the syntax /P password

    option 2 seems overkill for me for a transfer logins package and getting a new account set up will be long winded, plus I dont want to logon with a seperate account just to create SSIS packages, unless I can use my own account (admin privileges) as the proxy account?

    ---------------------------------------------------------------------

  • January 27, 2009 at 10:41 am

    #933374

    george sibbald (1/27/2009)

    thanks for the reply. Please bear with me -

    I've gone for option one but have hit the following problems:

    I set protection level at encryptsensitivewithpassword and set a password, although no evidence it is retaining this in the field.

    I have exited from BIDS and to get back into the package I need to enter the password, but if I try to save the updated package to msdb i get an error 'the protection level of the package requires a password, but package password property is empty'

    I presume I will also have to manually update the command line tab in the SQL agent job step. Is the syntax /P password

    option 2 seems overkill for me for a transfer logins package and getting a new account set up will be long winded, plus I dont want to logon with a seperate account just to create SSIS packages, unless I can use my own account (admin privileges) as the proxy account?

    George,

    Check your MSDB. There should a field where you should put the password. The password is not supposed to be preserved from inside BIDS. The proper parameter form the SQL agent job is with /DE . For the proxy account, yes you can use your own account. Please keep in mind that this might be a security problem in case your SSIS package is somehow breached because it will execute with administrative rights.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • January 28, 2009 at 7:19 am

    #933786

    CozyRoc,

    I am not a happy bunny. Got this to work (eventually) only to find this in BOL after I was unable to logon with SQL ids on the destination server:

    At the destination, the transferred logins are disabled and assigned random passwords. A member of the sysadmin role on the destination server must change the passwords and enable the logins before the logins can be used.

    What is the point of this, it means the transfer logins task is useless for transferring SQL authenticated ids if you want a seamless failover. Do you know why it was designed this way?

    I could not find anywhere to store the package password in msdb so could you elaborate on this? I was able to get a password protected package into msdb by saving it to file system first and then importing it into SQL. I then specified the password in the SQL agent job on the command line tab with the /DE option. However, although the job completed successfully the warning message about decryption was still present.

    So I tried it with a proxy and that worked without any warnings, but of course it all turned out to be a bit moot.

    So as I have a mixture or windows and SQL logins it looks like I will have to revert to loading up the script produced by sp_help_revlogin, which is a shame because I will need to load scripts to cover server roles and default language as well.

    Do you have a good source of information covering these various security gotchas in SSIS.?

    cheers for your help, its been an interesting learning experience

    ---------------------------------------------------------------------

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply