How to run an SSIS package with sensitive data on SQL Server

Introduction

In this article, we will cover the steps how to run an SSIS package with sensitive data on SQL Server.

In SSIS package sensitive data such as tokens and passwords are by default encrypted by SSIS with your Windows account which you use to create a package. So SSIS will fail to decrypt tokens/passwords, when you run it from another machine using another Windows account. To circumvent this when you are creating an SSIS package which uses authentication components (e.g. an OAuth Connection Manager or an HTTP Connection Manager with credentials, etc.), consider using parameters/variables to pass tokens/passwords. In this way, you won’t face authentication related errors when a package is deployed to a production server.

How to parameterize an SSIS Connection Manager

Most often sensitive data is used by SSIS Connection Manager, in a password, OAuth token, etc. properties. To pass a token or a password dynamically you will have to use a variable/parameter together with expression. 2012 and above versions of Visual Studio or BIDS have a nice way to do it, while in Visual Studio 2008 you will have to set it up manually. Another way is to set sensitive data using SSIS Package Configuration file, which is applicable when you have enabled package deployment model (obsolete method).

Using parameters or variables

Below you will learn how to set a property using a variable/parameter together with an expression. If you are not sure, what property to set, check the documentation of connection managers to find out.

Using a parameter

1. Right click on a Connection Manager and press Parameterize:
   
2. Then select an appropriate property you want to parametrize, e.g. Password, and give a name to the parameter. For OAuth Connection Manager we will use  RefreshToken.
   

NOTE: Applies to SQL Server 2012 and above.

 

Using a variable

Below is an example of how to set any property of a Data Flow using an expression and a variable. You can do the same thing for a Connection Manager – just create a variable, find a property named Password or RefreshToken, etc. and use that variable to set its value:

1. To define an expression on any property of a component firstly go to data flow designer surface. 
2. Now right click anywhere in data flow designer surface and click "Properties" menu item.
3. When Properties window appears, select a property and press [...] button to set an expression. 

Using a configuration file (obsolete method - only use for legacy support)

So if you wanted to parameterize RefreshToken of OAuth Connection Manager, SSIS Package Configuration file would look like this:

NOTE: Configuration File method is obsolete... so only use below method if you have some existing process to use Config file only... We highly recommend you move to new method of using Environment / Parameter (Introduced in 2012) - See next section to use newer approach of Parameter

<?xml version="1.0"?> <DTSConfiguration>     <DTSConfigurationHeading>         <DTSConfigurationFileInfo GeneratedBy="ZappySys\ZappySys" GeneratedFromPackageName="-Sandbox-" GeneratedFromPackageID="{152417C7-3764-4910-91E3-1D35C8FE89AE}" GeneratedDate="2017-11-03 19:22:13"/>     </DTSConfigurationHeading>     <Configuration ConfiguredType="Property" Path="\Package.Connections[OAuth 1].Properties[RefreshToken]" ValueType="String">         <ConfiguredValue>real-refresh-token-value-goes-here</ConfiguredValue>     </Configuration> </DTSConfiguration>

How to pass a variable/parameter to an SSIS Connection Manager

Executing a package from SSIS Catalog

When you are executing a package from SSIS Catalog to set a property you will have to use a parameter.

1. Select SQL Server Agent Job and access its properties. Then select SSIS Catalog as Package source and select your package:
   
2. In Configuration tab parameter will be already present, so just click on [...] button and input your parameter value:
   
   

NOTE: Applies to SQL Server 2012 and above.

 

Using a parameter from SSIS Environment

Another way of passing a parameter when using SSIS Catalog is to use Environment parameter.

1. Create an environment if you don’t have one already and access its properties:
   
2. Click Variables and create a parameter with appropriate type and don’t forget to check Sensitive checkbox:
   
3. Then go to your SSIS Catalog project and click Configure:
   
4. Click References and Add button. Select the environment you had created in the first step:
   
5. Then click Parameters and  [...]  button on the parameter you want to set:
   
6. The result should look similar to this:
   
7. Then in SQL Server Agent Job properties, Configuration tab selects the environment you created. The configured parameter will be set automatically:
   

Executing a package from a File system / SSIS Package Store / SQL Server

When you are executing a package from a File system / SSIS Package Store or SQL Server you will have to use a variable (parameters won’t work).

NOTE: Be aware that to pass sensitive data in this way is not safe because it is visible to everyone. Consider executing a package from SSIS Catalog and using sensitive parameters.

 

Using variables in SQL Server Agent Job configuration

You can pass a variable in SQL Server Agent Job properties in Set values tab:

 

Using a configuration file in SQL Server Agent Job configuration

You can pass configuration file in SQL Server Agent Job properties in Configurations tab:

Contact Us

If you have more question(s) feel free to contact us via Live chat or email to [e-mail]

https://zappysys.com/support/