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
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.