We use databases to store a lot of information. This can also include sensitive information we don’t want to give everyone access to. While we can use permission or views to ‘hide’ our sensitive information it will still be stored as plain text in our table. We can use column encryption to encrypt that sensitive data inside a table without the need to encrypt our entire database!

In this article we will be using symmetric encryption, since it is mathematically less complex then asymmetric encryption it is a lot faster.

Setting up the test environment

I already created a database to hold my table, so let’s start with creating a test table to hold our data:

-- Create a table to hold our sensitive data
CREATE TABLE sensitive_info
   (
   client_id INT IDENTITY,
   security_number VARCHAR(10), -- Can't be an INT
   security_number_encrypted VARBINARY(128) NULL -- Needs to be VARBINARY
   )
GO

It would seem pretty logical in this case to save our security_number as an INT in the table, however, there are some data type rules for us to be able to use encryption. We can only encrypt data of the type nvarchar, char, varchar, binary, varbinary or nchar. Keep that restriction in mind when working with encryption! We also need to make sure that the column that stores the encrypted data has a data type of varbinary.

Now that we know some of the data type rules of encryption and created the table, let’s fill it with some test data:

-- Insert 1000 records into the table with a random security_number
INSERT INTO sensitive_info
   (
   security_number
   )
SELECT ROUND(CAST(RAND() * 100000000 AS INT), 1.1)
GO 1000

If you query the table you can see we got some good data to test with:

Column encryption

We have all of our test data read so the next step is to create a database encryption master key. We need this master key to generate our certificates which we will use to actually encrypt the data. I supplied a pretty basic password for testing purposes, use a strong one if you plan on doing this in production!

-- Create the database master encryption key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password123!'
GO

Next step is creating a certificate:

-- We need to create a self-signed certificate
CREATE CERTIFICATE SensitiveInfo
WITH SUBJECT = 'SecurityNumber'
GO

We are ready to create the actual (symmetric) encryption key that will encrypt the data for us. We can choose from various algorithms like 3DES and RC4, we will be using AES 128bits. A complete list of algorithms can be found here: http://msdn.microsoft.com/en-us/library/ms188357(v=sql.120).aspx

-- Create a symmetric key
CREATE SYMMETRIC KEY SymSecurityNumber
WITH ALGORITHM = AES_128
ENCRYPTION BY CERTIFICATE SensitiveInfo
GO

Alright, let’s get cryptic!

The first thing we need to do before we can start encrypting data is opening the key we created in the previous step:

OPEN SYMMETRIC KEY SymSecurityNumber
DECRYPTION BY CERTIFICATE SensitiveInfo
GO

Let’s run a update statement against our test table to encrypt the security_number and write the encrypted value to the security_number_encrypted column:

UPDATE sensitive_info
SET security_number_encrypted = ENCRYPTBYKEY(key_guid('SymSecurityNumber'),security_number)
GO

If everything goes right we should see the following results when we run a SELECT against the sensitive_info table:

Looks like the encryption worked!

That wasn’t too bad now was it?

Column decryption

Decrypting column data works more or less as the same way as encrypting in.

We need to open the encryption key first, if we skip this we will get NULL values back in our SELECT:

-- Open the key
OPEN SYMMETRIC KEY SymSecurityNumber
DECRYPTION BY CERTIFICATE SensitiveInfo
GO

Now we can select our data and decrypt it using the DECRYPTBYKEY function:

-- Select the data we need and decrypt the encrypted column
SELECT
client_id,
security_number,
CONVERT(VARCHAR,DECRYPTBYKEY(security_number_encrypted)) AS 'Decrypted'
FROM
sensitive_info
GO

And again, if we run a SELECT against the table we should see decrypted values!

That’s all there is to encrypting a column in a table using a symmetric key!

The biggest challenge will most likely be certificate and key management, which always is the biggest challenge when dealing with certificates in any way.