3

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?

Hadi
  • 36,233
  • 13
  • 65
  • 124
pkExec
  • 1,752
  • 1
  • 20
  • 39

1 Answers1

1

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:

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • Thanks @Hadi. However, if I do that, all my packages throw an error that "The expression will not be evaluated because it contains sensitive parameter variable...". See also this: https://stackoverflow.com/q/28338363/1543677. How else can I use the encrypted connection string? – pkExec Nov 01 '21 at 08:18
  • @pkExec have you tried the suggested solution? Adding a separate parameter for the password and configuring it as sensitive, while configuring the connection string property as non-sensitive. – Hadi Nov 01 '21 at 08:58
  • yes, that works, but that leaves hostname, db name, and username unencrypted. – pkExec Nov 01 '21 at 09:12
  • @pkExec I think you cannot encrypt the whole connection string, you may encrypt the part you want using separate parameters – Hadi Nov 01 '21 at 09:14
  • @pkExec please check my answer update – Hadi Nov 01 '21 at 09:28
  • 1
    Ok, do you mind adding that last comment to your answer so that I mark it as accepted? Thanks – pkExec Nov 01 '21 at 09:28