You are currently offline, waiting for your internet to reconnect

How to use the dtutil utility (Dtutil.exe) to set the protection level of a batch of SQL Server Integration Services (SSIS) packages in SQL Server 2005

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

INTRODUCTION
Microsoft SQL Server 2005 Integration Services (SSIS) implements security on the client computer and on the server computer when you deploy SSIS packages. You can encrypt the packages to keep the packages' property values secret by setting the protection level of the packages. Packages include the ProtectionLevel property. You can set the ProtectionLevel property according to the level of protection that a package requires.

For example, in a team development environment, a package can be encrypted by using a password that is known to the team members who work on the package. You can easily set a password by using SQL Server Business Intelligence Development Studio or by using the dtutil utility (Dtutil.exe) for a single package. However, if you have to handle lots of packages, the best method is to use the dtutil utility to set the protection level of a batch of SSIS packages. You can generally put a list of individual commands for each package in a .bat file or in a .cmd file and then run the file. If the packages are stored in the same folder, you can use short commands to loop the batch of packages in that folder.
MORE INFORMATION
Consider the following scenario:
  • You are deploying SSIS packages that have the default protection level.
  • You have to send the packages by using a password to other team members who know the password.
In this scenario, you can use the following command to use the dtutil utility to set the protection level of these packages.
for %%f IN (*.dtsx) DO dtutil.exe /File %%f /encrypt file;%%f;3;<promissory password>
Notes
  • You do not have to decrypt packages that have the default protection level because the default protection level of SSIS packages is the EncryptAllWithUserKey value. The default protection level encrypts packages by using a key that is based on the user profile.
  • Add the /q switch to avoid additional prompts.
  • If you type this command at a command prompt, use one percent (%) symbol. If you use this command inside an MS-DOS-based batch file, use two percent (%%) symbols.
  • In this command, <promissory password> is a placeholder for the password.
  • This command actually performs a loop for the following command.
    dtutil.exe /File %%f /encrypt file;%%f;3;<promissory password>
    The protection level is set as a parameter in the command. In this example, the protection level is set to 3 before the password. Use one of the protection levels that is described in the following table according to your requirements.
LevelDescription
0Strip sensitive information.
1Sensitive information is encrypted by using local user credentials.
2Sensitive information is encrypted by using the required password.
3The package is encrypted by using the required password.
4The package is encrypted by using local user credentials.
5The package uses SQL Server storage encryption.
For more information, type the following command at a command prompt:
dtutil /? Encrypt
When the other team members receive these packages, the team members may have to change the password to a password that only the team members know or change the protection level. The team members can use the following command to use the dtutil utility to change the password for these packages.
for %%f IN (*.dtsx) DO dtutil.exe /File %%f /Decrypt <promissory password> /encrypt file;%%f;3;<new password>
REFERENCES
For more information about how to use the dtutil utility to set the protection level of SSIS packages, see the following topics in SQL Server 2005 Books Online:
  • Setting the protection level of packages
  • Security considerations for Integration Services
  • Package protection level
  • dtutil utility
SSIS
Properties

Article ID: 906562 - Last Review: 03/11/2006 00:36:01 - Revision: 2.2

Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Standard Edition

  • kbsql2005ssis kbhowto KB906562
Feedback
> ://c.microsoft.com/ms.js"> kTracking = 1; var varCustomerTracking = 1; var Route = "76500"; var Ctrl = ""; document.write(" document.write(" 50&did=1&t=">