February 21, 2011 at 12:32 am
hi,,
when i was trying to run a ssis package through the sql agent job
in that type is operating system (cmd.exec) but i got an error message below.
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.
can you suggest me how to solve this problem
Thanks for advance
February 23, 2011 at 1:45 pm
What is the value of the package property "ProtectionLevel"? You can find this by right clicking some open area on the Control Flow design area and choosing properties.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 24, 2011 at 7:44 am
As opc mentioned, check the package's protection ... unless you're using passwords and security with your packages and their deployment, it's probably best/easier to set your package ProtectionLevel to DontSaveSensitve, which removes passwords from the packages, which should probably solve your problem.
February 25, 2011 at 2:55 am
Hi Radh,
In some versions/service packs of SSIS - mostly 2005 - I've encountered a similar issue. If I muck with the Package ProtectionLevel property, I can end up with a package in an odd state: There is no longer a need for a PackagePassword and yet there is a PackagePassword property still stored in the XML of the dtsx file.
When creating the SSIS Job Type Step, SQLAgent apparently looks at the ProtectionLevel setting and will not prompt you for the PackagePassword unless that setting is one of the "...WithPassword" options. But (again), it's possible to have a package in a "rogue" state - whereby the ProtectionLevel is not set to one of the "...WithPassword" options and yet there's still a PackagePassword setting present. At run time, SQLAgent sees the PackagePassword property and wonders why you haven't supplied the password. - and then raises this error.
I've remedied this a couple ways:
1. Set the ProtectionLevel of the package to DontSaveSensitive, save it, close it and reopen it; and then re-enter all connection manager passwords. (Using DontSaveSensitive is a best practice for SSIS package security, but that's just part of the story. You then have to manage connections externally using some form of package configuration).
2. Make a copy of the dtsx file. Right-click the package in Solution Explorer and click View Code. Search the XML for the PackagePassword property and delete it. Note: it's not hard to utterly corrupt your SSIS package by editing the XML directly. If you're going this route, make a copy of the file first.
Hope this helps,
Andy
Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply