SSIS connection string password issue
-
I know SSIS package doesn't save password in the connection string due to the security reasons....I added the password in the config file...Everything works fine, data gets loaded into the sql server from the flat file, but the problem is everytime the package is run as a sql job, the job fails with the following error message, but the data gets loaded into the sql tables....
"Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:38:19 PM Error: 2011-09-01 12:38:19.30 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: 2011-09-01 12:38:19.39 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "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."
why do i get the above error message even though i save the password in the config file??
-
You can ignore that error - it doesn't actually affect anything.
If you want to remove it completely, then what you'll want to do is change the encryption type of the package, from EncryptSensitiveWithUserKey, to EncryptSensitiveWithPassword, or EncryptAllWithPassword, and then supply a password.
Then when you are executing it from the job, you have two options. You can change the execution method to use xp_cmdshell, and then just make it a normal job. To do that, use the following command:
EXEC xp_cmdshell 'dtexec /FILE "[INSERT FULL PATH TO .dtsx FILE HERE]" /DECRYPT [INSERT PASSWORD HERE] /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E'
The /REPORTING E is so that you only get messages on the error lines, instead of all of them.
The other option is to use the Integration Services as the type for the job, and find the path for the .dtsx package. This is the approach you're probably already using. Only thing you'll need to do is to go to the Command Line tab, and add the line for /DECRYPT [INSERT PASSWORD HERE], if it's not already there
-
If you are providing the information from outside, then you can set the package protection level to DontSaveSensitive. This should solve the issue you are having.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
what's the advantage of using xp_cmdshell (not sure how it works, never used it before)...I always create it as a SSIS package....
Yes, i don't have "/DECRYPT [INSERT PASSWORD HERE]" in the command line.....Right now i am using "Restore the original options"...okay, i will go ahead and edit the command line to include the "/DECRYPT [INSERT PASSWORD HERE]" option
-
There's no advantage to using xp_cmdshell. Both approaches end up using it. The only reason why I like using the xp_cmdshell approach is because I don't really like the interface that SQL Server provides for the Integration Services jobs.
-
One more basic question....How to change the execution method of the sql job to use xp_cmdshell??(I never did it before 🙁 )
-
If you want to use that approach, just change it from an Integration Services job to a normal SQL Server job -- Type should be Transact-SQL script. Choose the correct database for it to be running from, and then just paste the xp_cmdshell line into the command box.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply