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.
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
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.
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
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 = '[email protected]' 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
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
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
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
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.