SSIS: Configuration of Dynamic Connection String Integrated Security Mode

user_68693 0
Viewed 674

Setup

In the local environments, we're using SQL Authentication with Username and Password to connect to the databases. I created a Project Connection Manager that has expressions bound to Project Properties, Username and Password being set to sensitive.

On the dev server, when the SSIS run, it needs to use an AD account. I might need to create a Credential/Proxy for the SQL Agent, but for now I'm logged in as the user and I execute the packag through SQL.

Problem

In the SSIS project itself, I'm trying to configure a dynamic connection string to use Integrated Security in one case, and SQL Account in another. I just can't figure out how to do it. Things I tried:

1- Created a boolean "UseIntegratedSecurity" parameter. In the connection string, use that bool to set IntegratedSecurity=SSPI or not with the expression, and also use expressions to set the other attributes of the connection string individually. It didn't work, said the connection string could not be built.

2- Created a boolean "UseIntegratedSecurity" parameter, and write my connection string as something (ugly) like: @[$Project::IntegratedSecurity] ? "Data Source="+@[$Project::SqlServerName]+";Initial Catalog="+@[$Project::SqlServerDatabase]+";Provider=SQLNCLI11.1;Auto Translate=False;Integrated Security=SSPI;" : "Data Source="+@[$Project::SqlServerName]+";Initial Catalog="+@[$Project::SqlServerDatabase]+";Provider=SQLNCLI11.1;Auto Translate=False;User ID="+@[$Project::SqlServerUsername]+";Password=" + @[$Project::SqlServerPassword]

It didn't work because since SqlServerUserName and SqlServerPassword are sensitive, it refuses.

3- Tried having Project Parameters for ConnectionString, Server, Database, User, Password and setting them all. Works locally, but on the server, I get "Invalid Authorization Specifications".

Ideas? Thanks

ssis sql-server
1 Answers

You need to handle situation when in one environment you have to use SQL Authentication, and on the other - AD Authentication.
This can be done with help of SSIS Catalog Environment variables. When you create a Project file, Visual Studio automatically creates the following so called project connection parameters for each OLEDB connection manager :

  • CM.< conn manager name >.ConnectionString
  • CM.< conn manager name >.InitialCatalog
  • CM.< conn manager name >.Password Created as sensitive param
  • CM.< conn manager name >.ServerName
  • CM.< conn manager name >.UserName

OLEDB is an example, SSIS creates similar parameters for other connection manager types. Important fact, you do not have to create additional project parameters. The parameters mentioned are created on project being built and are present on all projects.

We create environment variables which specify connection string, DB name (initial catalog), Server Name etc. Good thing - Connection string variable is applied first, and then amended with the other variables.
More details on these parameters is in MS Docs.

In case similar to yours, in Dev environment - using SQL Auth define Conn string for SQL Auth and specify username and password in corresponding variables. In QA env where SSPI is used - the Connection string is reworked for SSPI, UserName and Password environment variables are empty.

user_10106220
Related