May 22, 2008 at 12:00 pm
So here's where I am, and I need an answer:
- Created an SSIS package that needs sensitive password info for connecting to an Oracle DB
- The package was originally created with ProtectionLevel set to EncryptSensitiveWithUserKey
- After expereiencing errors, I change ProtectionLevel to DontSaveSensitive and created a configuration file that stores the Oracle password.
- I am able to execute the package on my development machine with my dev user account AND as a job with the SQL Agent service account
- When I move the package and configuration to another server, I am able to execute it with my dev user account, but NOT as a job with the SQL Agent service account (I receive the famed "Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized..." error)
- How is it possible that I can execute the package with my user AND the SQL Service account on machine 1, but on machine 2, I can only execute it as my user account? I would think that the encryption issue would have been fixed when I successfully ran the package as a job on machine 1.
May 23, 2008 at 4:17 am
What worked for me is to save the encrypted information with password.
Note that when you create your configuration.xml file, the sensitive information will not be created. Microsoft deems this as a risk. You have to open the config file and add this information by hand.
Hope this helps
~PD
May 23, 2008 at 8:19 am
I had attempted EncryptSensitiveWithPassword, and I could never get that to run as a SQL Agent login, even though I got the command line stuff correct and could run it as my dev user. .
I had read somewhere that SSIS BI isn't real good at changing the encryption levels once the package has been created. I think that sounds reasonable, because even though my package is saved as DontSaveSensitive, I still receive encryption errors when I run it. However, if that's the case, what's the solution?
May 23, 2008 at 10:19 am
You might want to look at storing those username/password combos in an encrypted manner inside SQL to better protect the data. Of course you would need to include a script task to decrypt it so it can be used but you should be able to run fine this way w/o having to save the sensitive data inside the package itself...
Ben Sullins
bensullins.com
Beer is my primary key...
May 23, 2008 at 11:02 am
The main question is, I'm not storing any secure data in the package. The ProtectionLevel is set to DontSaveSensitive, so there really shouldn't be any encryption involved.
My password info is stored in an XML-based configuration file on the server. I really didn't expect to see any more encryption errors once I set the package to DontSaveSensitive.
Still searching for the solution. Thanks for the on-going help.
May 29, 2008 at 2:19 pm
The next step I will try is to recreate the package from scratch, using the DontSaveSensitive setting and using Configuration files. Perhaps some setting is not getting updated in my existing package when I change the EncryptionLevel.
May 29, 2008 at 4:53 pm
I fixed this issue. I did two things.
Firstly, I recreated my package from scratch, first saving the EncryptionLevel to DontSaveSensitive. Luckily my package is small! 😉
When I ran the package as a job on my test server, I was seeing weird intermittent issues. For example, when I would run the job manually, the status would quickly flash to "Performing complete..." before going back to Error. Oddly, when looking at the data, the package had still executed. So, I checked the login name for the SQL Agent service. It was correct, but being the nut that I am, I changed to something else and then changed it back. Whatever happens behind the scenes during that process changed something that now allows my package to run successfully as a job!
I'm not sure if I would have success with the second step alone, but to anyone with this issue, try changing the login account to something different and then restoring it to the correct service account.
Thanks for the help!
May 29, 2008 at 11:29 pm
Whoa!
That does sound a tad bit strange...
I spent about 3 days understanding configurations in SSIS (actually I kinda figured that if I was going to add dynamics to my packages, may as well start here first).
How I configured my packages to run:
I chose to store all my configurations in SQL, simply because it means that I dont need to worry about additional security on a file system somewhere, everything is in the database. Also, when getting into package deployment, when you use xml configurations, all passwords are exposed to whomever does the deployment.
a) I have a database called SSIS Configurations running on my localhost. This is specifically to deal with configurations at development time. I went a little smarter and created 3 tables - database configurations, file configurations, FTP configurations. I reckon the one subject to most volatile change is file configurations, as drive letters etc will most probably change per environment (I will most probably be adding another table called SMTP configurations later, once I get the damn resource mailbox approved for the 2 servers). The same structure exists on preprod as well as production databases.
b) I have an environment variable on my localhost that points to this database. I reached agreement with the operational guys on our preprod and production server to create the same. Therefore, I dont really care what the user is that they are using to link to SSIS Configurations.
c) ANYTHING remotely resembling a connection string, file path, remote FTP path, server IP is stored in these configurations. The only thing to remember is that if you add any configurations, you will need to work out how to deploy these to your other environments. If you get really smart, you could write a install SSIS package (hmmm I think I just added to my to-do list)
NOW, what was really cool is that if you standardise the naming conventions on your connection manager, and actually enable a configuration, it remembers that you are for example pointing to the connection string, or whatever the configuration is for.
Therefore, there is no need to save anything sensitive.
I have deployed several mini-packages, and one really big one so far, and I havent had any of the issues described. It plugs in seamlessly to whatever configuration is specified.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply