Column-Level Encryption in SQL Server

Beginning with SQL Server 2005, column-level encryption and
decryption capabilities were made available within the database.  This
provides a compelling solution for situations where one-off types of data need
to be secured beyond your existing authorization, authentication or firewall
settings.  This article provides a brief overview and example of securing a
column using native SQL Server cryptography functions.

Overview

The ways to encrypt data are as varied as the reasons to do
so.  Encryption and decryption can occur at just about any point in the
solution using one of many possible algorithms and tools.  The reasons for
encrypting the data as well as the manner in which the data will be accessed
are important factors in deciding where, when, and how to encrypt and decrypt
sensitive data.

SQL Server has multiple encryption capabilities.  In the
case where encryption is required on a single column, ENCRYPTBYKEY and
DECRYPTBYKEY provide this capability in a fairly simple manner without adding
significant complexity to the management of the SQL Server.

Example

In this example, I’ll show you how to set up an encryption
key on the database.  We’ll create a table and some data to use for the
encryption.  Next the SSN in the table will be encrypted and saved.  Finally,
we’ll decrypt the data for display.

Create the example table

Run the following script to create the table and populate
it.  Notice that the EncryptedSSN column is of type varbinary.  This is because
that is the return type of the ENCRYPTBYKEY function.  Therefore, the encrypted
value has a limit of 8000 bytes.

CREATE SCHEMA [HR]
    AUTHORIZATION [dbo];
GO
 
CREATE TABLE [HR].[Employees](
      [EmployeeID]      [int]         NOT NULL,
      [EmployeeName]    [varchar](50) NULL,
      [SSN]             [varchar](20) NOT NULL,
      [EncryptedSSN]    [varbinary] (200) NULL,
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
(     [EmployeeID] ASC)
)
GO
 
INSERT INTO [HR].[Employees]
VALUES(1, 'Jack Smack', '555-11-3333', NULL)
INSERT INTO [HR].[Employees]
VALUES(2, 'Bob Flob', '555-22-4444', NULL)
INSERT INTO [HR].[Employees]
VALUES(3, 'Hal Van Pal', '555-33-5555', NULL)
INSERT INTO [HR].[Employees]
VALUES(4, 'Gregg Craig', '555-44-6666', NULL)

This gets us the following:

EmployeeID  EmployeeName                                       SSN                  EncryptedSSN
----------- -------------------------------------------------- -------------------- -------------
1           Jack Smack                                         555-11-3333          NULL
2           Bob Flob                                           555-22-4444          NULL
3           Hal Van Pal                                        555-33-5555          NULL
4           Gregg Craig                                        555-44-6666          NULL
 
(4 row(s) affected)

Now we have an employee table with the SSN column filled
in.  However, we don’t want to have SSN plain text in our table, so we are
going to encrypt this column and place the encrypted data into the EncryptedSSN
column.  To do this, we must first create a key.

Set up the Master Key

Before we can set up a symmetric key, we must first create a
master key in the database.  The master key will be used to encrypt the symmetric
key we will create.  It is also used to encrypt certificates and asymmetric
keys.

The master key is encrypted using the Triple DES algorithm
and the supplied password.  The password must meet the Windows password policy
of the server hosting SQL Server.  Use the following code to create a master
key for this example.

CREATE MASTER KEY ENCRYPTION BY 
PASSWORD = 'T3stP@ssword'
GO

It is highly recommended that you backup the database master
key.  See How
to: Back Up a Database Master Key
on MSDN.

Create the Symmetric Key

To perform the encryption on the column, we need a symmetric
key.  Before we can create one, however, a certificate must be created in order
to secure the symmetric key.   As of 2005, SQL Server has the capability of
generating its own self-signed certificates.  CREATE CERTIFICATE can also pull
in X.509 standard certificates from files and assemblies.  Use the following code
to create the self-signed certificate and then create and encrypt the symmetric
key using that certificate.

CREATE CERTIFICATE TestCert
   WITH SUBJECT = 'SSN Encryption';
GO
 
CREATE SYMMETRIC KEY HRKey
    WITH ALGORITHM = DES
    ENCRYPTION BY CERTIFICATE TestCert;
GO

The algorithm used in this example is DES, however there are
many more available for use with the cryptography functions.  For assistance in
deciding which one is right for your solution, see Choosing an
Encryption Algorithm
.

Encrypt Data

With the symmetric key created, the exposed SSNs in our test
table can now be encrypted.  First we have to open and decrypt the symmetric
key as follows.

OPEN SYMMETRIC KEY HRKey
   DECRYPTION BY CERTIFICATE TestCert;

Now we can update the EncryptedSSN column of our Employees
table.

UPDATE [HR].[Employees]
   SET [EncryptedSSN] = EncryptByKey(Key_GUID('HRKey'), SSN);
GO

The table’s data now looks like this:

EmployeeID  EmployeeName   SSN          EncryptedSSN
----------- -------------- ------------ -------------------------------------------------
1           Jack Smack     555-11-3333       0x00AB8586D4448744BA5DF1D9D558711F0100000074FCF21CE8DCC5C28766F49D2A99CF0D03FB9A726004B084A81D09D071346611
2           Bob Flob       555-22-4444          0x00AB8586D4448744BA5DF1D9D558711F010000009906D3CC80EECD92231FE4EB10B1ACBD18EAB14639296CFCF79E1D025C33E600
3           Hal Van Pal    555-33-5555          0x00AB8586D4448744BA5DF1D9D558711F01000000CF404C8A4AD608E9DE194C39F5D92C25B294872570C96C611DA52257E70D53E8
4           Gregg Craig    555-44-6666          0x00AB8586D4448744BA5DF1D9D558711F010000002967C1E9890BD083FF5BB875FE86982D1D85D2633530FE1BDB11E04D315A4710
 
(4 row(s) affected)

Note that the encrypted data is bigger than the source
data.  The size difference between the two depends on the algorithm you
choose.  You should take this difference into consideration when sizing your
encrypted column.

Decrypt Data

Decrypting the data looks very similar to the encryption. 
The DECRYPTBYKEY function is used to perform the decryption using the symmetric
key.  As with the encryption, we must first open and decrypt the symmetric key.

OPEN SYMMETRIC KEY HRKey
   DECRYPTION BY CERTIFICATE TestCert;
SELECT [SSN], 
    CONVERT(VARCHAR, DecryptByKey([EncryptedSSN])) 
    AS 'Decrypted SSN'
    FROM [HR].[Employees]
GO

This gives the results:

SSN                  Decrypted SSN
-------------------- ------------------------------
555-11-3333          555-11-3333
555-22-4444          555-22-4444
555-33-5555          555-33-5555
555-44-6666          555-44-6666
 
(4 row(s) affected)

As you can see, our encrypted data was properly decrypted
for display in this result set.  The last thing we need to do is close the
symmetric key.  It will automatically close when the session closes, but we’ll
go ahead and clean up after ourselves.

CLOSE SYMMETRIC KEY HRKey
GO

Conclusion

It is important to understand the requirements for encrypting
data prior to choosing the means and timing of the encryption.  Should the
database be the preferred location for the encryption, SQL Server has built in capability for encrypting and decrypting data.  The
example in this article covered only a small portion of the cryptography
library made available since SQL Server 2005. 

For More Information

Cryptography
in SQL Server
 

Database
Encryption in SQL Server 2008 Enterprise Edition
 

Create Certificate

Create Symmetric
Key

Cryptographic
Functions

»


See All Articles by Columnist

Deanna Dicken

Deanna Dicken
Deanna Dicken
Deanna Dicken is an architect with approximately 20 years of IT experience. In that time she has worked on many large-scale, mission-critical applications and has been involved at all phases of the lifecycle. She has also contributed to three SQL Server MCSE books, co-authored Learn SQL in a Weekend, and tech edited many other titles. She lives on the outskirts of Indianapolis, Indiana with her husband, Curtis, and children, Kylee and Zach.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles