Search code examples
azuressis-2019

How to get password from outside of package when using SSIS project parameter

One of my clients uses SSIS packages with project connections. Some of these connections are sybase connections that cannot use Integrated Authentication. Currently a connection string including userid and password is entered into a SSIS environment which is connected to a project parameter which in turn is used to parametrize the project connection. This means that this password is visible in clear text in the environment as well as in the parameters as well as in the logging. It is cleartext in the project.params file. Since Azure Devops is used the protectionlevel has to be set to 'DoNotSaveSensitive'. I know that I can set project parameters (sensitive=true) to prevent the connectionstrings from showing up in the logging. But that obscures the whole connectionstring which for reasons is not practical.

I thought of the following approach:

Three projectparameters per connection

1. $Connection: Data Source=server,12032;User ID=<USER>;Password=<PASSWORD>;Initial
    Catalog=tempdb;Provider=ASEOLEDB.1;Persist Security Info=True;OLE DB
    Services=0; 

2. $User: STVEIJERH (sensitive=true) 

3. $Password: MYPASSWORD (sensitive=true)

Then feed the project connection this expressions as connectionstring:

Replace(Replace($Connection, “<USER>”,$User),”<PASSWORD>”,$Password)

Also I am thinking about storing the password outside of the package. 1st I was thinking of storing the passwords in a (very) secure table and getting it from a database, putting them in a variable and use that variable to build the connectionstring. But since they are project connections that would not work. I thought of using Azure Keyvault but that would run into the same issue as the table/variable approach: you cannot use project connections with variables.

In the end it boils down to these questions:

  1. I have project connections (ASEOLEDB) that need to be supplied a user and a password.
  2. I do not want these passwords in clear text in the project parameters.
  3. I do not want to obscure the whole connectionstring, just the password and userid.

How can I reach these objecrtives?

Solution

  • Azure Keyvault was not available. I set the connections as package connections, during the start of the package a separate package is run that gets the connectionstrings from a secure database and puts them in variables. These are then passed as sensitive parameters to the other packages (that er called as child packages). Effectively this means that the package connections work like project connections.