Identifying Sensitive Data

One of the founding security design principles for Integration Services was that passwords should never be stored in clear text. But it presents a problem. How does the persistence code know what properties are passwords or other sensitive data? For stock components that the SSIS team wrote, this wouldn't be a problem. All the password and sensitive properties could just share a common implementation of some encryption code. But, there needed to be a way to share the encryption code so that third-party custom component writers could also participate in the same encryption mechanism. So, it is necessary to somehow mark properties.

SSIS lets components differentiate between normal package data and sensitive data. To mark a property as sensitive, the component writes a Sensitive=True XML attribute to the property element when saving the component. Generally, when a property is marked as sensitive, it is a password. But the Sensitive attribute can protect any information that the custom component writer deems too sensitive to be stored in clear text. SSIS detects that attribute on properties and can give special treatment to a component's sensitive property values depending on the package-protection setting for the package.

For example, the Sensitive attribute for a password property on a task in package XML might look similar to the following.

<Password Sensitive="1" Encrypted="1">AQAAtAAqEzgw/o80More Data</Password> 

This is the abbreviated XML for a property in an actual package. The SSIS runtime adds the Encrypted attribute so that it can detect that the value is encrypted and decrypt it when loading.

When the SSIS runtime saves a package, it writes all the data out to an in-memory document. It then searches the document for any nodes marked with the Sensitive attribute. If the SSIS runtime finds one, it encrypts the value of the node with either a password or user key or simply removes the value altogether, depending on the package-protection option. Then, it saves the entire document to disk.

It's a simple but important concept. This is covered in more detail in Chapter 24, "Building Custom Tasks," but for now it's important to understand how the SSIS runtime identifies sensitive properties.