Encrypted SQL Server SSIS Configurations

There are several aspects of securing SSIS configurations in a SQL Server. The SQL permissions to the configurations themselves, the bytes stored on disk and in backups, the network packets between client and server, and so on. This will deal with encrypting the bytes on disk transparently for SSIS packages.


The standard SSIS configuration table looks like the following:

CREATE TABLE [dbo].[SSIS Configurations](
[ssisConfigurationId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[ConfigurationFilter] [varchar](255) NOT NULL,
[ConfiguredValue] [varchar](255) NULL,
[PackagePath] [nvarchar](255) NOT NULL,
[ConfiguredValueType] [nvarchar](20) NOT NULL,
)

To this we’ll make three small changes. First we’ll change the table name to SSISEncryptedConfigurations, we’ll change the ConfiguredValue to varbinary(512), and finally we’ll add an auto-incrementing primary key. The result will look something like this:

CREATE TABLE [dbo].[SSISEncryptedConfigurations](
[ssisConfigurationId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[ConfigurationFilter] [varchar](255) NOT NULL,
[ConfiguredValue] [varbinary](512) NULL,
[PackagePath] [nvarchar](255) NOT NULL,
[ConfiguredValueType] [nvarchar](20) NOT NULL,
)

Great so now we’ve got a place to store those the encrypted values, next we need to be able to retrieve them unencrypted. Following is a sample view that might be used.

CREATE VIEW [dbo].[SSISConfigurations]
WITH ENCRYPTION
AS
SELECT    [ssisConfigurationId]
 , [ConfigurationFilter]
 , CAST(DecryptByPassPhrase(N'myPassword', [ConfiguredValue]) AS NVARCHAR(255)) AS [ConfiguredValue]
 , [PackagePath]
 , [ConfiguredValueType]
FROM  [dbo].[SSISEncryptedConfigurations]

The DecryptByPassPhrase is what we’ll use to decrypt and we cast it to the original data type nvarchar(255) just to keep SSIS nice an happy. We’ll also use the WITH ENCRYPTION option so that people can’t open our view and read the password! Note that we’re not really recommending the use of myPassword for a password, or for that matter that you use a password you even know. More on that towards the end.

So now we have a store for encrypted values and we can retrieve them, but we still need to be able to add new records. To accomplish that we’ll create an instead of trigger on the view as follows:

CREATE TRIGGER [dbo].[SSISConfigurations_Insert]
ON [dbo].[SSISConfigurations]
WITH ENCRYPTION
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO [dbo].[SSISEncryptedConfigurations] (
     [ConfigurationFilter]
      , [ConfiguredValue]
      , [PackagePath]
      , [ConfiguredValueType]
    )
 SELECT
       [ConfigurationFilter]
       , EncryptByPassphrase( 'myPassword', [ConfiguredValue] )
       , [PackagePath]
       , [ConfiguredValueType]
 FROM inserted
END

Note that the trigger will automatically encrypt our data as it’s headed to the table. It’s important that the password used throughout is consistent. Now we can issue commands like:

INSERT INTO [SSIS].[dbo].[SSISConfigurations]
( [ConfigurationFilter]
, [ConfiguredValue]
, [PackagePath]
, [ConfiguredValueType]
)
VALUES
( 'MyFilter'
, 'MyValue'
, 'The path'
, 'String'
)

And yes we can use SSMS, Microsoft Access, and even the SSIS designer to add records. But adding records is only 1/3 of the data manipulation (DML) story.
Next we need to consider getting rid of that data we just added by deleting it. Following is the trigger to do just that:

CREATE TRIGGER SSISConfigurations_Delete
ON [dbo].[SSISConfigurations]
WITH ENCRYPTION
INSTEAD OF DELETE
AS
BEGIN
DELETE FROM [dbo].[SSISEncryptedConfigurations]
 FROM [dbo].[SSISEncryptedConfigurations] A
 WHERE EXISTS (SELECT 1
       FROM deleted B
       WHERE A.[ssisConfigurationId] = B.[ssisConfigurationId]
      )
END

Pretty straightforward. Here’s the first hint of why we added the primary key field, when we get to updates it’ll really shine. Finally of course we’ll need to be able to update data in our tables. Following is a sample trigger for that:

CREATE TRIGGER SSISConfigurations_Update
ON [dbo].[SSISConfigurations]
WITH ENCRYPTION
INSTEAD OF UPDATE
AS
BEGIN
UPDATE [SSIS].[dbo].[SSISEncryptedConfigurations]
   SET  [ConfigurationFilter] = B.[ConfigurationFilter]
   , [ConfiguredValue] = EncryptByPassphrase('myPassword', CAST(B.[ConfiguredValue] as nvarchar(255)) )
   , [PackagePath] = B.[PackagePath]
   , [ConfiguredValueType] = B.[ConfiguredValueType]
 FROM [dbo].[SSISEncryptedConfigurations] A
   INNER JOIN inserted B
 ON  A.[ssisConfigurationId] = B.[ssisConfigurationId]
END

Presto, we’ve got a way to encrypt and decrypt data from a table that’s only reliant on SQL Server permissions. There are a couple of things to note though. First, if we create the script with the password we’re going to have a problem, we can’t really store the source code without making sure it’s really secure. Instead what I like to do is create random passwords for use when the procedures are created. Then no one knows that password and is tempted to abuse the system. To do that we’ll run something like the following:

DECLARE @pass AS NVARCHAR(64)

SET @pass = REPLACE(CAST(NEWID() AS VARCHAR(36))+CAST(NEWID() AS VARCHAR(36)),'-','')

That gives us a nice 64 byte password that’s not likely to be guessed and that would be different automatically for everyone using the script. Of course you can use any method you like for creating nice secure passwords. I’ll use the sample above in my final script. Another thing for the truly paranoid to consider is what if your backups get stolen? One additional precaution you could take to protect unwarranted access even if the backup is stolen and restored to an unknown machine is check the original login.

WHERE ORIGINAL_LOGIN()='myServiceAccount'

Or

WHERE ORIGINAL_LOGIN() IN ('myServiceAccount','DBA1','DBA2')

And of course the triggers:

IF ORIGINAL_LOGIN() = 'myServiceAccount'
BEGIN
…
ELSE
RAISERROR('Insufficient priviledges', 11, 1);
END

Note that the above method isn’t completely foolproof. If the attacker can figure out what service account you use and figures out your doing something like that, they can always create an account with the exact same name on their own computers. But for most of the real world the only way to get data in or out of the table is through the service account (or your list of accounts). Of course you need to make sure that password and access to running jobs under that ID is restricted, but that’s a problem for another article.

Using this method you should be able to store package configurations for as many packages as you want. If the tables get large you can index ConfigurationFilter to speed things up and all your important secrets will be hidden from at least a few rounds of hackers.

The last thing I should mention is what if you need to change the list of accounts that can view the data?... *cough* it’s not horrible  Just create a temporary table, insert the data from your view. Update your scripts with the new accounts, and then put the data back. I’ll leave that as an exercise for you.

Hope you find this article helpful.

You can download the full script here.