THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Michael Coles: Sergeant SQL

SQL Server development, news and information from the front lines

SSIS: Mo' Secure Configurations

I ran into an issue the other day and I needed a solution for automically configuring my SSIS packages from securely stored DBMS connection strings. Problem is that most DBMSs don’t support Integrated Authentication—they require a username and password. Storing the username/password combo in the connection string in plain text is a security risk, so the question becomes an issue of storing this information securely.


There are several ways to store configuration data for SSIS packages. Probably one of the easiest and most popular methods is to use .dtsConfig files stored in the file system. This method presents some challenges when you want to secure usernames and passwords in the files. I won’t go into the details, since this post isn’t about .dtsConfig files, but Jamie Thompson does a great job of running down the .dtsConfig file options over at his old blog: http://consultingblogs.emc.com/jamiethomson/archive/2007/04/26/SSIS_3A00_-Storing-passwords.aspx.


After abandoning .dtsConfig files the idea of combining SSIS SQL Server configurations and cell-level encryption popped into my head. I created a quick proof of concept and tested it. Then SSIS guru Jamie Thompson pointed me over to a blog post from Larry Charlton (from way back in 2007!) at http://curionorg.blogspot.com/2007/05/encrypted-sql-server-ssis.html that is very similar to what I came up with. Kudos to Larry for a nice piece of code.  My concept is very similar to Larry’s, but with one major difference in implementation that I’ll cover below.


So the idea is that SSIS, by default, looks for a table named [dbo].[SSIS Configurations] to get its configuration data. This script builds the table:

 

CREATE TABLE [dbo].[SSIS Configurations]

(

      ConfigurationFilter   nvarchar(255) NOT NULL,

      ConfiguredValue       nvarchar(255) NULL,

      PackagePath           nvarchar(255) NOT NULL,

      ConfiguredValueType   nvarchar(20)  NOT NULL

);

GO

 

Here’s a breakdown of the columns that SSIS is looking for:


 

  • ConfigurationFilter holds a name you assign to the configured value. In my sample package I named my entry “Password Connection String”.
  • ConfiguredValue is the actual value. In the sample package this is the actual connection string with username and password.
  • PackagePath is the SSIS package property path. In the example the connection string value is used to configure the connection string of an OLEDB Connection named “Password Login Connection”. The path is “\Package.Connections[Password Login Connection].Properties[ConnectionString]”.
  • ConfiguredValueType tells SSIS the data type of your ConfiguredValue. In this case the data type is “String”.

 

Once you create the [dbo].[SSIS Configurations] table you can populate it with a connection string either through the SSIS designer or using a SQL INSERT statement like the following:

INSERT INTO [dbo].[SSIS Configurations]

(

    ConfigurationFilter,

    ConfiguredValue,

    PackagePath,

    ConfiguredValueType

)

VALUES

(

    'Password Connection String',

    'Data Source=(local);User ID=TestLogin;Password=p@$$w0rd;Initial Catalog=Test;',

    '\Package.Connections[Password Login Connection].Properties[ConnectionString]',

    'String'

);

GO

 

Note: if you use the SSIS designer in BIDS to populate the table with connection strings, it will strip out sensitive information like passwords.


You can use SQL Server security to restrict access to this table, but for my purposes that wasn’t good enough. Due to IT policies I specifically had to obscure the plain text of the connection string since it contained a password. For that I decided to turn to SQL Server cell-level encryption. The trick is that SSIS needs to be able to read the data directly from the table as plain text, but it shouldn’t be stored as plain text.

 

Larry’s implementation uses the T-SQL EncryptByPassPhrase and DecryptByPassPhrase functions, both of which require you to store a plain text passphrase somewhere to encrypt and decrypt your data and limit you to Triple-DES encryption. But the main problem I have with this method is that storing a plain text passphrase somewhere doesn’t solve the security issue—it just passes the buck. The same problem is evident when you try to encrypt SSIS packages by password.

 

Make no mistake, encryption key management is the hardest part of encryption—it’s a simple case of Turtles All The Way Down. Personally I don’t want to take on the responsibility for managing plain text passwords and passphrases—I’d rather let the server do that for me. So my solution needs to eliminate the need to store and pass plaintext passwords and passphrases to encrypt and decrypt the secure data.

 

To secure data using cell-level encryption first we need to do a little setup. The first step is to create a database master key (DMK), as shown below.

 

Note: always backup new encryption keys and certificates as soon as you create them!

 

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Ma5t3rK3yP@55';

GO

 

 

Then we need to create a certificate that is secured by the database master key (DMK). For simplicity I named the certificate ConfigCert.

 

CREATE CERTIFICATE ConfigCert

       AUTHORIZATION TestUser

       ENCRYPTION BY PASSWORD = 'myT3stP@$$'

       WITH SUBJECT = 'Configuration Encryption Certificate',

       EXPIRY_DATE = '20201231';

GO

 

ALTER CERTIFICATE ConfigCert

WITH PRIVATE KEY (DECRYPTION BY PASSWORD = 'myT3stP@$$');

GO

 


Next we create a symmetric encryption key that’s protected by the certificate. I called the symmetric key ConfigSymKey.

 

CREATE SYMMETRIC KEY ConfigSymKey

       AUTHORIZATION TestUser

       WITH ALGORITHM = AES_256,

       KEY_SOURCE = 'I am the very model of the modern major general',

       IDENTITY_VALUE = 'Stella! Stella!'

       ENCRYPTION BY CERTIFICATE ConfigCert;

GO

 

Note: it’s a best practice to always specify a KEY_SOURCE and IDENTITY_VALUE in the CREATE SYMMETRIC KEY statement so you can recreate the same symmetric key when necessary. There is no BACKUP SYMMETRIC KEY statement.

 

Then we need to create a slight variant of the [dbo].[SSIS Configurations] table designed to store the encrypted configuration values, as shown below.

 

CREATE TABLE [dbo].[SSIS_Configurations_Encrypted]

(

      ConfigurationFilter   nvarchar(255)  NOT NULL,

      ConfiguredValue       varbinary(512) NULL, -- This column changed

      PackagePath           nvarchar(255)  NOT NULL,

      ConfiguredValueType   nvarchar(20)   NOT NULL

);

GO

 

The only difference between this table and the [dbo].[SSIS Configurations] table is the ConfiguredValue column. In the encrypted version of the table this column is varbinary instead of varchar, and it’s bigger than it’s unencrypted version. The reasons are that (1) all SQL Server encryption functions return varbinary data, and (2) the encrypted data will always be longer than the plain text.

 

The next step is to drop the [dbo].[SSIS Configurations] table and create a view with the same name.

 

DROP TABLE [dbo].[SSIS Configurations];

GO

 

CREATE VIEW [dbo].[SSIS Configurations]

AS

SELECT ConfigurationFilter,

      CAST

      (

         DecryptByKeyAutoCert

         (

            Cert_ID(N'ConfigCert'), NULL, ConfiguredValue

         ) AS nvarchar(255)

      ) AS ConfiguredValue,

      PackagePath,

      ConfiguredValueType

FROM [dbo].[SSIS_Configurations_Encrypted];

GO
 

The view uses the DecryptByKeyAutoCert function to automatically decrypt the ConfiguredValue column. This works because SSIS does not differentiate between the view and the table of the same name. This is a good thing, because it simplifies our process quite a bit. Our next step is to make sure that inserts and updates to the view properly update the underlying table with encrypted data. For this we create an INSTEAD OF INSERT, UPDATE trigger.

 

CREATE TRIGGER SSIS_Configurations_Trigger

ON [dbo].[SSIS Configurations]

INSTEAD OF INSERT, UPDATE

AS

BEGIN

    SET NOCOUNT ON;

   

    -- SQL treats updates as a logical delete followed by an insert

    -- So look at the deleted virtual table to see if anything was deleted

    DECLARE @IsUpdate char(1) = CASE WHEN (SELECT COUNT(*) FROM deleted) = 0 THEN 'N'

                                     ELSE 'Y'

                                END;

   

    -- Open the symmetric key

    OPEN SYMMETRIC KEY ConfigSymKey

    DECRYPTION BY CERTIFICATE ConfigCert;

   

    -- Do a merge

    MERGE [dbo].[SSIS_Configurations_Encrypted] AS target

    USING inserted AS source

    ON

    (

            target.ConfigurationFilter = source.ConfigurationFilter

            AND target.PackagePath = source.PackagePath

            AND target.ConfiguredValueType = source.ConfiguredValueType

    )

    WHEN MATCHED AND @IsUpdate = 'Y' THEN

        UPDATE SET target.ConfiguredValue = EncryptByKey(Key_Guid(N'ConfigSymKey'), source.ConfiguredValue)

      WHEN NOT MATCHED AND @IsUpdate = 'N' THEN

        INSERT

        (

            ConfigurationFilter,

            ConfiguredValue,

            PackagePath,

            ConfiguredValueType

        )

        VALUES

        (

            source.ConfigurationFilter,

            EncryptByKey(Key_Guid(N'ConfigSymKey'), source.ConfiguredValue),

            source.PackagePath,

            source.ConfiguredValueType

        );

   

    -- Close the symmetric key

    CLOSE SYMMETRIC KEY ConfigSymKey;

END;

GO

 

This trigger intercepts INSERT and UPDATE requests to the [dbo].[SSIS Configurations] view to automatically encrypt the ConfiguredValue column, which is then used to update the encrypted data in the underlying [dbo].[SSIS_Configurations_Encrypted] table. We can re-run the previous INSERT statement from the beginning of the article to test it.

 

INSERT INTO [dbo].[SSIS Configurations]

(

    ConfigurationFilter,

    ConfiguredValue,

    PackagePath,

    ConfiguredValueType

)

VALUES

(

    'Password Connection String',

    'Data Source=(local);User ID=TestLogin;Password=p@$$w0rd;Initial Catalog=Test;',

    '\Package.Connections[Password Login Connection].Properties[ConnectionString]',

    'String'

);

GO

 

A couple of quick SELECT queries reveal the results:

 

SELECT *

FROM dbo.[SSIS_Configurations_Encrypted];

GO

 

SELECT *

FROM dbo.[SSIS Configurations];

GO

 

SELECT query results

The results from the first query show the ConfiguredValue is stored unreadable/encrypted. Querying the view returns the unencrypted plain text.

 

Making a clear fashion statement with the classic belt and suspenders, you can deny permissions to the underlying table and the view to those that don’t need to see it, and you can deny access to the symmetric key and certificate to those same folks. If someone gains access to the view or the table, they still need access to the encryption key and certificate to decrypt the contents. Without this access, anyone querying the view will see NULL in the ConfiguredValue column.


Sample scripts and SSIS package are attached to this entry.

 

UPDATE: Andy L. pointed out that [dbo].[SSIS Configurations] is just the default name SSIS uses for your configuration entries table. You can actually change it in the designer at design time, or in SQL Server at build time. The designer lets you choose which table to point at. The important thing is that the columns need to have the correct names.

Published Monday, January 18, 2010 6:19 PM by Mike C

Attachment(s): Sample_Package.zip

Comments

 

Todd McDermid said:

Stellar post!

January 18, 2010 6:17 PM
 

KK_SSIS said:

Excellent Article !!  ...will this work with SQL 2005 and SQL 2008 both ??

January 22, 2010 1:44 PM
 

Mike C said:

Thanks for the comments.  I only tested with 2008, but the cell-level encryption functionality is the same on 2005 and 2008.  SSIS 2005 can also use SQL Server configurations, so yes it should work on 2005 also.

January 23, 2010 9:08 PM
 

SMurray said:

The Merge statement is not available in SQL 2005.  Any other options?

January 25, 2010 9:51 AM
 

Mike C said:

You can use INSERT and UPDATE instead of MERGE.  I just used MERGE to get rid of the wrapping IF ... ELSE.  It will look something like this:

IF @IsUpdate = 'Y'

BEGIN

 -- Update Statement from inserted virtual table

END

ELSE

BEGIN

 -- Insert Statement from inserted virtual table

END

January 26, 2010 6:59 PM
 

Bill said:

Thanks! Great article.

February 10, 2010 2:17 PM
New Comments to this post are disabled

This Blog

Syndication

News

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement