Securing a Package

You can protect a package in two ways: by encrypting sensitive information stored in the package definition file and by controlling who has the ability to run or make changes to a deployed package.

You can protect a package so that sensitive metadata, such as connection passwords, is encrypted; or you can encrypt the entire package. You can also use password protection to prevent both modification and execution of the package.

You can use password protection to implement only package definition protection or both package definition and deployed package protection. When deployed to SQL Server, a package can be protected by using role-based security.

Package Encryption

Package definition files contain connection information, user names, and passwords that might be considered sensitive. You can protect this information by encrypting all or part of the definition file with options such as encrypting only sensitive information (that is, connection string passwords) or encrypting the entire package, using the development environment. You can also choose to allow the SQL Server database engine to encrypt the package when it is deployed, using the SQL Server database option. The encryption algorithm can be seeded using a password or a key derived from the developer's Microsoft Windows user account.

Password Protection

You can use password protection to prevent modification and deployment. When you provide a password for the PackagePassword property of the package, the development environment will prompt for this password when attempting to open the package file. This, however, does not prevent the file from being opened from the Windows file system. The real value of password protection is when the password is used in conjunction with package encryption. As mentioned previously, the password can also be used to seed the package encryption algorithm, depending on the ProtectionLevel property setting.

To enable password protection on a package, right-click anywhere in the package designer, select Properties, and then use the Properties window to set the PackagePassword property.

ProtectionLevel Property

All of the security options are controlled by the ProtectionLevel property. With the package open in the package designer, you can use the Properties window to set the ProtectionLevel property to one of the following values:

• DontSaveSensitive Removes passwords and other sensitive information from the package.

• EncryptAllWithPassword Encrypts all package contents by using a password.

• EncryptAllWithUserKey Encrypts all package contents using a key based on the current Windows user. Only this user will be able to open and make changes to the package.

• EncryptSensitiveWithPassword Encrypts only passwords and other sensitive information in the package by using a password.

• EncryptSensitiveWithUserKey Encrypts the entire package by using a key based on the current Windows user. Only this user will be able to view sensitive information. Another user can modify the package, but sensitive information will not be displayed.

• ServerStorage Encrypts the package contents stored when the package is deployed to SQL Server. The encrypted package is stored in the MSDB system database. This option is supported only when a package is saved to SQL Server. Access to the package is controlled using SQL Server database roles.