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.

16 comments:

Jason said...

Thanks for the excellent article.

Saved me a lot of time!!

Jonas said...

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

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

buy wow gold said...

When Wow Gold wolf finally found the wow gold cheap hole in the chimney he crawled cheap wow gold down and KERSPLASH right into that kettle of water and that was cheapest wow gold the end of his troubles with the big bad wolf.
game4power.
The next day the Buy Wow Goldlittle pig invited hisbuy gold wow mother over . She said "You see it is just as mygamegoldI told you. The way to get along in the world is to do world of warcraft gold things as well as you can." Fortunately for that little pig, he buy cheap wow gold learned that lesson. And he just k4gold lived happily ever after!.

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

icemi said...

wedding dress wedding dress discount wedding dress , Learn French , Learn English , Learn Chinese ,Learn Arabic ,Learn Italian , Learn Korean , Learn Portuguese , dvd boxset .wedding dress ,cheapest wedding dress ,cheapest wedding dresses ! Dell GD761 battery Dell GK479 battery Dell GW240 battery Dell PC764 battery Dell 75UYF battery Dell 8N544 battery Dell NF343 battery , ghd ghd straighteners Dell D5318 battery Dell Y9943 battery Dell RN873 battery Dell G5260 battery 312-0660

icemi said...

Vostro 2510 XPS M1210 XPS M1330 XPS M140 XPS M1530 01X284 1691P 1G222 1K500 1X793 2G218 2G248 2M400 2N135 2P700 310-0083 310-0083 310-6321 312-0022 312-0095 312-0279 312-0305 312-0306 312-0314 312-0315 312-0340 312-0383 312-0386

dfadf said...

follow up with another email after a few days
Microsoft Office
often acknowledging that maybe the person
Office 2010
didn't get my message due to email trouble on my
Microsoft Office 2010
end (which, when I was in graduate school, was
Office 2010 key
For repeat offenders, I just use the phone whenever
Office 2010 download
Interesting post. I have been wondering about this issue,so thanks for posting.
Office 2010 Professional
I will be checking back for any new articles
I’ll likely be coming back to your blog. Keep up great writing. Find your great Travel News and sing the songs at Free Song Lyric or you can watch the drama at
Microsoft outlook
Outlook 2010
Thanks for kindly sharing it with us. Very well done indeed
Windows 7
Microsoft outlook 2010

David Bridge said...

Time to turn on Spam filtering me thinks!

Chetan Patil said...
This comment has been removed by the author.
Chetan Patil said...
This comment has been removed by the author.