Data Encryption in SQL Server 2005

One of our product stores sensitive information in the database. Our client wants the sensitive information to be encrypted so that nobody except the authorized person can access to it.

We want to implement the data encryption at the database level so that it becomes transparent to the applications using the data. The developer should not need to take care about data encryption/decryption and equally important our reporting system which is based on Reporting Service should still works.

SQL Server 2000 does not have built-in encryption capability. Data encryption can be done by using extended stored procedures that utilize external dll. This is definitely cumbersome.

It turns out that data encryption is a native feature in the new SQL Server 2005. So without further ado, I spent some time investigating the data encryption feature.

In SQL Server 2005, data can be encrypted using symmetric keys, asymmetric keys, certificates, or passphrases (plain text), being the last option as the least recommended. We can use a combination of several encryption mechanisms to create a hierarchy of encryption. For example, the data is first encrypted using a symmetric key, then the symmetric key is encrypted using an asymmetric key, and so on to make a stronger encryption.

Encryption using certificates and asymmetric keys are slower but more secure than using symmetric keys. Microsoft recommends using symmetric key to encrypt large amount of data and then secure the symmetric key by asymmetric keys or certificates.

The code following this blog entry is my first attempt to test the encryption in SQL Server 2005. I created a hypothetical Employee table that stores salary (as money data type), credit card number (varchar) and birth date (datetime). Since encrypted data is stored as varbinary, those columns are declared as varbinary instead of their original type.

Following Microsoft's recommendation for large amount of data, I use a symmetric key to encrypt/decrypt the data. The symmetric key is then encrypted by a certificate created internally in SQL Server. Since I don't specify any further encryption mechanism to secure the certificate, by default, the certificate is encrypted by the database master key. In the encryption hierarchy, the database master key is further encrypted by the service master key, and the service master key is secured by DPAPI at the operating system level.

I use EncryptByKey and DecryptByKey functions for the encryption/decryption. These functions only accept varchar, nvarchar, char, nchar, varbinary and binary data type. For other data type like datetime and money, we need to CAST/CONVERT it to varbinary. The symmetric key must also be opened/decrypted before we can use it for encryption/decryption.

In the view, I use DecryptByKeyAutoCert that automatically opens the symmetric key and uses it to decrypt the cipher text.


The code:

-- create database
CREATE DATABASE EncryptionTest
GO
 
-- use database
USE EncryptionTest
GO
 
-- create master key for the new database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'
GO
 
-- Create table
CREATE TABLE [dbo].[Employee](
       [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
       [Name] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
       [Position] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
       [Salary] [varbinary](256) NOT NULL,
       [CreditCard] [varbinary](256) NULL,
       [BirthDate] [varbinary](256) NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
       [EmployeeID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
 
-- create certificate to encrypt the symmetric key
CREATE CERTIFICATE EmployeeCert
       WITH SUBJECT = 'Company Certificate',
       START_DATE = '1/1/2006',
       EXPIRY_DATE = '12/31/2006';
GO
 
-- create symmetric key and encrypt it with the certificate
CREATE SYMMETRIC KEY EmployeeKey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE EmployeeCert
GO
 
-- create a view to access Employee table
CREATE VIEW [dbo].[vw_Employee]
AS
SELECT  [Name],
              Position,
              CONVERT(MONEY, DecryptByKeyAutoCert(CERT_ID('EmployeeCert'), NULL, Salary)) AS Salary,
              CONVERT(VARCHAR, DecryptByKeyAutoCert(CERT_ID('EmployeeCert'), NULL, CreditCard)) AS CreditCard,
              CONVERT(DATETIME, DecryptByKeyAutoCert(CERT_ID('EmployeeCert'), NULL, BirthDate), 112) AS BirthDate
FROM dbo.Employee
GO
 
-- *** Batch: Encryption
 
-- open symmetric key
OPEN SYMMETRIC KEY EmployeeKey
DECRYPTION BY CERTIFICATE EmployeeCert
 
-- get symmetric key id to be used in the encryption
DECLARE @KeyGUID uniqueidentifier
SET @KeyGUID = KEY_GUID('EmployeeKey')
 
-- insert some records to Employee table
INSERT INTO dbo.Employee([Name], Position, Salary, CreditCard, BirthDate)
SELECT
       'John Smith',
       'CEO',
       EncryptByKey(@KeyGUID, CONVERT(VARBINARY(256), $200000)),
       EncryptByKey(@KeyGUID, '4444-3333-2222-1111'),
       EncryptByKey(@KeyGUID, CONVERT(VARBINARY(256), CONVERT(DATETIME, '19400502', 112)))
UNION
SELECT
       'Garry Baker',
       'General Manager',
       EncryptByKey(@KeyGUID, CONVERT(VARBINARY(256), $150000)),
       EncryptByKey(@KeyGUID, '4444-3333-2211-1144'),
       EncryptByKey(@KeyGUID, CONVERT(VARBINARY(256), CONVERT(DATETIME, '19450108', 112)))
UNION
SELECT
       'Natasha Smith',
       'Account Manager',
       EncryptByKey(@KeyGUID, CONVERT(VARBINARY(256), $120000)),
       EncryptByKey(@KeyGUID, '4444-1111-1111-1111'),
       EncryptByKey(@KeyGUID, CONVERT(VARBINARY(256), CONVERT(DATETIME, '19550501', 112)))
 
-- close key
CLOSE SYMMETRIC KEY EmployeeKey
GO
 
-- *** Batch: Decryption
 
-- open symmetric key
OPEN SYMMETRIC KEY EmployeeKey
DECRYPTION BY CERTIFICATE EmployeeCert
 
-- select Employee
SELECT  [Name],
              Position,
              CONVERT(MONEY, DecryptByKey(Salary)) AS Salary,
              CONVERT(VARCHAR, DecryptByKey(CreditCard)) AS CreditCard,
              CONVERT(DATETIME, DecryptByKey(BirthDate), 112) AS BirthDate
FROM dbo.Employee
 
-- close key
CLOSE SYMMETRIC KEY EmployeeKey
GO