SSIS - Very Intelligent With Security
(Aug 20 2007 - 07:17:16 AM
by Timothy Khouri
) - [print blog
My company has switched from DTS packages to SSIS as a new standard. Since we have literally hundreds of DTS packages, we've decided not to try and port everything over, but instead to slowly "phase out" the old. Since I love the "cutting edge" so much, I was given the task of creating a few SSIS packages for some new client processes. No, I'm not saying that SSIS is really "cutting edge" (as it's been around for a few years now), but in terms of my company, it is.
Encrypted Connection Strings
The point of this post is that I'm very impressed with the way that "secure" information (such as connection strings) are handled in SSIS. At first I didn't understand why my SSIS package would work locally (while designing in "SQL Server Business Intelligence Development Studio" - that's a long name), but it wouldn't work when I've moved it to the extract server.
The SSIS package failed because the connection strings could not be decrypted by the SQL Agent (the default user that runs SSIS packages). As a side note, I didn't even realize that the connection strings were encrypted because whenever I would open the designer, I would see clear text connection strings (of course, because I had permission to decrypt).
When you check the history logs, you'll see a very cryptic error 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."
The Solution Is Simple
Again, the reason for this is that the "SQL Agent" user cannot decrypt any secure information (such as the connection string). The solution is to tell SSIS not to encrypt the secure information based on the current user (which is the default behavior), but instead to encrypt the secure settings in the "MSDB" (which does not stand for "Microsoft Database", but rather "Management Studio Database").
To do this, you go to "File -> Save Copy of Package.dtsx As..." and fill out the save dialog to look like this:
The key is the "Rely on server storage and roles for access control" setting. Once you have stored your package on the SSIS server with the above security settings, you can schedule a job and run it under the SQL Agent context and all the connection strings (and any other secure data) will decrypt as normal.