Subscribe
Twitter
Full Site
About
RSSBeginning 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.