Securing Packages

You might want to secure a package for many reasons; it might contain sensitive data such as passwords that you do not want to be accessible to all users, you might want to restrict who can run the package, or you might just want to make sure a critical business process is modified by only a select few. SSIS provides an interesting-if somewhat confusing-set of options for securing your packages (protection levels):

• Do Not Save Sensitive   No encryption is used, and data elements marked as sensitive are removed from the package when it is saved. This is the simplest and least robust option for securing a package.

• Encrypt All with Password   The package is fully encrypted and requires a password to modify or execute the package. This is pretty good security, but the downsides are that you have to share the password (which might get shared again without your knowledge) and the password could be exposed as clear text wherever you execute the package (in a SQL job or a batch file).

• Encrypt All with User Key   The package is fully encrypted and can be accessed only by the Windows account that saved the package.

• Encrypt Sensitive with Password   This is minimal security and ensures that no passwords or other secret information can be compromised. The sensitive items require a password to access. The package can be modified and executed without the password, but the user would have to provide the sensitive data elements at runtime.

• Encrypt Sensitive with User Key   This provides the same security as Encrypt Sensitive with Password, but the user key means the sensitive data is accessible only to the person who saved the package.

• Rely on Server Storage for Encryption   By storing the package in the MSDB database of a SQL Server instance, you can opt to manage access to the package using SQL roles, which is an approach that in our opinion is both flexible and very secure.

Note 

Settings that require a user key are encrypted using the Data Protection API (DPAPI). Settings that require a password are encrypted with Triple DES with a key length of 192 bits.

SSIS defines sensitive data as variables marked as sensitive (SSIS marks some automatically, and it’s possible to manually add a Sensitive attribute by editing the DTSX file), the password portion of a connection string, or the task-generated XML that SSIS marks as sensitive.

Packages created in BIDS default to a protection level of Encrypt Sensitive with User Key. To change the protection level, right-click anywhere on the Control Flow tab, select Properties, and then scroll down to find ProtectionLevel (see Figure 6.6).

Figure 6.6: Viewing the ProtectionLevel property

Regardless of what protection level you set in BIDS, you can override that setting to opt for ServerStorage when you deploy-if you’re deploying to SQL Server-by checking the Rely on Server Storage for Encryption check box. Note that unless you’ve encrypted the MSDB database, the package is not really encrypted, just secured using SQL Server permissions.

If you opt for ServerStorage, you can manage permissions by right-clicking the package in SSMS, selecting Package Roles, and then choosing from the available roles to determine who will be authorized to read or write to the package.

By default, members of the fixed roles DTSAdmin and DTSOperator plus the package creator are granted read access to the package, and members of the DTSAdmin role plus the package creator are granted write access to the package. If you need more granular control, you can add database roles to the MSDB database and then use those roles to control access to your packages (users will still need to be members of the appropriate fixed roles).

The security puzzle has a couple of other pieces. If you have elected to deploy your packages to the file system rather than store them in SQL Server, you will want to use NTFS permissions to secure the folder. Another option that goes beyond protection levels is to digitally sign the package (select SSIS from the top-level menu, and then select Digital Signing) using a certificate. Digital signing is top-notch security and is a great choice if your organization can issue certificates.