Microsoft SQL Server 2005 Integration Services

2017-07-07 02:10:07

SSIS pulls together the concepts of sensitive data identification and encryption by providing six package-protection options. Figure 18.6 shows the dialog box you use to select the appropriate package-protection option whenever you save a package to file or SQL Server, whether you're using the SSIS Import/Export Wizard, DTExecUI, or SQL Server 2005 Management Studio.

Figure 18.6. The Package Protection Level selection dialog box

The package protection levels are also available in the designer property grid for packages.

Tip

Make sure you click on the package control flow design surface before you look at the property grid.

You should see the option to select the ProtectionLevel, as shown in Figure 18.7.

Figure 18.7. Selecting the package protection level in the property grid

The Package Protection Levels

Following is a brief description of each of the package protection levels.

Do Not Save Sensitive Data (DontSaveSensitive)This option, which eliminates all sensitive data from the package, is useful when you want to purge all passwords from a package. For example, if you write a sample package to share with others or you want to post a package to a support forum to have someone troubleshoot it for you, you can use this option to ensure that you don't share any passwords.

Encrypt Sensitive Data with User Key (EncryptSensitiveWithUserKey)This option is similar to the Encrypt All Data with User Key option, except the user key encrypts only sensitive properties. Users other than the person who saved the package can open packages encrypted at this level, but they'll need to overwrite all sensitive data in the package. This option is the default because it's the easiest way to keep sensitive properties safe while developing packages, but you should use the user-key encryption option only when you want to prevent anyone besides yourself from opening the package. After the package is complete and ready to be deployed, you should consider a different encryption option.

Encrypt Sensitive Data with Password (EncryptSensitiveWithPassword)This option tells SSIS to encrypt only those properties that are marked as sensitive. This option is useful if you're interested in protecting only the sensitive data in the package and not any of the other data or package logic.

Encrypt All Data with Password (EncryptAllWithPassword)This option tells SSIS to encrypt the entire package with a password that you provide. The password, which is the encryption key, isn't stored in the package. No user can open the package without the password, and you can't recover a password-encrypted package without the password. This option is useful when you have intellectual property tied up in a package or when you're sending packages across unencrypted channels such as email. The receiving party must know the password, which you can provide in a separate message or other medium. Encrypting an entire package is safer than encrypting only the sensitive data because more data is hidden.

Encrypt All Data with User Key (EncryptAllWithUserKey)This option is essentially the same as the Encrypt All Data with Password option, except that the user key is the encryption key. Only the person who saved the package with this option can open it and only on the same machine. If you create a package with this encryption mode, then attempt to share the package with others, they cannot open it on their machines. For more information about the user key, please see the sidebar "What Is a User Key?" later in this chapter.

Rely on Server Storage for Encryption (ServerStorage)If you use this option, SSIS doesn't strip out or encrypt any part of the package; you're essentially saying that you want to use SQL Server security to protect your sensitive data. Because SSIS always communicates with SQL Server through an encrypted channel, SSIS doesn't transmit your sensitive data to SQL Server in clear text. This option is available only when you're saving the package to SQL Server.

Table 18.3 gives a little more information about each of the package protection levels. Note that when using server storage, the sensitive data is left in the package. SQL Server 2005 provides an encrypted channel and SSIS uses it to protect the package and all sensitive data en route to the server.

Table 18.3. Package Protection Level Notes
Protection Level Setting	Applies to	Encryption	Where Encrypted	Notes
`RemoveSensitive`	Package file or field in sysdtspackages90	Internal IS methods to strip sensitive data	Client	This option clears all properties on save, which have the sensitive flag set to `trUE`.
`EncryptSensitive WithPassword`	Package file or field in sysdtspackages90	Windows Crypto API	Client
`EncryptAllWithPassword`	Package file or field in sysdtspackages90	Windows Crypto API	Client
`EncryptAllWithLocalUser Credential`	Package file or field in sysdtspackages90	DPAPI	Client	Combination of HW key and current user credentials.
`EncryptSensitiveWithLocal UserCredential`	Package file or field in sysdtsackages90	DPAPI	Client	Combination of HW key and current user credentials.
`ServerStorage`	Rows in sysdtspackages 90	SQL certificate	Server	Only allowed when saving to SQL 2005. This option leaves sensitive data in the package data itself, which is then protected in the destination (SQL). Allows the user to execute packages from SQL in a protected and noninteractive way (for example, Agent).

Note

If you save with one of the sensitive options Encrypt Sensitive Data with Password, Encrypt Sensitive Data with User Key, or Do Not Save Sensitive Datathe package XML will be visible in the designer, so you can recover it if you forget the password or are no longer able to log in as the person who created the package. This tidbit is good to know if you're ever concerned about impending layoffs, disgruntled package developers, or just long-term accessibility of packages and you're not too concerned about losing the intellectual property in your packages. Encrypting the entire package is a safer way of protecting everything in the package, but it also carries more risk than protecting only the sensitive data. If a user-key password is lost or the person who wrote the package leaves and you can't log in with the original credentials, the package is lost for good. No one, including Microsoft, can do anything to recover the package in such a situation. For some packages, such a loss might not be a problem, but it is good to be aware of the risks associated with full encryption.

SSIS lets you use either the user key or a password to encrypt an entire package or just parts of the package. Like your login password, an encryption password can be any password you create. However, Windows generates the user key and you cannot change it. The user key is ideal when you want to protect a package that only you will run. For example, it is a great option when developing packages because you don't need to repeatedly type in a password. The drawback to encrypting packages with a user key is that other users can't open the package unless they log in with the original package creator's credentials. Using a password is more flexible and can add secondary protection to packages, but requires you to type in the password every time you open the package.

What Is a User Key?

When a package protection level is EncryptAllWithUserKey or EncryptSensitiveWithUserKey, the SSIS runtime uses the Windows Data Protection API (DPAPI) to encrypt the package. The DPAPI is a simple symmetric key API and provides two functions for encrypting and decrypting data using the Triple-DES algorithm. The algorithm uses the user credentials as the password for encrypting, which is why packages encrypted with the user key are so bound to the login credentials of the user who creates the package. So, to answer the question "What is a user key?", the simple answer is, it is the user credential. To find out more, search MSDN for "Windows Data Protection."

Caution

It is important to understand the implications of using the user key encryption methods. Because the user key is used to generate the decryption key, if by chance, you ever forget your system login password and must have the system administrator perform a password reset, you will be unable to decrypt any packages that were encrypted with the EncryptAllWithUserKey protection level. This is really no different than if you forget the password used to encrypt a package with one significant exception: If you forget the password you used to encrypt the package, it's obvious to you and you chalk it up to bad memory. Because the user key encryption methods are somewhat transparent to you as an SSIS user, if you forget your system login password, and you have your password reset, it simply appears that your packages were corrupted somehow. And it only happens when you attempt to open the package after the next time you log in. Figure 18.8 shows the error you receive when attempting to load a package encrypted with the user key after resetting your login password. Use EncryptAllWithUserKey carefully.

Figure 18.8. Attempting to load an encrypted package after resetting the system login password

Protecting Nonpackage Files

Package encryption only protects the contents of packages and does not protect other files the package generates, such as log files, checkpoint files, or configuration files. Also, child packages do not inherit package protection levels from their parents. Depending on the package and the kind of data you store in variables, you might want to consider encrypting them or storing them in a secured folder.

For example, checkpoint files contain the contents of variables in the package at the last checkpoint saved. If the package is processing Visa numbers or medical records when the package fails, it might be important that you protect the resulting checkpoint file.

For protecting log files and configurations, the easiest way, in many cases, is to use the SQL Server variant. So, for configurations, use SQL Server Configurations and for logging, use SQL Server Logging. But there is no SQL Server variant of checkpoint files, so for checkpoint files you need to secure the folder in which they are stored using file system security, as described previously.