SSIS: Setting the same variable in multiple projects in the catalog

Recently I was assisting a customer with a review of their SSIS implementation and one the security implementations that they had in place was service accounts for SQL Server Integration Services packages. While using SQL Authentication to establish the connections to SQL Server, one of the challenges on the team was that the password for this service account reset every 90 days. In the old package deployment model, they used an XML file to manage the configurations and wanted to know how they would be able to change the password easily across hundreds of projects by upgrading to the latest version of SQL Server and the project deployment model. The purpose of this post is to demonstrate how the use of the environment variables feature in SSIS 2012 can accomplish this ask, while keeping your service account password information secure.

To start, I have a sample SSIS solution that contains 2 separate projects, each with 1 package per project as shown in the Figure below:

Using "LoadCustomerInformation.dtsx" as an example, there is a project parameter for the Data Warehouse where all the data should eventually land. For this example, the connection is using SQL Authentication to connect to the database. One of the challenges of SSIS development in the past with SQL Authentication is the password that needs to be used to connect. Workarounds for this in the past have included storing the password in the connection string. With SSIS 2012+ there is a way to store the configuration information in the project parameters file as sensitive which masks the connection at the SSIS catalog encryption level (AES_256). Parameterizing the entire connection string and storing it as sensitive, however, results in the below error:

"The expression will not be evaluated because it contains sensitive parameter variable "$Project::DataWarehouse_ConnectionString". Verify that the expression is used properly and that it protects the sensitive information. "

This behavior is by design. The best practices approach to storing the connection information in SSIS is not to store the entire connection string inside of one variable but to separate out the individual pieces (username, password, server, etc.). This makes configuration easier within environment variables as well (we'll come back to those later). Below is a screenshot of the connections in this package. Viewing them, you can easily identify which one is more secure and protected from attack:

So back to the initial question, using multiple SSIS projects. Both the project above and a separate project have been deployed to the SSIS catalog in this environment, with the same parameter setup for the Data Warehouse connection (ServerName, UserName, Password). Since they both use the same service account and password to connect, we want an easy way to modify the Password (remember, which is also encrypted at AES_256 so can't be seen by anyone on the server). This also allows the environment to be extremely secure by creating a separate password for production and set by the DBA team, while the development team can build and focus on new releases to the business instead of maintenance.

Within the Integration Services catalog in management studio, right-click on Management Studio and select "Create Environment…"

Give the environment a name, and click OK. Then open the environment up to modify the properties. On the variables page, give the variables that you wish to assign through the environment a name. They do not necessarily have to be the same name as the ones within Visual Studio. Under the password that was marked sensitive in Visual Studio, make sure you mark it as sensitive here as well (otherwise the package will fail):

Once these are set we need to assign them to the projects. Click Ok, then right click on one of the projects and select "Configure…"

This will open the project configuration page. On the references page, add a reference to the environment that was just created:

Then click back on the Parameters page. The last step is to assign the variable that was created in the environment that belongs to the variable at the project level:

Then click Ok, and repeat the exercise in the last 2 steps to assign the same environment variables to the additional projects. Afterwards, password changes for the service account become very easy to manage by simply being updated in an encrypted field. Below is a screenshot after I've executed the package. Notice that the password is still encrypted.