SQL Server 2005 Data Encryption

Key_hierarchySQL Server 2005 includes new encryption capabilities that all administrators, programmers and database analyst should be aware of.  Key and Certificate creation and management functions are now an integral part of SQL Server 2005.  You have the flexibility to create your own X.509 certificates, use Windows Server 2003 CA issued certs, or use other certs purchased from a trusted certificate authority.  

 

There are a number of levels in the key hierarchy, but you’ll spend most of your time with database level certs and symmetric keys.  See the SQL Server 2005 Encryption Hierarchy article for a detailed description of certificates, asymmetrical and symmetrical keys.  You’ll also notice at the bottom of that article is a link to the SQL Server 2005 Permissions Hierarchy.  I would recommend reading and digesting both.  It’s a short read and will be a good use of your time as we review the following scripts and demos.

Watching the Demos

My team is now disseminating information using a variety of publishing techniques.  See the following scripts demonstrated using Windows Media Video format.  The videos of the demos are now posted at https://channel9.msdn.com/Showpost.aspx?postid=139794 in both Windows Media Video format as well as Macromedia Shockwave Video format.  See the buttons at the bottom of that post for the full screen versions.

Setting Up To Use Encryption

In our first script and demo, we are going to create a number of objects.  We’re going to create a user id, login id, a sample database,  and a sample table.  Later, we’ll create and use some views into the data along with a helper function that will allow us to control access to the data.  I’m going to cut a lot of the comments from the original script(s).  I will however highlight or link (links are in red) important function calls and features in the scripts.  You’ll notice that this first script is fairly straight forward.  You’ll also notice that the definition for CardNumber doesn’t reveal anything out of the ordinary.  It certainly doesn’t indicate the contents of that column will be encrypted.  This provides a little bit of stealth but not much.  The real power is in the encryption which you see soon.

Setup.sql – it’s purpose is to create a sample database, id and table to use.  Nothing fancy.

CREATE DATABASE [DataEncryptDemo]go

USE [DataEncryptDemo]
go

CREATE LOGIN [login_low_priv] WITH PASSWORD = 'Login1 Password!'
CREATE USER [user_low_priv] FOR LOGIN [login_low_priv]
go

CREATE TABLE [dbo].[CreditCards]( CardId INT PRIMARY KEY ,
CardNumber varbinary(256) )
go

Key and Certificate Creation

CreateSecrets.sql – now we are starting to get into the good stuff. You’ll notice we are going to create a master symmetrical key to start things off (hyper linked below). The next few lines of the script create the certificate and symmetric key we’ll use to encrypt and decrypt data we’ll add or retrieve from the demo table.

USE [DataEncryptDemo]go

-- Create the DB master key. -- Notice that the password may be subject to password policy verification, depending on your system.CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'DB Master key password!'go

CREATE CERTIFICATE [cert_SecretTable_SecretData_Key]
WITH SUBJECT = 'SecretTable_SecretData_Key protection'
go

-- You can also use other encryption algorithms like AES_128 if your system supports it
CREATE SYMMETRIC KEY [SecretTable_SecretData_Key]
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE [cert_SecretTable_SecretData_Key]
go

Encrypting Data and Testing Encrypted Contents

EncryptData.sql – in this script we start to do the fun stuff. You’ll notice we first open the keys created in the prior script. We are going to call key_guid() to get the symmetric key GUID from the database.  Now that we have it, we’ll call the encryptbykey() function with that GUID and the data to be encrypted on the two inserts into the table.  After that occurs, the credit card values are safely locked inside the table rows.  You can see in the script we try to retrieve those rows using a standard select.  This fails to display the credit card numbers because we didn’t call a decryption function.  The next test in the script calls the decryptbykey() function.  This of course works nicely.

USE [DataEncryptDemo]
go

-- In order to use the synmmetric key, you need to open it first
OPEN SYMMETRIC KEY [SecretTable_SecretData_Key]
DECRYPTION BY CERTIFICATE [cert_SecretTable_SecretData_Key]
go

-- Now insert some secret data into the table
DECLARE @KeyGuid AS UNIQUEIDENTIFIER
SET @KeyGuid = key_guid ( 'SecretTable_SecretData_Key')
IF( @KeyGuid is not null )
BEGIN
INSERT INTO [dbo].[CreditCards] VALUES ( 1, encryptbykey( @KeyGuid, N'4388-1234-1234-1234'))
INSERT INTO [dbo].[CreditCards] VALUES ( 2, encryptbykey( @KeyGuid, N'4549-5678-5678-5678'))
END
ELSE
BEGIN
PRINT 'Failed to obtain the symmetric key GUID'
END

SELECT * FROM [dbo].[CreditCards]

SELECT CardId,
convert( NVARCHAR(100), decryptbykey ( CardNumber )) as 'Card Number'
FROM [dbo].[CreditCards]
go

-- A good recommendation is to close the key after you have finish to encrypt data
CLOSE SYMMETRIC KEY [SecretTable_SecretData_Key]
go

-- Without the key open, the unencrypt function returns NULL
SELECT CardId,
convert( NVARCHAR(100), decryptbykey( CardNumber )) as 'Card Number'
FROM [dbo].[CreditCards]
go

Using A View to Access Encrypted Data

CreateView.sql – this script creates a view that allows our lowly privileged user access to the encrypted data.  The view calls the cert_id() function to retrieve our cert id number.  That number is then used to open and use the symmetric key for decryption of the credit card number.  The problem with this script is that it grants way too much authority and access to the keys used in the decryption process.  We’re going to change that in a minute when we revoke those permissions but use a helper function to provide access to the data.

USE [DataEncryptDemo]
go

CREATE VIEW [dbo].[CreditCardsView]
AS
SELECT CardId as CardId,
convert( nvarchar(50), decryptbykeyautocert ( cert_id ( 'cert_SecretTable_SecretData_Key' ),
null, CardNumber )) as CardNumber
FROM [dbo].[CreditCards]
go

GRANT SELECT ON [dbo].[CreditCardsView] TO [user_low_priv]
go
GRANT CONTROL on certificate::[cert_SecretTable_SecretData_Key]
TO [user_low_priv]
go
GRANT VIEW DEFINITION on symmetric key::[SecretTable_SecretData_Key]
TO [user_low_priv]
go

Using the View to Access Data

UseView.sql – this is a simple script that impersonates the user we created.  Keep in mind for the moment, the user has too much authority over the cert and key.  However, you’ll also notice the view is doing all of the work to decrypt the data and present it.  Nothing fancy here.

USE [DataEncryptDemo]
go

EXECUTE AS USER = 'user_low_priv'
SELECT * FROM [dbo].[CreditCardsView]
REVERT
go

Creating Helper Functions to Access Encrypted Data

CreateView2.sql – now things are starting to get interesting.  Here we start things off by creating a helper function called Cards_decrypted_Helper.  This functions executes temporarily with elevated privilege as DBO.  You’ll notice this function takes the data passed to it and decrypts it using the cert and symmetric key.  So how does the helper function get called?  By the new view of course!!!  The new view is called CreditCardView2.  The view iterates through the rows in the table, calls the helper, decrypts the data, then shows the result.

You’ll also notice we revoke the permissions for user_low_priv to the cert and symmetric key.  After the revocations, we’ll grant access to the view.

USE [DataEncryptDemo]
go

CREATE FUNCTION [dbo].[Cards_decrypted_Helper] ( @SecretData VARBINARY(256))
RETURNS NVARCHAR(50)
WITH EXECUTE AS 'DBO'
AS
BEGIN
RETURN convert( NVARCHAR(50), decryptbykeyautocert( cert_id( 'cert_SecretTable_SecretData_Key' ), null, @SecretData ))
END
go

CREATE VIEW [dbo].[CreditCardsView2]
AS
SELECT CardID as CardID, [dbo].[Cards_decrypted_Helper](CardNumber) as CardNumber FROM [dbo].[CreditCards]
go

REVOKE CONTROL on certificate::[cert_SecretTable_SecretData_Key]
TO [user_low_priv]
go
REVOKE VIEW DEFINITION on symmetric key::[SecretTable_SecretData_Key]
TO [user_low_priv]
go

GRANT SELECT ON [dbo].[CreditCardsView2] TO [user_low_priv]

Testing the Helper Function

UseView2.sql – now that we have all of the access controls in place, we can test CreditCardView2 to see if it really works.  In the first test we are using View2.  It of course works correctly and displays the data.  Our little helper function did all the work and more importantly, we reduced the permission footprint for our lowly user.  The second test, the one using our first view fails due to the fact there is no access to the certificate and key used to decrypt the data. HA!!!

USE [DataEncryptDemo]
go

-- access through new view works without unneeded permissions
EXECUTE AS USER = 'user_low_priv'
SELECT * FROM [dbo].[CreditCardsView2]
REVERT
go

-- no access through the old view
EXECUTE AS USER = 'user_low_priv'
SELECT * FROM [dbo].[CreditCardsView]
REVERT
go

Summary

As you can see, there are some powerful capabilities in SQL Server 2005.  This was a brief look into the encryption functions, view usage, permission models, and help functions.  That ought to get your appetite going.