January 9, 2019 at 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 ?
January 9, 2019 at 12:11 pm
Tershi - Wednesday, January 9, 2019 10:43 AMRandomly 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 absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 10, 2019 at 2:50 pm
Phil Parkin - Wednesday, January 9, 2019 12:11 PMTershi - Wednesday, January 9, 2019 10:43 AMRandomly 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.
January 11, 2019 at 6:02 am
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 10, 2019 at 1:21 pm
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply