SSIS Package Configurations with SQL Encryption

This blog post outlines the implementation approach to use SSIS package configurations as a development and deployment best practice. I have written it as a step by step approach as requested by some of my customers for purposes of setting up and configuring Package Configurations for SSIS. Most companies are using Package configuration, however, are not encrypting the tables in SQL Server.  This is not wise as the password in the connection strings are stored in plain text and can be easily read with some level of thought and execution within the environment. For fun I used an older version of SQL to create this. I highly recommend the latest. I did this to show that it can be used by all versions but SQL Server 2000.

Create Configuration Database

The connection strings and other configuration information for SSIS packages are stored in the Configuration database. Each environment will need to have its own configuration database with its own set of connection strings.

In this example the database name is SSISConfigDB.

Create the Database SSISConfigDB

CREATE DATABASE [SSISConfigDB] ON PRIMARY

( NAME = N’PrimaryFileName’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\SSISConfigDB.mdf’ , SIZE = 2240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

LOG ON

( NAME = N’PrimaryLogFileName’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\SSISConfigDB_log.ldf’ , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

EXEC dbo.sp_dbcmptlevel @dbname=N’SSISConfigDB’, @new_cmptlevel=100

GO

IF (1 = FULLTEXTSERVICEPROPERTY(‘IsFullTextInstalled’))

begin

EXEC [SSISConfigDB].[dbo].[sp_fulltext_database] @action = ‘enable’

end

GO

ALTER DATABASE [SSISConfigDB] SET ANSI_NULL_DEFAULT ON

GO

ALTER DATABASE [SSISConfigDB] SET ANSI_NULLS ON

GO

ALTER DATABASE [SSISConfigDB] SET ANSI_PADDING ON

GO

ALTER DATABASE [SSISConfigDB] SET ANSI_WARNINGS ON

GO

ALTER DATABASE [SSISConfigDB] SET ARITHABORT ON

GO

ALTER DATABASE [SSISConfigDB] SET AUTO_CLOSE OFF

GO

ALTER DATABASE [SSISConfigDB] SET AUTO_CREATE_STATISTICS ON

GO

ALTER DATABASE [SSISConfigDB] SET AUTO_SHRINK OFF

GO

ALTER DATABASE [SSISConfigDB] SET AUTO_UPDATE_STATISTICS ON

GO

ALTER DATABASE [SSISConfigDB] SET CURSOR_CLOSE_ON_COMMIT OFF

GO

ALTER DATABASE [SSISConfigDB] SET CURSOR_DEFAULT LOCAL

GO

ALTER DATABASE [SSISConfigDB] SET CONCAT_NULL_YIELDS_NULL ON

GO

ALTER DATABASE [SSISConfigDB] SET NUMERIC_ROUNDABORT OFF

GO

ALTER DATABASE [SSISConfigDB] SET QUOTED_IDENTIFIER ON

GO

ALTER DATABASE [SSISConfigDB] SET RECURSIVE_TRIGGERS OFF

GO

ALTER DATABASE [SSISConfigDB] SET ENABLE_BROKER

GO

ALTER DATABASE [SSISConfigDB] SET AUTO_UPDATE_STATISTICS_ASYNC ON

GO

ALTER DATABASE [SSISConfigDB] SET DATE_CORRELATION_OPTIMIZATION OFF

GO

ALTER DATABASE [SSISConfigDB] SET TRUSTWORTHY OFF

GO

ALTER DATABASE [SSISConfigDB] SET ALLOW_SNAPSHOT_ISOLATION OFF

GO

ALTER DATABASE [SSISConfigDB] SET PARAMETERIZATION SIMPLE

GO

ALTER DATABASE [SSISConfigDB] SET READ_WRITE

GO

ALTER DATABASE [SSISConfigDB] SET RECOVERY FULL

GO

ALTER DATABASE [SSISConfigDB] SET MULTI_USER

GO

ALTER DATABASE [SSISConfigDB] SET PAGE_VERIFY NONE

GO

ALTER DATABASE [SSISConfigDB] SET DB_CHAINING OFF

Within the SSISConfigDB database create a table to hold the encrypted connection information.

USE [SSISConfigDB]

GO

CREATE TABLE [SSISConfig].[SSISConfigurationsEncrypted](

[ssisConfigurationId] [int] IDENTITY(1,1) NOT NULL,

[ConfigurationFilter] [varchar](255) NOT NULL,

[ConfiguredValue] [varbinary](512) NULL,

[PackagePath] [nvarchar](255) NOT NULL,

[ConfiguredValueType] [nvarchar](20) NOT NULL,

PRIMARY KEY CLUSTERED

(

[ssisConfigurationId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

Next, create the view that will be responsible for un-encrypting the configured values. For this example our Pass Phrase is [email protected]!. It is strongly recommended to change this to another strong password. Use a strong password at all times and the production password should be held by production DBA’s.

USE [SSISConfigDB]

GO

CREATE VIEW [SSISConfig].[SSISConfigurations]

WITH ENCRYPTION

AS

SELECT [ssisConfigurationId]

, [ConfigurationFilter]

, CAST(DecryptByPassPhrase(N’[email protected]!’, [ConfiguredValue]) AS NVARCHAR(255)) AS [ConfiguredValue]

, [PackagePath]

, [ConfiguredValueType]

FROM [SSISConfig].[SSISConfigurationsEncrypted]

GO

Create Triggers for Interactions

The triggers are created on the view to handle the interactions with the SSISConfigurationsEncrypted table processes. These triggers will also be used by BI Development Studio (BIDS) during the Package Configurations setup in package development.

CREATE TRIGGER [SSISConfig].[SSISConfigurations_Insert]

ON [SSISConfig].[SSISConfigurations]

WITH ENCRYPTION

INSTEAD OF INSERT

AS

BEGIN

INSERT INTO [SSISConfig].[SSISConfigurationsEncrypted] (

[ConfigurationFilter]

, [ConfiguredValue]

, [PackagePath]

, [ConfiguredValueType]

)

SELECT

[ConfigurationFilter]

, EncryptByPassphrase( ‘[email protected]!’, [ConfiguredValue] )

, [PackagePath]

, [ConfiguredValueType]

FROM inserted

End

GO

CREATE TRIGGER SSISConfigurations_Update

ON [SSISConfig].[SSISConfigurations]

WITH ENCRYPTION

INSTEAD OF UPDATE

AS

BEGIN

UPDATE [SSISConfig].[SSISConfigurationsEncrypted]

SET [ConfigurationFilter] = B.[ConfigurationFilter]

, [ConfiguredValue] = EncryptByPassphrase(‘[email protected]!’, CAST(B.[ConfiguredValue] as nvarchar(255)) )

, [PackagePath] = B.[PackagePath]

, [ConfiguredValueType] = B.[ConfiguredValueType]

FROM [SSISConfig].[SSISConfigurationsEncrypted] A

INNER JOIN inserted B

ON A.[ssisConfigurationId] = B.[ssisConfigurationId]

END

GO

CREATE TRIGGER SSISConfigurations_Delete

ON [SSISConfig].[SSISConfigurations]

WITH ENCRYPTION

INSTEAD OF DELETE

AS

BEGIN

DELETE FROM [SSISConfig].[SSISConfigurationsEncrypted]

FROM [SSISConfig].[SSISConfigurationsEncrypted] A

WHERE EXISTS (SELECT 1

FROM deleted B

WHERE A.[ssisConfigurationId] = B.[ssisConfigurationId]

)

END

GO

Add a SQL user to access the configurations

In this section the steps for creating a user and granting permissions will be shown.  It is important that when this is set up in the different environments, Development, QA, Production that different logins and passwords are used.  They do not have to be environment matched for Package Configurations to work. That is the brilliance of Package configurations in this implementation.  Three separate environments of access and security to appropriately manage the potential differences between the physical setup of each.

Create user login.

CREATE LOGIN [SSISConfig] WITH PASSWORD=N’$$1$Config’, DEFAULT_DATABASE=[SSISConfigDB], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

Add user to the SSISConfigDB database.

USE [SSISConfigDB]

GO

CREATE USER [SSISConfig] FOR LOGIN [SSISConfigDB] WITH DEFAULT_SCHEMA=[SSISConfig]

—Assign permissions to the [SSIS Configurations] view.

use [SSISConfigDB]

GO

GRANT DELETE ON [SSISConfig].[SSISConfigurations] TO [SSISConfig]

GO

use [SSISConfigDB]

GO

GRANT INSERT ON [SSISConfig].[SSISConfigurations] TO [SSISConfig]

GO

use [SSISConfigDB]

GO

GRANT SELECT ON [SSISConfig].[SSISConfigurations] TO [SSISConfig]

GO

use [SSISConfigDB]

GO

GRANT UPDATE ON [SSISConfig].[SSISConfigurations] TO [SSISConfig]

GO

Set up Environment Variable for Configuration Connection

For each machine that will be running the SSIS packages, an environmental system variable will be created to store the Configurations connection string.

Variable name: ConfigurationConnectionString

Variable value: Provider=SQLOLEDB.1;Password=$$1$Config;Persist Security Info=True;User ID=SSISConfig;Initial Catalog=SSISConfigDB;Data Source=.\SQL2008

Configure Connections

The next step is to configure a connection manager within the package to point to our new configuration table to retrieve configurations for the other connections.

This connection will modified using a package configuration.

Choose Package Configurations from the SSIS menu within Bi Development Studio (BIDS) and check Enable package configurations.

Select Add.

Click Next.

Choose Environment variable for the Configuration type and select the Environment Variable we created. Each environment from development to production will point to corresponding configuration database for that environment. At run-time, SSIS will configure the connections based upon the values stored in the configurations table. This allows the deployment options to be changed easily through T-SQL.

Note: When setting up the different environments the Environmental Variable for the Package Configuration database it is important to name the Environmental variable the same.  If not the packages will need to be edited for each environment.

Choose the ConnectionString property of the connection manager and name the Configuration.

 Click Finish.

Create Configurations

Now the configurations for additional connection managers can be created.

Select Add from the Package Configurations.

Select SQL Server as the Configuration type.

Choose the connection manager that will connect to the configurations database.

Select the [SSIS Configurations] table from the drop down.

Name the Configuration filter for the property of the connection to be set.

Select the corresponding property of the connection to be configured. The default values of the connection will be inserted into the configuration table. These can be overwritten or changed using T-SQL against the [SSIS Configurations] table.

Finally, name the configuration within the table.

Click Finish.

You will see the configurations being accessed within the output window at runtime.

Additional Recommendation

The configuration database will need to be set up for each environment with the appropriate connections. Additionally, environment variables will need to be created for each node within a cluster. Again it is extremely important to name the Environmental Variable the same for each node.