SSIS Packages Using Package Protection Level

Question

SSIS packages have a property called ProtectionLevel with several possible values. Can anyone provide an explanation of the available ProtectionLevel options and examples of how they are behave in a package? what is the advantage and disadvantage of using ProtectionLevel properties.

Thanks.

Answer 1

Package Protection Levels come in a few different flavors. The idea is that SSIS knows that things like connection strings might contain sensitive information, like passwords. The package itself might contain proprietary information if you are a vendor and your product is the WhizBangPackage you wouldn't want people to see how the magic works. For these reasons and more, MS has the concept of how the underlying XML, and that's all an SSIS package is, should be written to disk.

• EncryptSensitiveWithUserKey this is the default setting. Anything that could be sensitive is assumed to be sensitive. When the package is saved, VS is going to use some bits of the the Active Directory account of the original author to encrypt things like connection strings. Even if that connection string is using SSPI and thus, no password, it'll still encrypt the connection string in the underlying XML. When the package runs, SSIS will talk to AD to unencrypt that information. Generally, that all works well and good until the original author of a package is no longer with the company and their AD account is removed. What we ran into with SQL Server 2005 was the package can't be decrypted by the SQL Agent job running the package. A developer could go and open the package and it'd run fine in interactive mode but fail during non-interactive mode. The immediate resolution was to update the author to someone with an active AD account and redeploy. This may be fixed in current/future release but that's my war story on this one.

• DontSaveSenstive this is the only setting I ever had need to use. SSIS will not write anything that looks like a password into the .dtsx file when you save. And it was my experience that it'd also blank it out after saving in your current design session leading to immediate validation errors. In particular, this makes the FTP task a PITA to work with unless you are using configurations, which you should as that's the only sane way of migrating packages between environments. Use configuration to help SSIS connection managers to "remember" the password without it ever hitting disk.

• EncryptSensitiveWithPassword Instead of using an AD account to encrypt the sensitive bits, you now use a password the developer supplies. The downside to this is for teams of more than 1 person, you now have a shared password and a password shared by more than one person defeats the purpose of having a password.

• EncryptAllWithPassword Instead of just encrypting sensitive bits, this is going to encrypt the entirety of the XML with a password. Same downside as before, shared secret = no secret. Also, if you lose the key you're humped and are recreating your package.

• EncryptAllWithUserKey Same as with password, it encrypts the entire file with the author's AD account as the key. Same downside as above, that account goes away and there's no key to unlock the package.

• ServerStorage - Doesn't matter what your local setting, assuming you deploy to the msdb catalog, packages will be encrypted in the database. I've never used it to be honest. We deploy to the msdb but rely on our configuration to keep sensitive data private.