Column Data Encryption in SQL 2005 at 12:26 PM

How to encrypt Social Security Number or Credit Card Number in SQL database? We can not use one-way hash, which can be used to verify password in some solutions. For SSN, we should be able to decrypt the data when needed.

To speed up process, we should use symmetric key – same key for encryption and decryption. When we add a new record, the key is used to encrypt the data before the record is physically added into data table; when we get the record, the same key is used to decrypt the data before it is returned to client program.

Here are the questions:

1. Where do you save the symmetric key? It is not a good idea to include plain text key in every related SQL statements or stored procedures – it is too easy to hack and too complex to change key!

Ok, I can put the symmetric key (key1) into a central table in SQL server database. To avoid hacking, I also encrypt key1 using another symmetric key (key2) – good :) But where should I put key2 then? The problem to save key2 is very similar to the initial problem to save key1.

2. When the encryption key is changed for security reason, we should decrypt the old data using the old key and then to encrypt all data using the new encryption key. Do we need track which column in which table is encrypted using which key? What will happen if somehow we forget to keep the tracking up-to-date?

Fortunately, SQL Server 2005 builds the encryption feature for us. :)

For question #1, SQL Server uses multiple levels of keys:

Symmetric key --encrypted by--> Database Master Key or certificate --encrypted by--> SQL Server Service Master Key --encrypted by--> Windows DPAPI and the service account credential or the machine key.

The benefit of multiple level keys is that you do not need to provide any password to decrypt a key. SQL Server can decrypt the key by itself. What you need is to use symmetric key name in your code.

Below is a sample from http://msdn2.microsoft.com/en-us/library/ms179331.aspx:

USE AdventureWorks;
GO

--If there is no master key, create one now. 
IF NOT EXISTS 
    (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
    CREATE MASTER KEY ENCRYPTION BY 
    PASSWORD = '23987hxJKL969#ghf0%94467GRkjg5k3fd117r$$#1946kcj$n44nhdlj'
GO

CREATE CERTIFICATE HumanResources037
   WITH SUBJECT = 'Employee Social Security Numbers';
GO

CREATE SYMMETRIC KEY SSN_Key_01
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE HumanResources037;
GO

USE [AdventureWorks];
GO

-- Create a column in which to store the encrypted data.
ALTER TABLE HumanResources.Employee
    ADD EncryptedNationalIDNumber varbinary(128); 
GO

-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY SSN_Key_01
   DECRYPTION BY CERTIFICATE HumanResources037;

-- Encrypt the value in column NationalIDNumber with symmetric 
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);
GO

-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data.
OPEN SYMMETRIC KEY SSN_Key_01
   DECRYPTION BY CERTIFICATE HumanResources037;
GO

-- Now list the original ID, the encrypted ID, and the 
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.
SELECT NationalIDNumber, EncryptedNationalIDNumber 
    AS 'Encrypted ID Number',
    CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber)) 
    AS 'Decrypted ID Number'
    FROM HumanResources.Employee;
GO

To support server cluster, you can export Service Master Key, Database Master Key, symmetric key and certificate to another server.

For question #2, when a key is to be changed, you should use SQL command to change or recreate a key, so that SQL Server can decrypt old data and encrypt data using the new key automatically.

The encrypted data begins with symmetric key GUID. You can define several symmetric keys to encrypt different data columns. SQL Server can get proper key using the GUID part.