Thursday, May 3, 2007

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.

12 comments:

Jason said...

Thanks for the excellent article.

Saved me a lot of time!!

Unknown said...

Great!
But one question, how do I prevent others to access the information throw the view?

Larry Charlton said...

As presented only the accounts in the view will ever have access to the data. The "achilles" heel of this is that if a hacker can guess the account names that have access, they can move the database to another network and created accounts with the exact same names to access the data. Otherwise the data won't be accessible.

A better way is to use transparent decryption with certificates. Then without the original password used to add the certificate to the server, no one can access the data.

chuckcycles said...

create the VIEW first and then create INSERT, UPDATE, and DELETE as triggers for the VIEW. That helps....



Its working now. dont create the triggers on the configuration table. since i was renaming the table to follow our standards i just lost track of where i was creating them

Mycrof Holmes said...

Having stored your configs in SQL Server how can I select a config when setting up a job in SQL Server

Larry Charlton said...

Using Encrypted configurations is no different than using unencrypted configurations both at run-time and design time. So you can either have specific single configurations or set environment variables to choose your configuration (might be a few others).

The only thing that's important is that whatever account is attempting to access or update the configuration needs to have access.

Unknown said...

Is this code still available for download somewhere?
Thanks for any help.

Sumant said...

If you are working on SQL Server Integration Service (SSIS) packages, it is highly unlikely that you are working in the production environment directly. Alternatively, you might be working in the development environment and later deploying to the production environment. Apart from these environments, you normally have testing and staging environments as well. For all these cases you probably have different servers and folders to work with.

Recently I just came across a good articles on " Windows Server"
Here is its link.

Amit said...

It's really a very interesting article. Hope it will be helpful for all developers those are working on SQL. This gives the more information about on this.

David Bridge said...

Time to turn on Spam filtering me thinks!

Unknown said...
This comment has been removed by the author.
Unknown said...
This comment has been removed by the author.