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.

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:

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:

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 DELETEASBEGINDELETE 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:

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.

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.

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.

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.

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.