Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Feb 3, 2011

Column-Level Encryption in SQL Server

By Deanna Dicken

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM