Saving Passwords in SSIS Packages

June 30, 2008

Browsing the Microsoft TechNet forums under SQL Server Integration Services, I constantly find posts from users with a common thread: “Why won’t my package run once deployed to the server”. A lot of the issues have to do with the saving of passwords in the Connection Managers. This narrative may help to shed some light on what is going on, and what you can do to fix it.

 DISCLAIMER: While the T-SQL scripts included here are fairly simple, there is no guarantee that they won’t do something unexpected on YOUR system that they didn’t do on MINE. Please read through and understand what is going on with each script before running it. Shame on you if you blindly copy a script off the internet and run it without first checking what it does.

Connection Strings

Every time you add a Connection Manager (whether from a Shared Data Source or not), it has a Connection String associated with it. The connection string may be as complex as the example below: 

Data Source=Server_Name;User ID=MyUser;Password=AbC123xYz;Initial Catalog=Db_Name;Provider=SQLNCLI.1;Persist Security Info=True;

or it may be as simple as a path to a text file: “C:\My_Folder\Text_Data.txt”

In any case, the information stored in the Connection String tells SSIS how and where to connect to get the data you want. In many cases, ‘non SQL’ databases such as Oracle, AS400, or other legacy systems may require a user name and password because those systems do not support (Windows) integrated security.

Passwords at Design Time

So you, as a SSIS designer, dutifully configure your SSIS Data Sources and Connection Managers to save usernames and passwords because, like all of us, you don’t want to keep inputting the password every time you go to test run the package. AND, you make the common assumption (like I did early in my SSIS days) that by clicking the box that says, “Save my password”, the password you supply will magicaly persist with the life of the package, no matter where it goes or where is is deployed.

Saving a Connection String Password

Such is not always the case. The password will stay with the package, but only under certain conditions.

Package Protection Levels

One of the Package properties is one called Package Protection Level, the default of which is “Encrypt Senstive With user Key”. (Note that I have added the spaces to the names and settings for clarity and readability here.) This default setting basically says this: “SSIS will encrypt passwords and as long as YOU are the one running the package, then SSIS will know how to DECRYPT those passwords because it is the same USER who saved them.” The trouble comes when you deploy this package (with its saved passwords) to the server, and it gets run under the context of some other user.

If you just look at the setting choices above, it is easy to see that there is no really good choices, as “Encrypt All With Password” or “Encrypt Sensitive With Password” means that you need to supply a password in order to save, deploy, and run the package. And since we have enough passwords floating around already, nobody wants to start another set just for their SSIS Packages. So that leaves many users to try “Server Storage” but get an error.

The other question that may not have been asked yet is, “What happens if the password changes? Do I need to re-design and re-deploy all my packages?”

The answer lies in the dialog box presented to the user when executing the package from within SSIS or using the DTExec or DTExecUI command, or when scheduling a packag to run via SQL Server Agent. Each of these GUI’s has a tab titled “Data Sources” which lists the Connection Managers and their Connection String WITHOUT any saved password info, regardless of the Protection Level you used when saving the package. The thing to do is check off any Connection Manager in this list that requires a password and manually edit the Connection String to include it.

Additional Disclaimer

Some kind readers of this blog have responded with the comment that when following these steps above, the SQL Agent did not save the changes they made in the DataSources tab. I do remember seeing some similarly strange bahavior a while back, but was never able to pinpoint the cause. I am certainly not discounting those responders who made the claims. I can only offer that I THINK you will have a better chance of success if you do the edits to the DataSources as the LAST thing you do before saving and closing the dialog box. If any reader can verify or discount these suggestions, I would love to hear from them.

Since I wrote the above disclaimer, I came across a post on the SSIS MS Technet forum. The author said that when using Configurations, the external Configuration will override any Connection String edits done in the dialog box shown above. This may explain the behavior a little more.

Advanced Steps

If you want to take this a step further, review my blog at

https://toddchitt.wordpress.com/2008/06/27/SSIS_configs

This will walk you through the steps to create a Configuration Database in SQL Server that will eliminate the need to manually edit DataSource entries for every job you create.

Good luck, and happy coding!

Todd

Advertisements

Using Various Configuration Settings in SQL Server 2005 Integration Services

June 27, 2008

Introduction

I frequently watch the Microsoft TechNet forums for questions regarding SQL Server 2005 Integration Services. One theme that continually crops up, either in the questions themselves, or the answers given, relates to the use of Configurations in SSIS packages to make the packages easier to maintain as they migrate from Development, to Test, and finally, to Production environments. The methodology I describe here has worked for me in the past and hopefully will work for you as well.

DISCLAIMER: While the T-SQL scripts included here are fairly simple, there is no guarantee that they won’t do something unexpected on YOUR system that they didn’t do on MINE. Please read through and understand what is going on with each script before running it. Shame on you if you blindly copy a script off the internet and run it without first checking what it does!

A Basic Understanding of Environments

With the release of SQL Server 2005, Microsoft made a shift to a more traditional setup regarding the whole DTS/SSIS design environment. Gone are the days when the DBA, or anyone else with proper access, could open a DTS package, edit it (and possibly break it), save it, THEN decide to test it, only to find that: 1) it doesn’t work and 2) the changes cannot be reverted. What IT Manager in their right mind would let a C# or VB.NET coder edit a piece of code in a production environment without that change being fully tested and proven? Such WAS the case with SQL Server 2000 and DTS design environment. With SSIS, you now have the ability to work on your package in a development environment, test it fully, then, once satisfied, send the finished copy off to the test environment, and finally to production.

The Problem

So, you ask, how does one ‘switch’ their package from one environment to the next? Some people out there are going through the arduous task of manually editing each and every connection manager in each and every package when it gets moved from one environment to the next. I recently developed a series of packages that had 50 child packages, each with several Connection Managers. Yes, Shared Data Sources made things a little easier because all the packages used the same set of Data Sources, but there has to be a better way. And with SSIS Configurations, alas, there is.

A Basic Understanding of Configurations

With Configurations, your package has the ability to get settings and values from places EXTERNAL to the package itself. The list of those ‘external places’ includes, amoung others, Registry entries and Environment Variables.  But for this discussion, I will be focusing only on two options: XML Files and SQL Server entries. Open an SSIS Package and right-click on the Control Flow, then select Package Configurations. Check the box Enable package configurations. Familiarize yourself with this dialog box. We’ll be visiting it quite a bit.

Basic Overview

What I’m going to walk you through in this document is a way for a package to first visit an XML Configuration document to retrieve the location of a SQL Server database. This database will hold all the connection string information for all the Connection Managers that the package uses, along with other inportant settings like Variable values, etc. There will be one database per environment (Dev, Test, Prod) running on different instances of SQL Server (which in most cases is different physical servers).

Let’s Get Started

Before you get too far ahead, we need to do some setup work. Open SQL Server Management Studio and connect to your Development server. Create a new database called SSIS_Configuration. (See script below.)

CREATE DATABASE [SSIS_Configuration] ON PRIMARY
NAME = N’SSIS_Configuration’,
FILENAME = ‘D:\MSSQL\SSIS_Configuration.mdf’ ,
SIZE = 10240KB ,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON ( NAME = N’SSIS_Configuration_log’,
FILENAME = N’D:\MSSQL\SSIS_Configuration_log.ldf’ ,
SIZE = 1024KB ,
MAXSIZE = 2048GB ,
FILEGROWTH = 10 %)
GO

 Note: You may need to edit the script to place the mdf and ldf files in their proper place for your environment.

There, now that wasn’t too bad was it? Let’s keep going and add a table:

USE [SSIS_Configuration]
GO 

CREATE TABLE [dbo].[Package_Config](
–[Application_Name] [varchar](50) NULL,
–[Common_Name] [varchar](50) NULL,
[ConfigurationFilter] [nvarchar](255) NOT NULL,
[ConfiguredValue] [nvarchar](255) NULL,
[PackagePath] [nvarchar](255) NOT NULL,
[ConfiguredValueType] [nvarchar](20) NOT NULL
) ON [PRIMARY]

 Notice the first two columns are commented out. The four remaining columns absolutely need to be in this table in order for SSIS to retrieve the info. I just added the other two to mine to give me a better handle of managing the numerous rows that will be saved there. If you want, remove the comments from the first two lines, and/or add an IDENTITY column.

We don’t need to add any data to this table directly. Our first SSIS Package is going to do that for us.

NOTE: This table can be generated from within an SSIS Package, but you will only get the four basic required columns. If you wanted more, you would need to manually edit the table design to add them.

Using Shared Data Sources

In the Business Intelligence Development Studio (BIDS), open the Project/Solution of your SSIS Packages. Under Shared Data Sources, create a new SQL Server Connection to the SSIS_Configuration database above. Make sure you use SQL Native Client as the Provider.

In one of the packages, right-click in the Connection Manager tray and select “New Connection from Data Source”. Choose the SSIS_Configuration connection just added.

NOTE: I design ALL my packages with Shared Data Sources stored at the Package level.

Creating The XML Configuration

Open the Package Configuration Organizer dialog box (remeber that one?) and click “Add”. Select XML as the type, then under the file name, click Browse. Create a new folder on the C:\ drive (this will get saved on the workstation, I named mine C:\SSIS_Config\) and specify a file name. My full path and filename is as follows:

C:\SSIS_Config\SSISConfigDb.dtsConfig

Click Next. Under the “Select Properties to Export” dialog box, navigate to and select ONLY the Connection String property of the Connection Manager for the SSIS_Configuration database as shown below.

Click Next. Give the Configuration a name that makes sense. Example: “SSIS Config ConnStr”. Configuration names must be unique inside a package, but they are for reference purposes only.

Back in the Package Configuration Organizer, click the Add button again. But this time, select the following options:

  • For Configuration type: “SQL Server”
  • For Connection: “SSIS Configurations” (or whatever you named the Connection Manager for your SSIS_Configuration database)
  • For Configuration Table: “[dbo].[Package_Config]” (choose from the list based on what you named your table)
  • For Configuration Filter, enter something like “Connection String for <MyDatabase>”

NOTE: If you are having trouble accessing the database and/or table you created earlier, you may need to click the “NEW” button as appropriate to generate the CREATE sql statement.

Click Next. Under the “Select Properties to Export” dialog box, navigate to and select ONLY the Connection String property of ONE other Connection Manager in the package. Give the Configuration a name. I typicaly use the same name as the Filter from the above step.

Repeat this process for the other Connection Managers in the Package. NOTE: DO NOT fall to the temptation of having only ONE Configuration ‘Filter’ with MULTIPLE Connection String properties associated with it. MAKE SURE you create a NEW ‘Filter’ for each and every Connection String/Connection Manager. (I’ll explain why later!) When finished, close the dialog box.

Just for the heck of it, open your Windows Explorer and look for the XML file you created on your C:\ drive. Open it in NotePad, Explorer, or some other XML editor. See if the tags and values jibe with what you set up. Now open SSMS and query the one table in your SSIS_Configuration database. Look at the various fields and values. If you added the two extended columns like I did, you can manually edit them to suit your needs, but DO NOT TOUCH any other fields. I typically use “Global Connection String” in the Application_Name field and the name of the database for the Common_Name field.

When the package runs, here is what is going to happen: First, the SSIS Run-Time engine will read the settings in the XML file and determine where the SSIS Configuration database is located (because you saved the Connection String property of the SSIS_Configuration database there). Then, knowing that, it will go to that server, database and table and get the Connection String property of all the other Connection Managers in the package, in the order specified in the Configurations dialog box. Only once that is done, will it start moving through your package, executing the tasks and moving data in the Data Flow.

If you now manually edit one of the ConnectionString properties in your SSIS_Configuration database, the next time a package runs that uses that Configuration entry, it will override the design-time ConnectionString with the ConnectionString from the SSIS_Configuration database.

So far, this is not earth shattering, but stick with it for a little longer.

Reusing Configuration Settings in Other Packages

 On subsequent packages, add the Connection Managers from Shared Data Sources in the same way. Then add Configurations for each, starting with the XML entry for SSIS_Configuration database. BUT, when you add each one, the system will determine that the file (or database, table, and filter) already exists. When it does, you will get a message like displayed below.

 Resue Existing Connection

Choose “Reuse Existing” and this will ‘hook’ the Connection String settings of the Connection Managers to the values stored (in the file or table, as appropriate). DO NOT select “Overwrite!”

A good practice would be to create a template package with, as a minimum, the SSIS_Configuration Connection Manager and its Configuration already in place. Then use the template to create regular packages.

One of the keys to making this work across all packages is to use the same ConnectionManager name  for a connection to the same database. (Shared Data Sources takes care of this for you, which is why I like them so much). If you name one Connection Manager as “Customer Contact DB” in one package, and as “Contact info for Customers” in another, you are going to have trouble. Since, in my work place, the database name is the same in Development, Test, and Production environments, I usually use the name of the Database for the name of the Connection Manager. Keeps things simple and consistent.

Migrating to Test and Production

You should be at a point where all the packages you have are set up with Configurations, and they all look for an XML file on the C:\ drive FIRST, then from there look to a SQL Server database to get the rest of the Connection Strings. So how do you move them to the next environment? And here’s the great thing about this method: You don’t need to edit the packages at all! Follow these basic steps:

  1. Copy the XML Config file from your local C:\ drive to the exact same folder location on the TEST server.
  2. Open the XML file in an editor and manually edit the Server Name portion of the tag to reflect the name of the TEST server (or, more accurately, the name of the SQL Server instance that hosts your test environment).
  3. In the SQL Server instance for the TEST Environment, create the SSIS_Configuration database as before.
  4. Create the table with the exact same meta data.
  5. Copy all rows from the Development table to the table on the TEST server.
  6. Manually edit the Connection String properties (stored in the  “ConfiguredValue” field) to reflect where each database is located in the TEST Environment. If you are lucky, you may only need to edit the “ServerName=xxxzzzyyy” portion of the Connection String.

Now install all your packages on the TEST Server. When they go to run, they will look for the xml file (on the TESTserver’s C:\ drive), figure out that the SSIS_Configuration Database is on the TEST server, and go there to get the location (Connection String) of all the Connection Managers in the package.

Follow the same steps to create the table and database in your Production Environment, making sure the XML file is placed in the same location, and edited as needed.

One Filter per Connection Manager

Why one ‘filter’ per Connection? Suppose you have 4 standard database Connections Managers that you use and create your one SQL Configuration ‘filter’ to save all 4 Connection Strings (resulting in 4 rows of data in the table, but all with the same Filter value). Suppose the NEXT package you create only needs 3 of those 4. When you connect to that filter and choose “Resue Existing” it is going to still hook the three Connection Managers’ Connection String property to the Configuration, BUT when it tries to RUN the package, it will try to configure 4 Connection Managers, but only find 3.  One-for-one, in my opinion, is the way to go here.

Configurations Are Not Just For Connection Strings

You are not limited in saving Connection String information in a Configuration table. I typically also save things like Variable Values as well. I recently created a package that needed to loop over all Excel files in a folder, extract data then move each file after processing. I saved the Folder location in a Configuration because in Dev, I had the files in a folder under My Documents, but in Production, the location was a file server. The Configuration entry for the Path saved me from having to re-design the package to run in Production.

Another typical use is for deciding who should recieve the email if and when a package fails. I create many of my packages with an Event Handler set up at the root package level, and on the OnTaskFailed event. I only include a Send Mail Task, and name it “Failure Email”. I then set up a Configuration entry to save the ToLine property of the “Failure Email” task as a configured value. The ‘package path’ is relative, so it is always the same in every package (does not depend on the package name) and the task name is the same for all packages. So now all I do is edit the Configured Value in each of the three databases, and voila, the package will send an email to a different person (or persons) if it fails in a different environment.

Developer’s Tip: Running against TEST or Prod from your Workstation

There were times when I needed to run my package in the Production environment to verify 100% that it would work, but did not want to (or could not) deploy it to the server and test it there. I was able to set up additional SSIS_Config.dtsConfig files on my C:\ drive, each one pointing to a different server. Since the files all need to be named the same, I created three folders under C:\SSIS_Config\ called DEV\, Test\, and Prod\. Each folder contains an exact copy of the XML file with the one value edited as appropriate. Now, if I need to run my package against Production, I simply COPY (don’t move) from the Prod\ folder and overwrite the one in the location that SSIS will look at. Then when I test run the package from withing BIDS, it runs against Production.

NOTE: If you do this, make sure you 1) keep track of what the ‘current config’ is in your XML file and 2) change it back to DEV when finished.

Passwords in your Config Db

Many times, the connection to a database is NOT through integrated security (as much as Microsoft would want us to use), but through a user name and password. When you go to save a Connection String property of this type to the SSIS_Configuration database, you may notice that the password does NOT get saved with the string. This is by design, and not for the scope of this write-up. Just know that you need to go back an manually edit the ConfiguredValue column of the entry in question and add “Password=abcd1234;” in the string.

Overview

Hopefully, you were able to follow these instructions, even thought the screen shots are a little blurry. It took a while to settle on this methodology, and it came from watching a lot of my initial packages fail, reading a lot of error messages, and deleting and recreating Configuration entries of various types before settling on the system laid out here. But it works. Hopefully, it will work for you as well.

If you have issues with attempting to copy these steps, don’t be afraid to post a comment back to the blog. I’ll get notified and make a personal response if warranted. I also monitor the MS TechNet forum for Integration Services found here: http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.integrationsvcs

Good luck, and happy coding!