July 21, 2009 at 2:12 pm
[font="Times New Roman"]I have 5 SSIS packages which synchronize my application with corporate databases. Four run without error. One copies limited data from a corporate SQL Server database into the application database. It truncates an input table, copies over new data, and runs a stored procedure to update appropriately. It runs as a scheduled job under SQL Server Agent from the target server’s MSDB. The package has “ProtectionLevel” of “DontSaveSensitive”. The usernames and passwords for the connections are supplied with a configuration file (one dtsConfig per package).
When the package runs it works. I’ve compared the data using Red Gate Data Compare (great tool) before and after and updates are all moving correctly. But when it runs from SQL Server Agent I am getting, in the package log file, the error: [/font]
[font="Courier New"]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.[/font]
[font="Times New Roman"]I’ve run it from my developer account, from the system account which is used to log on the SQL Server Agent service, and I see no errors when testing.
Any idea what could be failing?
I’m grateful for any assistance.
[/font]
July 21, 2009 at 2:35 pm
My thought is that is a fantom error, because in reality when the package is opened, it CAN'T decrpyt the credentials for that connection (from your development work) and therefore it is an error. I'm guessing that you have delayed validation at the package level turned on or the package would probably have died right there. Either way, even though it can't decrypt them, it doesn't need to since you fill the data in from a config file. You could get rid of error by changing the connection to a trusted and then making sure the config file clears trusted and sets the username and password. But that will make Dev harder if you are visiting this package very often. I think there is also another option to suppress startup errors but I am leary of using it.
CEWII
July 22, 2009 at 8:02 am
This is helpful - thank you.
DelayValidation is false at all levels. The connection to the remote SQL Server uses the same domain authenticated account that runs the SQL Server Agent process. What option would I change to eliminate the run-time error?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply