SSIS Design Pattern - EncryptSensitiveWithPassword
SSIS Design Pattern - EncryptSensitiveWithPassword
Deploying SQL Server Integration Services packages is one of the steeper paths on the slopes of the SSIS learning curve.
Like almost everything else in life, it makes perfect sense once you understand it. But it is completely and utterly different from anything I've ever worked with before. And if you're here reading this post, you're likely experiencing the same thing I did when I attempted to deploy my first SSIS package.
Hence this SSIS Design Pattern: EncryptSensitiveWithPassword.
Sensitive Stuff
SSIS doesn't allow me to decide what it defines as Sensitive. It simply decides for me and that's that. This keeps me from missing something and accidentally exposing a powerful domain, Windows, or SQL Server login and password - and for that reason alone I like it a lot.
Package Protection Levels
There are six Package ProtecionLevel property values:
- DontSaveSensitive
- EncryptSensitiveWithUserKey (default)
- EncryptSensitiveWithPassword
- EncryptAllWithPassword
- EncryptAllWithUserKey
- ServerStorage
I discussed DontSaveSensitive in a previous post.
As stated earlier, SSIS decides what is sensitive and what is not. EncryptSensitiveWithPassword encrypts only the information SSIS marks as sensitive using the password as a key.
One nice advantage is connection information travels within the SSIS package. This is particularly helpful if you're connecting to database engines that require usernames and passwords - such as Oracle and DB2. As a result, I use EncryptSensitiveWithPassword to troubleshoot permissions issues when deploying SSIS packages.
For example, when I schedule a package using a SQL Agent Job, they sometimes fail to execute. If I suspect this is due to a permissions issue, I'll rebuild the package using the EncryptSensitiveWithPassword Package ProtectionLevel setting. If the package then executes successfully I know the issue is permissions.
:{> Andy
Technorati Tags: SSIS Design Patterns ProtectionLevel EncryptSensitiveWithPassword