Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Securing Your SSIS Packages Using Package Protection Level


By:   |   Last Updated: 2010-08-17   |   Comments (10)   |   Related Tips: More > 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:

<DTS:Password DTS:Name="Password" Sensitive="1">

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
SSIS packages have a property called ProtectionLevel with several possible values

To show the effect of the ProtectionLevel property, add an OLE DB Connection Manager to an SSIS package:

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:

specify DontSaveSensitive as the ProtectionLevel

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):

<DTS:PASSWORD Sensitive="1" DTS:Name="Password" Encrypted="1">AQAAANCMnd8BFdERjHoAwE/Cl+...</DTS:PASSWORD>

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:

 you do not want to deploy an SSIS package with this setting

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:

The EncryptSensitiveWithPassword setting for the ProtectionLevel property requires that you specify a password in the package

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:

opening a package with a ProtectionLevel of EncryptSensitiveWithPassword

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:

encrypt the entire contents of the SSIS package with your specified password

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:

encrypt the entire contents of the SSIS package by using the user key

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.


Last Updated: 2010-08-17


next webcast button


next tip button



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, November 18, 2015 - 2:21:18 PM - Ray Barley Back To Top

Beginning with SQL Server 2012, there is a new deployment model called project deployment.

Take a look at these tips for the details:

https://www.mssqltips.com/sqlservertip/2450/ssis-project-deployment-model-in-sql-server-2012-part-1-of-2/

https://www.mssqltips.com/sqlservertip/2451/ssis-project-deployment-model-in-sql-server-2012-part-2-of-2/

Part 2 talks about project deployment "Environments" which allow you to specify package parameters and store them securely in the SSISDB database.


Wednesday, November 18, 2015 - 12:58:32 PM - Zinc Back To Top

NONE of these options appear to allow saving an sql server auth password in the connection manager, what's the point of having a checkbox there that says "save my password" if it's always nonfunctional?   This whole scheme is about as broken as it could be.  I'm thinking the only solution must be to set the Sensitive flag to 0.


Tuesday, November 03, 2015 - 4:22:21 PM - Ray Barley Back To Top

I think the best approach for package protection level is to not store any sensitive data in the package then you can choose the DontSaveSensitive option.  Store sensitive data using the SQL Server package configuration.  Take a look at this tip:

https://www.mssqltips.com/sqlservertip/1405/sql-server-integration-services-ssis-package-configuration/

 


Monday, November 02, 2015 - 5:03:08 AM - farahnaz Back To Top

Hello, 

Thanks for sharing useful information. Is there any way to protect SSIS after deployment at client side? Above all methods are fine if you don't give deployment right to the user. In our case we have to provide deployment rights to the user but in this way we need to tell them Password for deployment so they can easily decrypt the project as well. We are using SSIS 2010 and manifest at project level is also not available. Kindly reply me soon if you have an idea how to handle such situation.  


Thursday, October 29, 2015 - 8:16:16 AM - farah naz Back To Top

Hello, We need to tell password to the user for deployment otherwise user can't deploy successfully. So Protection level won't good when you want to give deployment rights to the user. Is there any way to protect SSIS packages at deployment level? 


Wednesday, November 26, 2014 - 5:54:09 AM - Samuel Back To Top

Hi Ray...thanks for the article...helped me. Thank you


Saturday, June 15, 2013 - 6:30:41 PM - Ray Barley Back To Top

SSIS does not support password protecting attributes.  The package protection settings allow you to encrypt the package or just sensitive information in the package; see this link for the details on what is sensitive: http://technet.microsoft.com/en-us/library/ms141747(v=sql.90).aspx

When you supply the password or you are the user that encrypted the package using one of the encrypt with user key options you have access to everything in the package.


Tuesday, June 11, 2013 - 12:31:51 AM - uma Back To Top

       We are creating a package in abc.com and need to deployed through dtutil.All team members should have the access to view the package.but not the attributes.Need to set the password for the attributes

a)      Batch file that runs dtutil program and set the Protection level to EncryptSensitiveWithPassword on each of the ssis package you have

b)      Batch file that runs dtutil program and set the Protection level to EncryptSensitiveWithUserKey on each of the ssis package you have

c)       Batch file that runs dtutil program and set the Protection level to EncryptAllWithUserKey on each of the packages you have created earlier

d)      Batch file that runs dtutil program and set the Protection level to EncryptAllWithPassword on each of the packages you have created earlier

 


Saturday, May 18, 2013 - 7:08:23 AM - Ray Barley Back To Top

You are correct.  With SQL Server 2012 there is a new configuration approach called project deployment which works great.


Friday, May 17, 2013 - 5:35:32 PM - Hui Shi Back To Top

Ray, if in a team environment, the only option for the protectionLevel would be 

DontSaveSensitive

 

You need to use Package configuration (xml, env variable, SQL table ) to feed connectString to Connection Manager.

 

All other options are simply painful

 

cheers


Learn more about SQL Server tools