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

  • Question

  • Hi,

    I have one job in SQL Agent. It contains 5 steps in it. It includes few packages and one cube processing steps all running fine. The protection level property in all packages set to EncryptSensitiveWithUserKey. Now I have created a new package and added on the last step in the same daily job. The protection level same as other. There is no proxy account set up for all the above steps in the same job. On this last step I am getting 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: 2013-07-12 12:18:04.63     Code: 0xC0202009     Source: OrganisationFteDetail Connection manager "DEV_Quote"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E4D  Description: "Login failed for user 'quotesystem'.".  End Error  Error: 2013-07-12 12:18:04.63     Code: 0xC020801C     Source: Data Flow Task Dev_Qoute FTE Details [1]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "DEV_Quote" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: 2013-07-12 12:18:04.63     Code: 0xC0047017     Source: Data Flow Task SSIS.Pipeline     Description: component "Dev_Qoute FTE Details" (1) failed validation and returned error code 0xC020801C.  End Error  Error: 2013-07-12 12:18:04.63     Code: 0xC004700C     Source: Data Flow Task SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2013-07-12 12:18:04.63     Code: 0xC0024107     Source: Data Flow Task      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  12:18:04  Finished: 12:18:04  Elapsed:  0.219 seconds.  The package execution failed.  The step failed.


    MH

    Friday, July 12, 2013 11:51 AM

Answers

  • I can't say for sure, but I am guessing that none of the other packages have passwords in them and the last one you added does have a password.  EncryptSensitiveWithUserKey requires that the user running the package be the same as the one that added a password if SSIS is to decrypt the password.  If there is no password in the package it behaves just like DontSaveSensitive.  However, as soon as there is a password, SSIS will not be able to decrypt the password and you will get errors.  I can't remember the specific errors.

    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by MustafaH Tuesday, July 16, 2013 11:38 AM
    Friday, July 12, 2013 12:01 PM

All replies

  • Hi,

    Make sure that the user name and password provide for package is correct.

    Regards

    Naveen

    Friday, July 12, 2013 11:58 AM
  • Hi Naveen,

    Thanks for your reply. I did not set any password for the package.


    MH

    Friday, July 12, 2013 12:01 PM
  • I can't say for sure, but I am guessing that none of the other packages have passwords in them and the last one you added does have a password.  EncryptSensitiveWithUserKey requires that the user running the package be the same as the one that added a password if SSIS is to decrypt the password.  If there is no password in the package it behaves just like DontSaveSensitive.  However, as soon as there is a password, SSIS will not be able to decrypt the password and you will get errors.  I can't remember the specific errors.

    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by MustafaH Tuesday, July 16, 2013 11:38 AM
    Friday, July 12, 2013 12:01 PM
  • Hi,

    Thanks for your reply. I am using two other servers in the package as source and destination.In connection string I have selected the option save my password. and package protection property is set to EncryptSensitiveWithUserKey.  I have created one proxy account in SQL Agent, the Key not valid error has gone now but i am getting message the unable to acquired connection for dev_quote (the source system). Even I have selected the option save password in connection string.

    Am I missing something?

    Now I am deploying the same package on other server. Created proxy account but getting this error

    TITLE: SSIS Execution Properties
    ------------------------------

    Error: 2013-07-12 15:42:50.92
       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: 2013-07-12 15:42:51.19
       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
    DTExec: Could not set the connection string Data Source=MPL-LDN-NPGDBD\DW_DEV;User ID=ETLProcess;Cr4b@pple;Initial Catalog=Nature_Warehouse;Provider=SQLNCLI10.1;Application Name=SSIS-OrganisationFteDetail-{379007F6-B621-475D-A491-CC3EC4F9D3F2}MPL-LDN-NPGDBD\DW_DEV.Nature_Warehouse.ETLProcess;Auto Translate=False; for connection Nature_Warehouse.


    ------------------------------
    BUTTONS:

    OK
    ------------------------------


    MH


    • Edited by MustafaH Friday, July 12, 2013 2:44 PM details
    Friday, July 12, 2013 12:53 PM