While working with SSIS packages, there are certain default behaviors and settings, which are there to make sure that sensitive properties are protected so that they are not saved or exposed as plain text. And by sensitive properties, it would mean any property that is sensitive in nature, such as "Password", "Client Secret", "API Key", etc. Usually, in the connection/component UI within development tools like Visual Studio or SSDT, they would be masked out. In an SSIS package, the following are defined as sensitive:
-
The password, secret, or such parts of a connection string.
-
The task-generated XML nodes that are tagged as sensitive. The tagging of XML nodes is controlled by Integration Services and cannot be changed by users.
-
Any variable that is marked as sensitive.
Whether Integration Services considers a property sensitive depends on whether the developer of the Integration Services component, such as a connection manager or task, has designated the property as sensitive. However, while deploying them onto an integration server, if these are not handled properly, it could result in unexpected errors, such as bad requests, unauthorized errors, etc. Therefore, having some steps and validations to have these translated successfully would ensure that the package runs flawlessly on the server as a part of an SSIS job. In this blog post, we will be discussing a few such mechanisms.
Protection Levels
In order to preserve such sensitive properties, you can set protection levels at the project and package levels. Furthermore, you can encrypt those sensitive fields with a password or a user key. There are various protection levels from which you can select the appropriate one. To do so, right-click on the project name and then open Properties.
Here you can see the types available in the drop-down list.
Out of these, the following two are the most commonly used options with regard to preserving sensitive properties:
- EncryptSensitiveWithPassword
- EncryptSensitiveWithUserKey
While using EncryptSensitiveWithUserKey, the option utilizes a user key based on the current user (who has created or last saved the package) to encrypt the values of the package's sensitive properties. Once done, then, only the same user who is using the same account profile can load the package on the same computer. If a different user tries to open the package, the sensitive information is removed and the current user must provide new values for the sensitive data. Note that this is the default option of an SSIS package after being created. Now, when you deploy the package from SSDT to an integration services catalog on your integration server, the package would only work properly if it's by the same user on the same computer. However, if the package encrypted by the User Key is imported to the SSIS catalog by an external wizard, which could be by using an .ispac file, then it would need to be the same user running the package (as a proxy user). Or if it's the service account, then the User Key needs to be handled accordingly.
Similarly, but slightly different from the above, is the EncryptSensitiveWithPassword protection level. It uses a password to encrypt the values of sensitive properties in the package. Sensitive data is saved as a part of the package, but that data is encrypted by using a password supplied when the package is created or exported.
Now, as with all Protection Levels, this needs to be in Sync in both Project and the associated packages. Therefore, once the protection level is set at project level, you could set the same in the package by opening the package Properties window in the control flow view.
To open the package in SSIS Designer, which is saved with the EncryptSensitiveWithPassword option, the user must provide the package password. If the password is not provided, the package opens without the sensitive data and the current user must provide new values for sensitive data. If the user tries to execute the package without providing the password, package execution will fail. And once deployed, the package password would need to be provided in the configuration tab when it pops up, unless it's deployed from within SSDT since a password would have already decrypted the packages.
Connection Parameterization
Along with Protection Levels, when you parameterize the Connection Managers, it is important to make sure that the sensitive properties are not missing in your deployment process. The recommended approach to parameterize connections is by using the ConnectionString property. For this, right-click on a connection manager, and open the properties panel, in which you can find the "ConnectionString" property.
In the ConnectionString, however, you will notice that the sensitive properties are missing. For instance, if you had filled up a password in the connection manager UI, it wouldn't be visible in the ConnectionString. The reason is that sensitive properties, as defined initially, are not actually saved as plain text in the ConnectionString for security reasons. For runtime use, you could however add those sensitive fields to the ConnectionString property. The format would be Password=myPassword; (make sure you have a semicolon as the last character), or ClientSecret=myclientSecret; etc., and can be placed anywhere in the ConnectionString.
Now, once again, this leads to them being in pain text, and in order to avoid that, you could assign these values to sensitive parameters, and assign those parameters in place of these text values. In SSIS, it allows a parameter to be sensitive and to be assigned to the property, as long as the property is sensitive as well.
Find out more details on how to assign them in an SSIS job in our blog post below:
Conclusion
We hope that this blog post has given you some insight on how to work with sensitive properties to avoid runtime issues in SSIS. It's worth mentioning again that when a project's protection level changes, it's important to change those of the associated packages as well to avoid any SSIS validation errors.