SSIS packages are failing randomly

  • Tershi

    SSC Rookie

    Points: 33

    Randomly some of my SSIS packages are failing with this message: 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. 
    My question is if the packages are running fine other times, why its giving me this error message ?

  • Phil Parkin

    SSC Guru

    Points: 243186

    Tershi - Wednesday, January 9, 2019 10:43 AM

    Randomly some of my SSIS packages are failing with this message: 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. 
    My question is if the packages are running fine other times, why its giving me this error message ?

    Are different people attempting to run the packages? What is the Protection Level of the packages?


    Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this [/url]link.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

    Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.

  • Tershi

    SSC Rookie

    Points: 33

    Phil Parkin - Wednesday, January 9, 2019 12:11 PM

    Tershi - Wednesday, January 9, 2019 10:43 AM

    Randomly some of my SSIS packages are failing with this message: 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. 
    My question is if the packages are running fine other times, why its giving me this error message ?

    Are different people attempting to run the packages? What is the Protection Level of the packages?

    The protection level of the packages is EncryptSensitiveWithUserKey. Nobody else is running the package.

  • Phil Parkin

    SSC Guru

    Points: 243186

    How are the packages being executed? (eg, manually, from SQL Agent, from T-SQL, …)
    Are they always executed in the same way?

    If you are executing them on a server, is there an option to remove passwords from the packages and set them to Don’t Save Sensitive? Then you can assign the passwords at run time from sensitive SSISDB environment variables. This is a better enterprise practice than using EncryptSensitiveWithUserKey.


    Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this [/url]link.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

    Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.

  • tim.ffitch 25252

    SSCommitted

    Points: 1747

    Yes as Phil said set the Project and each package to Don’t save sensitive.

    I personally use 2 solutions. A development solution with 1 Visual Studio Configuration and a Release solution with a Visual Studio Configuration for each environment the Solutions will be deployed to, eg. Development, UAT, OAT, Production etc.

    In the Development Solution I set the protection level to Encrypt with User Key.

    In the Release Solution I set the protection level to Don’t Save Sensitive.

    I use the Development Solution to test ideas, throw things away and play around with until I am happy. The packages only ever get run in the IDE. When I am happy I then import the SSIS package into the Release Solution and add the Parameters into the Configurations and set the values accordingly for each environment. Initially I deploy the solution using Project Deployment to Development server and set up my Agent Jobs for testing.

    I always keep previous versions of the packages that have gone through for release in the Development Solution. That way I don’t lose any work and can easily scrap new ideas if they don’t work out and I am not always fishing for passwords when doing Dev work . Also it means there is only ever the current version in the Release that gets deployed.

    You then only need to fish for passwords and set them up after the deployment is done. Either in the configurations in the SSIDB catalog or in each SQL Agent Job

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

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