This is the second post in my SQL Server Database Encryption with NHibernate Series. Check out the others posts here:

• Part 1 of 5: Summary
• Part 2 of 5: Create a database certificate and symmetric key for encryption
• Part 3 of 5: Configure domain and NHibernate mappings to decrypt and read an encrypted value
• Part 4 of 5: Configure domain and NHibernate mappings to encrypt and write a plain-text value
• Part 5 of 5: Wrapping Up

Creating a Database Certificate and Symmetric Key for Encryption in SQL Server

There are several methods of data encryption available within SQL Server; however, I will be focusing on data encryption and decryption via a symmetric key by using the EncryptByKey() and DecryptByKeyAutoCert() functions.

• http://msdn.microsoft.com/en-us/library/ms174361.aspx – EncryptByKey()
• http://msdn.microsoft.com/en-us/library/ms182559.aspx – DecryptByKeyAutoCert()

To use these functions, you need to establish a certificate and symmetric key within your database. You can use the following script template. When selecting names for the certificate and symmetric key below, be sure to write them down, as we will need the names in later steps. Note: I chose to use the AES_256 algorithm, but the minimum requirement for any algorithm in the AES family is Windows Server 2003. If you are not running Windows Server 2003, an alternative algorithm is TRIPLE_DES.

USE [database_name];

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘master_key_pass_phrase’;

CREATE CERTIFICATE = ‘cert_name’ 
WITH SUBJECT = ‘cert_subject’, 
EXPIRY_DATE = ‘12/31/2020’;

CREATE SYMMETRIC KEY sym_key_name 
WITH ALGORITHM = AES_256,
KEY_SOURCE = ‘key_pass_phrase’,
IDENTITY_VALUE = ‘identity_pass_phrase’,
ENCRYPTION BY CERTIFICATE cert_name;

In my examples below, I selected “TestCert” as the certificate name and “TestSymKey” as the symmetric key name.

Encrypting and Decrypting with EncryptByKey and DecryptByKeyAutoCert

Once we have established the database master key, certificate, and symmetric key (as shown above), you are ready to encrypt and decrypt data using EncryptByKey and DecryptByKeyAutoCert.

To encrypt data using EncryptByKey, you pass in the encryption key GUID and the plain text value you wish to encrypt. To obtain the encryption key GUID, you can call the KEY_GUID() function, passing in the encryption key name. Prior to encryption, you also need to open the symmetric key for the current database connection. The following code snippet demonstrates the encryption using the TestSymKey symmetric key for encryption:

DECLARE @encryptedValue varbinary(8000);
OPEN SYMMETRIC KEY TestSymKey DECRYPTION BY CERTIFICATE TestCert;
SELECT @encryptedValue = EncryptByKey(KEY_GUID(‘TestSymKey’), ‘my_salary’);
SELECT @encryptedValue;

To decrypt this data, we can use DecryptByKeyAutoCert, passing in the certificate ID of the certificate used to protect the symmetric used for encryption, NULL for the certificate password (because we chose to protect our private symmetric key with a database master key), and the encrypted value. To obtain the certificate ID, I used the CERT_ID() function, passing in the certificate name. The following code snippet demonstrates decryption using the TestCert certificate:

DECLARE @decryptedValue varchar(MAX);
SELECT @decryptedValue = CAST(DecryptByKeyAutoCert(CERT_ID(‘TestCert’), NULL, @encryptedValue) AS varchar(MAX));
SELECT @decryptedValue;