SSIS and Passwords

Question

Hello,

I have a number of SSIS packages that are launched by Windows Services. These packages point to any number of different databases, and the connection information is known only by the service at run-time.

I know that I can't save connection strings inside the package, but it seems that I can't even pass in a complete "connection string" as a variable; when I do, the package errors out and it appears that the password is removed.

What I can do, however, is pass in the parts of the connection string that I need and then re-assemble them into a working connection string. I do NOT want to do this; it seems sloppy and ill-advised.

Anyone have any thoughts on how to accomplish this?

Answer 1

You can save XML package configuration files. They too will strip out the passwords, but you can add them in by hand. I just keep them on a secure location on the servers, since they are XML, and the passwords are not encrypted. Anyone who can get to XML file can get to the database on that server anyway.

When you're creating a job to run the package, you can specify the config file to use and its location. If you're developing, the config file is on your computer. Once it's on the server, have the config file put in a secure location on that same server.

Answer 2

The proper deployment and configuration of SSIS packages isn't as easy as it first seems. There's a really good walk-through on MSDN about the right way to do it (including connection string configuration and password storage):

Depoloying Integration Services Packages

...in the end you end up with a deployment package that you install on the server, and then use a combination of XML Configuration files and Environment variables to configure the package.

Answer 3

You can pass data in to SSIS packages as variables, and the values in those variables can be "passed on" to virtually any property or attribute within the package.

One way I've done it is to pass in a servername as a variable, and then for the requisite connection I used Expressions to assign the variable to the connection's ServerName property. I can't (quickly) find a website that describes in detail how to do this, but I learned it from going through the SSIS tutorials in Books Online. (And, looking at a sample package, it appears that yes, you could pass in an entire connection string and assign it to a connection via expressions.)

This is great stuff if you need a fine level of control over what goes into your packages. XML configuration files are effective if your requirements are more straightforward. And definitely be paranoid about storing passwords in C:\Login.txt files -- if you at all can, use NT authentication.