Column Level Database Encryption using Symmetric Keys

Recently i needed to encrypt a column on (a password field) in a table in SQL Server 2008 database. After a bit of research and reading some great articles by Pinal Dave and Laurentiu Cristofor i decided Symmetric keys were the best approach for what i needed to achieve.

Encryption using Symmetric keys are one of the recommended methods of column level encryption in in SQL Server 2005/2008 for a number of reasons:

Advantages Of Symmetric Keys Encryption

• Performance.Symmetric key encryption is known to be much faster and stronger than their asymmetric counterpart. It uses less overhead on system resources. For some examples and timings between Symmetric and Asymmetric key encryption check out Brian Kelley
• Personally i find the symmeteric key approach very easy to work with.
• Easy to backup and restore your database without alot of re-work or data loss , ill talk about this later on in the article.

When a symmetric key is created, the symmetric key must be encrypted by using at least one of the following: certificate, password, symmetric key, asymmetric key, or PROVIDER. The key can have more than one encryption of each type. In other words, a single symmetric key can be encrypted by using multiple certificates, passwords, symmetric keys, and asymmetric keys at the same time.

Simple Encryption Example

Here is a simple example using the Master key to encrypt the Certificates and Keys in a database.

/* Create Database Master Key */

USE AliciaEncryptionTest
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'MyTestPassword'
GO

Now, you'll need a certificate with which you will encrypt your symmetric key. Certificates are used to safeguard encryption keys, which are used to encrypt data in the database.

/* Create Encryption Certificate */

USE AliciaEncryptionTest
GO
CREATE CERTIFICATE EncryptTestCert
WITH SUBJECT = 'MyEncryptionDatabaseCert'
GO

Once you have your certificates, you can create your key. The symmetric key can be encrypted by using various options such as certificate, password, symmetric key, and asymmetric key.

We can use many types of algorithm while creating Symmetric keys like DES, TRIPLE_DES, TRIPLE_DES_3KEY, AES_128, AES_192, AES_256 etc.



You should try to use the most secure algorithm you can, which is AES_256 in SQL Server 2012. It’s the same back to SQL Server 2005. You should avoid the RC4 algorithms, since they are not terribly secure. Even the DES ones you might avoid, but do some research to understand if you have a need to use anything less than AES_256.

/* Create Symmetric Key */
USE AliciaEncryptionTest
GO
CREATE SYMMETRIC KEY TestTableKey
WITH ALGORITHM = AES_256
BY CERTIFICATE EncryptTestCert
GO



It's as simple as that. You're key is now created and ready to use. To use this key in a simple example , create a table and add a column of type varbinary which will be the column you wish to encrypt.

USE EncryptTest
GO
CREATE TABLE TestTable(FirstCol INT, EncryptSecondCol VARBINARY(256))
GO

Before you can use your symmetric key, you have to open it. The symmetric key remains open for the life of the session. It is good practice to close your key after use as well. Here's how you open and close keys.

/* Update binary column with encrypted data created by certificate and key */
USE AliciaEncryptionTest
GO
OPEN SYMMETRIC KEY TestTableKey

DECRYPTION BY CERTIFICATE EncryptTestCert

INSERT

INTO TestTable VALUES (1,ENCRYPTBYKEY(KEY_GUID('TestTableKey'),'NewPassword' ))

GO

/* Close symmetric key */

CLOSE SYMMETRIC KEY TestTableKey ;

Authorized user can use the decryptbykey function to retrieve the original data from the encrypted column. If Symmetric key is not open for decryption, it has to be decrypted using the same certificate that was used to encrypt it. Decryption uses the same method that was used for encrypting it. Because of the same reason, we are using the same certificate for opening the key and making it available for use.

/* Decrypt the data of the SecondCol */
USE AliciaEncryptionTest
GO
OPEN SYMMETRIC KEY TestTableKey

DECRYPTION BY CERTIFICATE EncryptTestCert
SELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptSecondCol)) AS DecryptSecondCol
FROM TestTable
GO

CLOSE SYMMETRIC KEY TestTableKey ;

GO

/* Clean up database */
USE AliciaEncryptionTest
GO
CLOSE SYMMETRIC KEY TestTableKey
GO
DROP SYMMETRIC KEY TestTableKey
GO
DROP CERTIFICATE EncryptTestCert
GO
DROP MASTER KEY
GO

Backing up and restoring your database to a different server

Often you have to backup your database and restore it to a different server. Sometimes you have data already in an encrypted table and you need to be able to decrypt it from the new server. It is important to note that you cannot backup a symmetric key from one database and copy it to another.

When you create a backup, the symmeteric key is saved as part of the backup, so therefore they are available upon restore. However if the symmeteric  key was ecrypted using a certificate, that may not be available if the restore is to a different box.




Certificates are also stored in the database, so they should be available with the symmetric keys. You don't loose the certificates if you move the database to another server. The only thing you may need to do after restoring a database on a different server, is to restore the SMK encryption of the DbMK. For this, you need to execute the following statements in the database after you restored it:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'DbMK password';ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
Other than this, you don't need to do anything special to be able to work with the encrypted data like you worked on the original server.