SSIS Clear encrypted password when unchecking Sensitive

Question

Tools used

• Visual Studio 2017 (with SSIS)
• SQL Server Management Studio 17.9.1

Involved in the process

Two SSIS developers and SSMS with Integration Services Catalog which stores deployed projects.

Overview

I have a solution with projects inside created in SSIS. Each project has project parameters specifying for each database connection two different params: Connection string and a password. Password is marked sensitive.

Project and all it's packages have ProtectionLevel set to EncryptAllWithPassword. The project get's pushed to git repository and another developer downloads changes. Now, he needs to provide password in order to be able to work with the project (or multiple projects within solution). So far so good, we have a "master password" on project levels which protect access to parameters such as sensitive passwords. When a developer goes to Project.params and untick sensitive mark, the password is shown. All good for now as well, since he needed to know the password for the project first to see the passwords.

Here's the tricky part

When the project is being deployed do Integration Services Catalog, ProtectionLevel is being changed and the project which can be exported from Management Studio is no longer password protected. To export such a project one obviously needs ssis_admin permission, but that's out of scope for this issue. When the project was deployed and then imported back from SSMS to SSIS, a developer can open it without password and untick the sensitive mark for Project.params passwords. All passwords are visible for him now. This is wrong.

What am I trying to achieve

I want to mimic the same behaviour with sensitive values we have in SSMS. Whenever you untick a sensitive mark on an environment variable, the value is cleared - like below.

However, when I do the same in SSIS Project.params (untick sensitive mark), the value is still shown so I can see all the passwords - as presented below.

I'd like it to be stored as it is, but unable to see it's plain text value.

Is it possible at all? Or maybe there's a better way to organise this? I need to be able to execute packages from within SQL Server Agent (SSMS) providing environment variables as well as from my own computer under SSIS, which is why I need to store these passwords in order not to repeat them every time.

Answer 1

This problem that you described is a real issue for any team working collaboratively on SSIS. I'll describe the pattern that I've used to solve this, which might be helpful. First, I should state that I don't like storing passwords in source control, even if they're encrypted. Here is what I typically do:

• Set all SSIS packages and projects to Don't Save Sensitive. This removes all passwords from the files and closes the source control loophole
• When possible, all the developers should have a local set up of the ETL ecosystem - SQL databases (no data or just test data), file system, etc. All packages should be configured to work against this local environment. In this way, you can be an admin, connect with windows authentication and have full control over the test data. This also helps you avoid interfering with anyone else's development and testing.
• For a SQL connection, set parameters for the connection string and password. The connection string can point to your local instance and use windows auth. The password can be blank and checked as sensitive. If everyone sets up their local system the same way, then nothing needs to change when another developer opens this up and begins work on the project.
• For deployment, environments can be configured for each server. The password can optionally be used for SQL authentication and the connection string would change to include the username property and not windows auth.

The above pattern makes it really easy to develop as a team and pretty straight forward for deployment automation.