Sql-server – SSIS best practice to allow easily swappable/ secure/ accessible Database Connections

connectivitySecuritysql serverssis

I come from Pentaho PDI.

There, I learned to have scripts load data to a [VariableDB].

That way, I can either initiate that VariableDB at runtime to "ProdDB" or even set the variable to different DBs within the script, temporarily, whatever.

Point is, I don't have to go through tons of files changing database names too often unless some major architecture changes.

Second thing is security. Pentaho's isn't great but you can at least store some of the database credentials is encrypted format, and they are blocked out in GUIs. A 'mastermind' could crack it but they would need a lot of access anyway.

SSIS — well, it might be the opposite, maybe not. I'd prefer not to store any database passwords in plain text. At the same time, my predecessor left — and I have no idea where the credentials are. They're "poof" gone — I don't know. If SSIS scripts and jobs are running on the same SQL DB server, are they stored somewhere? I'm not sure.

Are they encrypted to his username, and now that he's long gone, unrecoverable?

This is not so important, since I can re-enter database credentials, but I'm looking to the future.

I know there are probably a few ways of doing this, but what's a simple way so that if I have 5 packages pointing to "TheFinanceDatabase" I can easily swap out the database host server, name, user, password, etc?

I take it config files and/ or connection managers — they can be shared between packages within the same "project?". Maybe the "projects" can share connection credentials / pointers within the same "solution"? Just wondering here — thanks!

I want to avoid re-entering/ losing credentials in the future.

Best Answer

When the packages are deploy to and executed from the SSIS Catalog variables from an Environment can be used to store credentials such as those you mentioned, with these then used by the connection managers of packages. Once the value of an environment variable is changed, this is propagated to all packages using this variable. For instance, if you had a variable called "Server" that's used as the ServerName property of a connection manager, and you updated it to hold production server name instead of the test one all packages with this would now be pointed to the prod server. Additionally, environments can also be created for test, development, prod, etc., with the referenced environment on the package then updated to point to a different environment. The variables can also be marked as sensitive to protect information such as passwords. If you haven't installed the catalog (SSISDB), I'd recommend doing so. You can learn more about the SSIS Catalog here. A high level overview of configuring a connection manager to reference an environment variable is below.

  • Right click the package (or project) and select Configure. Then on the References pane press Add and choose the environment you created.
  • Still on the Configure page, go back to the Parameters pane, then the Connection Managers tab. For the connection manager that will be configured, select the property that will be set from an environment variable by clicking the ellipsis next to the property and select the appropriate environment variables on the next window that is displayed.
  • When running a package that references an environment from SQL Agent make sure to update the job step that calls the package with an environment reference. This can be done on the Job Step by going to the Configuration tab, checking the Environment check-box and selecting the referenced environment.
Related Question