CODEWITHSUNDEEP
sql-servervisual-studiossisetlsql-server-data-tools
pkExec
pkExec

Reputation: 2066

ConnectionString not encrypted in SSIS integration project

I am building an Integration Services Project in VS 2019.

I have added a Project Connection Manager for an OLE DB/SQL Server.

I have configured the connection string using project parameters. (Project.params file).

Then I changed the project parameter to use "Configurations", in order to apply different values based on the active configuration.

enter image description here

In the Project Property settings, the protection level is set to "EncryptSensitiveWithUserKey".

However, if I examine the .dtproj file, both connection strings (Dev and "Azure") are stored unencrypted. This is a concern, as I want to be able to include the .dtproj file in a repository.

Am I doing something wrong? If it's not possible this way, how else can I securely "save" two connection strings in the project for different configurations?

Upvotes: 3

Views: 958

Answers (1)

Hadi
Hadi

Reputation: 37313

As the protection level is set to EncryptSensitiveWithUserKey, you should configure the project parameters as Sensitive. To do so, click on the Project.params file in the "Solution Explorer"

enter image description here

Then, set the parameter as sensitive. The parameter value will be masked immediately as shown in the image below:

enter image description here

Also, let us check the project.params file in the solution directory.

enter image description here

If we open the file using a text editor, we can check that the parameter value is encrypted.

enter image description here

Update

As the OP mentioned in the comments, setting the connection string parameter as sensitive led to the following exception:

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

Based on the following Microsoft Tech community article:

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.

This means that we should configure the ConnectionString as non-sensitive while adding a separate sensitive parameter for each connection string part we want.

enter image description here

Helpful links:

Upvotes: 1

Related Questions