By: Ray Barley | Comments (11) | Related: > Integration Services Security
Problem
I've noticed that SSIS packages have a property called ProtectionLevel with several possible values. Can you provide an explanation of the available options and examples of how they are used? In this tip we take a look at each of these protection levels and how they can be used.
Solution
ProtectionLevel is an SSIS package property that is used to specify how sensitive information is saved within the package and also whether to encrypt the package or the sensitive portions of the package. The classic example of sensitive information would be a password. Each SSIS component designates that an attribute is sensitive by including Sensitive="1" in the package XML; e.g. an OLE DB Connection Manager specifies that the database password is a sensitive attribute as follows:
When the package is saved, any property that is tagged with Sensitive="1" gets handled per the ProtectionLevel property setting in the SSIS package. The ProtectionLevel property can be selected from the following list of available options (click anywhere in the design area of the Control Flow tab in the SSIS designer to show the package properties):
- DontSaveSensitive
- EncryptSensitiveWithUserKey
- EncryptSensitiveWithPassword
- EncryptAllWithPassword
- EncryptAllWithUserKey
- ServerStorage
To show the effect of the ProtectionLevel property, add an OLE DB Connection Manager to an SSIS package:
The above connection manager is for a SQL Server database that uses SQL Server authentication; the password gives the SSIS package some sensitive information that must be handled per the ProtectionLevel package property.
Now let's discuss each ProtectionLevel setting using an SSIS package with the above OLE DB Connection Manager added to it.
DontSaveSensitive
When you specify DontSaveSensitive as the ProtectionLevel, any sensitive information is simply not written out to the package XML file when you save the package. This could be useful when you want to make sure that anything sensitive is excluded from the package before sending it to someone. After saving the package using this setting, when you open it up and edit the OLE DB Connection Manager, the password is blank even though the Save my password checkbox is checked:
EncryptSensitiveWithUserKey
EncryptSensitiveWithUserKey encrypts sensitive information based on the credentials of the user who created the package; e.g. the password in the package XML would look like the following (actual text below is abbreviated to fit the width of the article):
Note that the package XML for the password has the attribute Encrypted="1"; when the user who created the SSIS package opens it the above text is decrypted automatically in order to connect to the database. This allows the sensitive information to be stored in the SSIS package but anyone looking at the package XML will not be able to decrypt the text and see the password.
There is a limitation with this setting; if another user (i.e. a different user than the one who created the package and saved it) opens the package the following error will be displayed:
If the user edits the OLE DB Connection Manager, the password will be blank. It is important to note that EncryptSensitiveWithUserKey is the default value for the ProtectionLevel property. During development this setting may work okay. However, you do not want to deploy an SSIS package with this setting, as only the user who created it will be able to execute it.
EncryptSesnitiveWithPassword
The EncryptSensitiveWithPassword setting for the ProtectionLevel property requires that you specify a password in the package, and that password will be used to encrypt and decrypt the sensitive information in the package. To fill in the package password, click on the button in the PackagePassword field of the package properties as shown below:
You will be prompted to enter the password and confirm it. When opening a package with a ProtectionLevel of EncryptSensitiveWithPassword, you will be prompted to enter the password as shown below:
The EncryptSensitiveWithPassword setting for the ProtectionLevel property overcomes the limitation of the EncryptSensitiveWithUserKey setting, allowing any user to open the package as long as they have the password.
When you execute a package with this setting using DTEXEC, you can specify the password on the command line using the /Decrypt password command line argument.
EncryptAllWithPassword
The EncryptAllWithPassword setting for the ProtectionLevel property allows you to encrypt the entire contents of the SSIS package with your specified password. You specify the package password in the PackagePassword property, same as with the EncryptSensitiveWithPassword setting. After saving the package you can view the package XML as shown below:
Note that the entire contents of the package is encrypted and the encrypted text is shown in the CipherValue element. This setting completely hides the contents of the package. When you open the package you will be prompted for the password. If you lose the password there is no way to retrieve the package contents. Keep that in mind.
When you execute a package with this setting using DTEXEC, you can specify the password on the command line using the /Decrypt password command line argument.
EncryptAllWithUserKey
The EncryptAllWithUserKey setting for the ProtectionLevel property allows you to encrypt the entire contents of the SSIS package by using the user key. This means that only the user who created the package will be able open it, view and/or modify it, and run it. After saving a package with this setting the package XML will look similar to this:
Note that the entire contents of the package are encrypted and contained in the Encrypted element.
ServerStorage
The ServerStorage setting for the ProtectionLevel property allows the package to retain all sensitive information when you are saving the package to SQL Server. SSIS packages saved to SQL Server use the MSDB database. This setting assumes that you can adequately secure the MSDB database and therefore it's okay to keep sensitive information in a package in an unencrypted form.
Next Steps
- It's a good idea to plan which ProtectionLevel setting you will use. If you deploy SSIS packages with the default of EncryptSensitiveWithUserKey then only the user who created the package can execute it which is typically not what you want. You can change the ProtectionLevel of deployed packages by using the DTUTIL utility; see the /Encrypt command line argument.
- You can also set the password for a package using DTUTIL; see the /Decrypt command line argument.
- You can find additional details on the ProtectionLevel property in this SQL Server Books Online topic.
- The typical sensitive information is a connection string, especially when it includes a user name and password. My personal preference for this situation is to store that in a SQL Server package configuration, then secure the SQL Server database that's used to hold the configuration table. Take a look at our earlier tip SQL Server Integration Services SSIS Package Configuration for the details on SQL Server package configuration.
About the author
Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
