Data Encryption in SQL Server 2008, 2008R2, 2012 (Part 1)

Data is very important asset. If it’s stolen or “misplaced” then that can cost a fortune to the company. SQL Server provides encryption that can avoid the embarrassment and legal issues if data is stolen. Here we will discuss two important levels at which SQL Server supports encryption. In this part 1 of the blog post we will explore column level encryption. In Part 2 we will explorer encryption at the entire database level.

1. Data Encryption (column level encryption)

You can decide and select the columns that are of utmost importance and encrypt the data for those columns. Benefit is that if you have low end server then encrypting and decrypting hand picked columns won’t hurt the performance. Example of candidate columns for encryption would be password, credit card number, SIN number (Canadian, eh ), account number, PIN number etc.

Let’s create a sample database and table and apply encryption at column level

  1: CREATE DATABASE Bank

  2: GO

  3: USE Bank

  4: GO

  5: CREATE TABLE Account

  6: (

  7:   AccountId int NOT NULL IDENTITY(1,1) PRIMARY KEY,

  8:   AccountNumber varchar(50),

  9:   EncryptedAccountNumber varbinary(128)

 10: )

 11: GO

 12: 
AccountNumber will contain the data without any encryption where as EncryptedAccountNumber will contain the encrypted version of AccountNumber.
Now our next step is to create master key. Master key will be used for creating digital certificate and certificate will be used for generating the symmetric key that will finally be used for encrypting data in individual columns.
Create master key:
  1: CREATE MASTER KEY ENCRYPTION BY 

  2: PASSWORD = 'Pa$$w0rd'
 
Create digital certificate
  1: CREATE CERTIFICATE BankCert

  2:    WITH SUBJECT = 'Account Numbers';

  3: GO
Create symmetric key for encrypting data

  1: CREATE SYMMETRIC KEY BankAccountKey

  2:     WITH ALGORITHM = AES_256

  3:     ENCRYPTION BY CERTIFICATE BankCert;

  4: GO

For encrypting the data we use the EncryptByKey function and in order to use this function we have to first open the symmetric key that we created in one of the previous steps. Let’s insert some records:

  1: OPEN SYMMETRIC KEY BankAccountKey

  2:    DECRYPTION BY CERTIFICATE BankCert;

  3: 

  4: --insert original and encrypted values

  5: INSERT INTO Account 

  6: VALUES 

  7: ('123456789', EncryptByKey(Key_GUID('BankAccountKey'), '123456789')),

  8: ('987654321', EncryptByKey(Key_GUID('BankAccountKey'), '987654321'))
 
Let’s query the table and find out how the encrypted data looks like:


In the 3rd column we can see that Account Number is encrypted. Original data occupies 9 bytes but encrypted data is occupying 68 bytes. There’s overhead involved due to encryption.
Now how do we decrypt and read the data? Good question. Actually it’s quite similar to encryption. We use DecryptByKey function and before that we have to open the symmetric key that was used for encrypting the data. Let’s do it
  1: OPEN SYMMETRIC KEY BankAccountKey

  2:    DECRYPTION BY CERTIFICATE BankCert;

  3: GO

  4: 

  5: --list original and decrypted values

  6: SELECT AccountNumber, EncryptedAccountNumber,len(EncryptedAccountNumber) AS Size, CONVERT(varchar, DecryptByKey(EncryptedAccountNumber)) AS DecryptedAccountNumber FROM Account
And that’s about it.
Conclusion:
Column level security is useful for securing hand picked columns. DBA’s will have to make sure that they encrypt the certificates and keys so that we can recover the encrypted data in case of a disaster. Developers are responsible for writing the code that encrypts and decrypts the data. Actually it’s pretty straight forward to create stored procedures that encrypts and decrypts the data.