SQL Server Database Encryption

I recently went to the local SQL Server user group meeting were there was a presentation on database encryption. The main focus of the presentation was an overview of how encryption works and how to configure encryption to secure one or more columns of data in your database. I’m going to provide a brief overview of encryption, how to encrypt one column in a table, and how you can encrypt your entire database.

passwords

Introduction

One of the most difficult-to-understand options in SQL Server is the ability to encrypt data. This is mainly because of all of the different encryption capabilities and options offered.

Encryption brings data into a state that cannot be easily interpreted by someone who does not have access to the decryption key, password, or certificates. Though encryption does not restrict the actual access to the data, it ensures that if data loss does occur, the data is useless to the person who does not have the ability to decrypt the data. Though when you use encryption, there should be a maintenance strategy for passwords, keys, and certificates.

To meet the demands of regulatory compliance and corporate data security standards, SQL Server allows you to enable encryption at column-level or on the database level.

Column-level Encryption

Column-level encryption (aka cell-level encryption) was introduced in SQL Server 2005 and is available in all editions of SQL Server, including the free SQL Server Express edition. To use cell-level encryption, the schema must be changed to varbinary, then reconverted to the desired data type. This means the application must be changed to support the encryption-decryption operation; in addition, it can impact database performance. Encryption of the database occurs at the page level, but when those pages are read to buffer pool, they’re decrypted. Data can be encrypted using a passphrase, an asymmetric key, a symmetric key, or a certificate.  The supported algorithms for column-level encryption are AES with 128,196,256 bit keys and 3DES. You can read more about these algorithms here.

This level of encryption is the preferred method of encryption. Only columns containing important data should be encrypted; this will result in less CPU load than the whole database level encryption. If a column is used as primary key, or used in comparison clauses (WHERE clauses, JOIN conditions) the database will have to decrypt the whole column to do operations involving those columns.

Here are some scripts to help your analysis:

-- Create Database
USE master
GO
CREATE DATABASE EncryptionTest ON PRIMARY 
             (NAME = N'EncryptionTest', FILENAME = N'C:\Encrypt.mdf') 
       LOG ON 
             (NAME = N'Encryption_log', FILENAME = N'C:\Encrypt_log.ldf') 
GO

Let’s create a sample table and populate it with sample data. We will later encrypt one of the two columns of the table.

--Create table and insert data in the table
USE EncryptionTest
GO
CREATE TABLE TestTable (FirstCol INT, SecondCol VARCHAR(50)) 
GO
INSERT INTO TestTable (FirstCol, SecondCol) 
   SELECT 1,'First'
   UNION ALL
   SELECT 2,'Second'
   UNION ALL
   SELECT 3,'Third'
   UNION ALL
   SELECT 4,'Fourth'
   UNION ALL
   SELECT 5,'Fifth'
GO
--Check the content of the TestTable
USE EncryptionTest
GO
SELECT *
FROM TestTable
GO

Every database can have one master key. The database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys present in the database. It uses Triple DES algorithm along with user provided password to encrypt the keys.

--Create Database Master Key
USE EncryptionTest
GO
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'AVeryStongPassword'
GO

Certificates are used to protect encryption keys, which are used to encrypt data in the database. SQL Server 2005 and above has the ability to generate self-signed X.509 certificates.

--Create Encryption Certificate
USE EncryptionTest
GO
CREATE CERTIFICATE EncryptionTestCert
    WITH SUBJECT = 'MyCertificatePassword'
GO

The symmetric key can be encrypted by using any of the certificate, password, and symmetric key, asymmetric key options. We can use many different algorithms for the encrypting key. Supported algorithms are DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192, and AES_256.

--Create Symmetric Key
USE EncryptionTest
GO
CREATE SYMMETRIC KEY TestTableKey
    WITH ALGORITHM = TRIPLE_DES
    ENCRYPTION BY CERTIFICATE EncryptionTestCert
GO

Now add a column of type varbinary to original table, which will store the encrypted value for the SecondCol.

--Encrypt Data using Key and Certificate
--Add Columns to hold the encrypted data in binary
USE EncryptionTest
GO
ALTER TABLE TestTable
ADD EncryptedSecondCol VARBINARY(256) 
GO

Before using the key, it needs to be decrypted by the same method with which it was encrypted. In our example we had used a certificate for encrypting the key. Due to the same reason, we are using the same certificate for opening the key and make it available for use. After it is open and available to use, we can use the encryptkey function and store the encrypted values in the database, in the EncryptSecondCol column.

--Update binary column with encrypted
--data created by certificate and key
USE EncryptionTest
GO
OPEN SYMMETRIC KEY TestTableKey
DECRYPTION BY CERTIFICATE EncryptionTestCert
UPDATE TestTable
SET EncryptSecondCol = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),SecondCol) 
GO

We can now drop the original SecondCol column, because we have now encrypted the data in the EncryptSecondCol column. If you don’t want to drop the column, you can keep it for future comparison of the data as we decrypt the column. In production, you would not want to keep this data unencrypted.

--DROP original column which was 
--encrypted to protect the data
USE EncryptionTest
GO
ALTER TABLE TestTable
DROP COLUMN SecondCol
GO

You can run a SELECT query on your database to verify that your data in the table is now protected, and unauthorized users will have no understanding of the encrypted data if they manage to reach the data.

--Check the content of the TestTable
USE EncryptionTest
GO
SELECT *
FROM TestTable
GO

Encrypted Table

Authorized users 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 same certificate which was used for the encryption. One thing to keep in mind here is that the original column and the decrypted column should have the same data types. If they are of different data types, incorrect values could be reproduced. In our case, we have used a VARCHAR data type for SecondCol and EncryptSecondCol.

--Decrypt the data of the SecondCol
USE EncryptionTest
GO
OPEN SYMMETRIC KEY TestTableKey
DECRYPTION BY CERTIFICATE EncryptionTestCert
SELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptSecondCol)) AS DecryptSecondCol
FROM TestTable
CLOSE SYMMETRIC KEY TestTableKey
GO

Decrypted Table

Transparent Data Encryption

Transparent Data Encryption (TDE) is the primary SQL Server encryption option. It was first available in SQL Server 2008, and with the more recent versions of SQL Server it’s available only in the SQL Server Enterprise edition. TDE enables you to encrypt an entire database. Backups for databases that use TDE are also encrypted. TDE protects the data at rest, which means that the database’s data and log files are encrypted using the AES and 3DES encryption algorithms. TDE is completely transparent to the application and requires no coding changes to implement.

The performance overhead for using TDE ranges from about 3 percent to 30 percent, depending on the type of workload. SQL Server instances with low I/O and low CPU usage will have the least performance impact. Servers with high CPU usage will have the most performance impact.

When you enable or disable TDE, the encryption and decryption operations are scheduled on background threads (called the encryption scan or scan – that scans all database files for encryption when enabling or decryption when disabling TDE) by SQL Server. You can view the status of these operations using the catalog views and dynamic management views as demonstrated next.

Please note, TDE works on data at rest for the entire database by encrypting data when writing to disk and decrypting it when reading from disk at I/O level through the buffer pool. This means, data in the buffer pool remains there in clear text format. Hence, if you want to protect data in the buffer pool with encryption, you need to employ a different technique.

These are the steps you need to perform to enable TDE for a database, assuming you have the required permissions for creating a database master key and certificates in the master database and CONTROL permissions on the user database.

  1. Create a master key – A master key is a symmetric key that is used to create certificates and asymmetric keys.
  2. Create or obtain a certificate protected by the master key – Certificates can be used to create symmetric keys for data encryption or to encrypt the data directly.
  3. Create a database encryption key and protect it by the certificate .
  4. Set the database to use encryption – When you enable TDE on any user database, encryption is also automatically enabled for the tempdb database. This prevents temporary objects that are used by the user database from leaking to disk unencrypted via tempdb database. System databases other than tempdb cannot currently be encrypted by using TDE. You can read more about the supported algorithms here.

Let’s run through some scripts to see some basic examples:

USE master;
--create a database master key (DMK) 
--for the master database
CREATE MASTER KEY ENCRYPTION 
BY PASSWORD = 'AVeryStrongPassword';
--create a certificate for use as the database encryption 
--key (DEK) protector and is protected by the DMK.
CREATE CERTIFICATE Cert4TDE WITH   
SUBJECT = 'Certificate for TDE';
GO
 
Use   AdventureWorks2012
--Create the database encryption key (DEK) 
--encrypted with the certificate created
CREATE DATABASE ENCRYPTION KEY
     WITH ALGORITHM   = AES_256  
      --Supported encryption algorithms are 
      --AES (128-bit, 192‑bit, or 256‑bit keys) 
      --or 3 Key Triple DES
     ENCRYPTION BY SERVER CERTIFICATE Cert4TDE;
GO
 
Use Master
--Enable TDE for your database. 
--This command starts a background 
--thread which runs asynchronously.
ALTER DATABASE AdventureWorks2012 SET ENCRYPTION ON;
GO
 
--To monitor encryption progress 
--you can use this query
SELECT db_name(database_id), 
       encryption_state, 
       percent_complete, 
       key_algorithm, 
       key_length
FROM sys.dm_database_encryption_keys;

You can also disable this encryption by turning the process back off:

Use Master
--Disable TDE for your database. 
--As mentioned, this command
--starts a background thread 
--which runs asynchronously.
ALTER DATABASE AdventureWorks2012 SET ENCRYPTION OFF;

It’s very important (and essential for disaster recovery) to take backup of the keys and certificates in order to restore or attach the encrypted database on another SQL Server instance after restoring these keys and certificates there.

When you use a certificate without taking a backup of it, SQL Server gives this warning:

“Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.”

Please note, enabling encryption does not necessarily change the storage requirements but it does require additional CPU cycles to encrypt and decrypt the data. Additionally, you need to create and use a maintenance strategy in place to store and protect your keys. I recommended you first evaluate the need and then plan for implementation in a test environment before you move anything into production.

Summary

Encryption is a very important security feature of SQL Server 2005 and later. Long keys as well as asymmetric keys create strong encryption and stronger encryption uses lots of CPU cycles to encrypt and decrypt data. Stronger encryption is slower to process, but you will need to weigh performance against the level of required security. You can read more on this subject here and here.

Advertisements

1 thought on “SQL Server Database Encryption”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s