SSIS Clear encrypted password when unchecking Sensitive

3
votes

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.

enter image description here

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.

enter image description here

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.

2
deploymentssisssmspassword-encryption
It is not possible to trick the user interface. But the ideology is to set passwords to sensitive and stick to this. Use the built into SSIS Configuration feature (perhaps as a file) When you deploy a package along with the config you provide the password in it and one gets set at run time. This is the canonical SSIS DevOps - Arthur
@Arthur so what you're saying is there is a way to to store values that can be supplied to project parameters outside of SSIS in a configuration file? Would you be so kind to elaborate on this as an answer? Regarding deployment - after deploy has been done we have everything configured via Environment variables, so that's not an issue. What we're dealing with is obviously the fact that we're storing passwords in project params. - Kamil Gosciminski
@Arthur do I understand correctly that we should store credentials within a file and feed project parameters with them as a first step within a package? If so, how is this possible to feed project params from the level of package? I can't find configuration for package. I'm using Project Deployment Model. - Kamil Gosciminski
Using the Environment variables should do mssqltips.com/sqlservertip/4810/… - Arthur
@Arthur I've mentioned using environment variables, but that's not the case here. I'm using them as we speak, but the scenario is to be able to change environments within SSIS. - Kamil Gosciminski

2 Answers

2
votes

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.

1
votes

I would propose to use SSIS Project Catalog and Project Environments together with the following approach.
Think about SSIS packages as programs or runners, and databases - as resources. Thus, packages are independent from resources, and the resources are configured at package setup phase in specific environment.
In practice, this leads to the following configuration and activities:

  • Packages are created and developed in SSIS Project Mode. All connection manages are declared at Project level.
  • Do not save passwords in Packages or Projects.
  • Each environment Project is deployed to has defined Environment variable configuration where we store configuration about databases, namely:
    • Connection strings, could be cut and paste from the original package
    • DB name
    • Server name
    • User name if Windows Auth is not used
    • Password if Windows Auth is not used
  • After project deploy, one has to map all project connection params with environment variables. We created a simple C# program for that.
  • Values from environment variables are used at corresponding param values of connections. Moreover, you can store other configuration params there, not only connections.
  • You can have several sets of params at the same environment, and choose set when staring package.
  • Automated testing is done with scripted execution, and environment is specified in testing script.

So, every environment we deploy project to has configuration environment with all connection data. Connectivity params in QA environments are supplied by env engineer; developer does not need to worry about that.