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:
- I have project connections (ASEOLEDB) that need to be supplied a user and a password.
- I do not want these passwords in clear text in the project parameters.
- I do not want to obscure the whole connectionstring, just the password and userid.
How can I reach these objecrtives?