Security for SSIS packages

Protection for SSIS packages:

You set the protection level of a SQL Server 2005 Integration Services (SSIS) package when you first develop it in Business Intelligence Development Studio. Later, when the package is deployed, imported or exported from Integration Services in SQL Server Management Studio, or copied from Business Intelligence Development Studio to SQL Server, the SSIS Package Store, or the file system service, you can update the package protection level. For example, if you create and save packages on your computers with one of the user key protection level options, you likely would want to change the protection level when you give the package to other users; otherwise they cannot open the package.

The following table describes the protection levels that Integration Services provides. The values in parenthesis are values from the DTSProtectionLevel. These values appear in the Properties window that you use to configure the properties of the package object when you work with packages in Business Intelligence Development Studio.

Protection level	Description
Do not save sensitive (DontSaveSensitive)	suppress sensitive information in the package when it is saved. This protection level does not encrypt, but instead it prevents properties that are marked sensitive from being saved with the package and therefore makes the sensitive data unavailable to other users. If a different user opens the package, the sensitive information is replaced with blanks and the user must provide the sensitive information.
Encrypt all with password (EncryptAllWithPassword)	Encrypts the whole package by using a password. The package is encrypted by using a password that the user supplies when the package is created or exported. To open the package in SSIS Designer or run the package by using the dtexec command prompt utility, the user must provide the package password. Without the password the user cannot access or run the package.
Encrypt all with user key (EncryptAllWithUserKey)	Encrypts the whole package by using a key based on the user profile. Only the same user using the same profile can load the package. The package is encrypted by using a key that is based on the user who created or exported the package. Only the user who created or exported the package can open the package in SSIS Designer or run the package by using the dtexec command prompt utility.
Encrypt sensitive with password (EncryptSensitiveWithPassword)	Encrypts only the sensitive information in the package by using a password. DPAPI is used for this encryption. Sensitive data is saved as a part of the package, but that data is encrypted by using a password that the current user supplies when the package is created or exported. To open the package in SSIS Designer, the user must provide the package password. If the password is not provided, the package opens without the sensitive data and the current user must provide new values for sensitive data. If the user tries to execute the package without providing the password, package execution fails. For more information about passwords and command line execution, see dtexec Utility.
Encrypt sensitive with user key (EncryptSensitiveWithUserKey)	Encrypts only the sensitive information in the package by using keys based on the current user. Only the same user using the same profile can load the package. If a different user opens the package, the sensitive information is replaced with blanks and the current user must provide new values for the sensitive data. If the user attempts to execute the package, package execution fails. DPAPI is used for this encryption.
Rely on server storage for encryption (ServerStorage)	Protects the whole package using SQL Server database roles. This option is supported only when a package is saved to the SQL Server msdb database. It is not supported when a package is saved to the file system from Business Intelligence Development Studio.

 

The package protection levels that encrypt packages using passwords require that you provide a password also. If you change the protection level from a level that does not use a password to one that does, you will be prompted for a password.

Encryption, as used by package protection levels, is performed by using the Microsoft Data Protection API (DPAPI), which is part of the Cryptography API (Crypto API).

Integration Services uses the Triple DES cipher algorithm with a key length of 192 bits, available in the .NET Framework Class Library (FCL), for the protection levels that use a password.