Jason McKittrick's Blog

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 !@Password1!. 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’!@Password1!’, [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( ‘!@Password1!’, [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(‘!@Password1!’, 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.

Environment Variable

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.

Screen2

This connection will modified using a package configuration.

Screen3

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

 

Screen4

Select Add.

 

Screen5

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.

 

Screen6

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

 

Screen7

 Click Finish.

 

Create Configurations

Now the configurations for additional connection managers can be created.

Select Add from the Package Configurations.

Screen8

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.

 

Screen9

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.

 

Screen10

Click Finish.

 

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

Screen11

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.

1 comment for “SSIS Package Configurations with SQL Encryption

  1. IG
    February 25, 2017 at 3:24 pm

    Hey! I understand this is somewhat off-topic. however I had to ask. Does building a well-established website like yours take a massive amount
    work? I’m brand new to blogging however I do write in my journal daily.I’d like to start a blog so I can share my experience and feelings online. Please let me know if you have any ideas or
    tips for brand new aspiring bloggers. Thank you!

Leave a Reply

Your email address will not be published. Required fields are marked *