Transparent Data Encryption in SQL Server 2008

As a Database Administrator, security is one of the most important areas to consider when it comes to protecting the databases that you support. We use various mechanisms and technologies to secure our data and databases such as firewalls, certificates, and data encryption. Having said that although we have secured our environment, questions will always be raisesd regarding database security. Although we have protected our databases, what would happen if someone steals the mdf file or if someone steals the backup file. Although there are few ways to control this senario using third-party solutions up until SQL Server 2008 there has been no native way to handle this problem. SQL Server 2008 introduces a new feature that protects the database called Transparent Data Encryption - TDE which provides protection to the entire database. I recently tested TDE with SQL Server 2008 February CTP, and the result of the testing made me write this article. This article contains:

• What is Transparent Data Encryption?
• Implementation of TDE
• Is my database secure now, as it says?
• What needs to be considered before enabling TDE?
• What is the impact when TDE is enabled?

What is Transparent Data Encryption?
Microsoft SQL Server 2008 introduces another level of encryption, Transparent Data Encryption. TDE is full database level encryption that is not limited to columns and rows but protects both the data files and log files. The implementation of TDE on a database is comparatively very simple and transparent to the applications that connects to the selected database. It does not requires any changes to the existing applications. The protection is applied on the data files and log files as well as the backup files. Once TDE is enabled on a database, the restoring of a backup to another SQL Server instance or attaching data files to another SQL Server instance will not be permitted until the certificate that was used to secure the database encryption key (DEK) is available.

The encryption feature of TDE is applied at the page level. Once enabled, pages are encrypted before they are written to the disk and decrypted before they are read into the memory. It is important to remember that the communication channel between SQL Server and the client application will not be secured and encrypted via TDE.

The diagram below shows how SQL Server encrypts a database with TDE:



Transparent Data Encryption uses a Database Encryption Key (DEK) for encrypting the database that is stored in the database boot record. The DEK is secured by a certificate that is stored in the master database. Optionally, the DEK can be secured by an asymmetric key that resides in a Hardware Security Module (HSM) with the support of Extensible Key Management (EKM). The private key of the certificate is encrypted with the database master key that is a symmetric key, that is usually protected with a strong password. Note that although the certificate can be secured by a password, TDE requires that the certificate is secured by the database master key. The database master key is protected by the service master key that is protected by the Data Protection API.

Implementation of the TDE
As mentioned above, the implementation of TDE is fairly simple. Here is a sample script for enabling TDE on a database called TestDatabase.

-- If the master key is not available, create it.
USE master;
GO
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name LIKE '%MS_DatabaseMasterKey%')
BEGIN
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd4545';
END
GO
-- Create the certificate in the master database.
USE master;
GO
-- Since ENCRYPTION BY PASSWORD is not mentioned, the private key of the certificate
-- will be encrypted by database master key created above.
IF NOT EXISTS (SELECT * FROM sys.certificates WHERE name LIKE '%DEKCertificate%')
BEGIN
CREATE CERTIFICATE DEKCertificate WITH SUBJECT = 'DEK Certificate'
END
GO
-- Create Database Encryption Key (DEK) in the user database
USE TestDatabase
GO
IF NOT EXISTS (SELECT * FROM sys.dm_database_encryption_keys WHERE database_id = DB_ID('TestDatabase'))
BEGIN
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE DEKCertificate
END
GO
-- Check whether the key is created
SELECT DB_NAME(database_id) AS DatabaseName, * FROM sys.dm_database_encryption_keys
-- This should return one row (or more if DEKs have been generated in other databases)
-- with the encryption_state of 1 (1 = unencrypted).
-- Set the DEK on in the TestDatabase.
ALTER DATABASE TestDatabase
SET ENCRYPTION ON
GO

-- Check whether the encryption_state is changed to 3. It should be.
SELECT DB_NAME(database_id) AS DatabaseName, * FROM sys.dm_database_encryption_keys

The first two steps show how to creat the database master key and certificate in the master database. Note that the ENCRYPTION BY PASSWORD is not specified with the CREATE CERTIFICATE, hence the private key of the self-signed certificate will be secured by the database master key. The next step shows the way of creating the DEK in the TestDatabase. Execute the code. It adds the DEK to the TestDatabase. If the certificate's private key is secured with a password, you will get error message like below;

Msg 33101, Level 16, State 1, Line 4
Cannot use certificate 'DEKCertificateTest', because its private key is not present or it is not protected by the database master key. SQL Server requires the ability to automatically access the private key of the certificate used for this operation.

The sys.dm_database_encryption_keys allows you to see that the DEKs have been added to server. The column encryption_state indicates whether the DEK is in the following states unencrypted, encryption in progress, encrypted, key change in progress, and decryption in progress for the values of 1, 2, 3, 4, and 5 respectively. When you run the DMV before setting the ENCRYPTION, the state appears as 1 and, once set, state will be appeared as 3. Done. The TestDatabase is now fully secured.



Is my database secure now?
Though we have successfully enabled TDE on our database, we need to make sure that it is secured at all levels. We will do two tests on this. First we will backup the database and try to restore the backup on another SQL Server 2008 instance. The restore operation must fail unless the certificate used for securing the DEK's private key is available in the master database. Secondly, we will try to attach the mdf and ldf files of the TestDatabase in another instance. It should not work too. Here is the code for testing.

-- First step is backing up the TestDatabase.
BACKUP DATABASE [TestDatabase]
TO DISK = N'E:\TestDatabaseFull.bak'
WITH NOFORMAT, NOINIT, NAME = N'TestDatabase-Full Database Backup'
GO
-- Now connect with another SQL Server 2008 instance.
-- Try to restore the backup we have taken, in the new instance.
-- This statement should be failed.
RESTORE DATABASE [TestDatabase]
FROM DISK = N'E:\TestDatabaseFull.bak'
WITH FILE = 1,
MOVE N'TestDatabase' TO N'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDatabase.mdf',
MOVE N'TestDatabase_log' TO N'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDatabase_log.ldf',
NOUNLOAD, STATS = 10
GO

The first steps backs up the database. The second part needs to run in a different SQL Server 2008 instance. When you try to restore the backup in a different instance of SQL Server, you will get an error message similar to below;

10 percent processed.
20 percent processed.
31 percent processed.
41 percent processed.
52 percent processed.
62 percent processed.
73 percent processed.
83 percent processed.
94 percent processed.
100 percent processed.
Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0x8AD8C0A89476752FCC3D7A7005A2DCF546C38C58'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

You will face for same issue when you try to attach the database to the another instance.

USE [master]
GO
CREATE DATABASE [TestDatabase] ON
( FILENAME = N'D:\Test\TestDatabase.mdf'),
( FILENAME = N'D:\Test\TestDatabase_log.ldf')
FOR ATTACH
GO

The result of above code is;
Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0x8AD8C0A89476752FCC3D7A7005A2DCF546C38C58'.

It works. Our database is secured. The only way to restore or attach the TestDatabase in another instance is, adding the same certificate in the second instance. Study the code given below;

-- backup the certificate
-- The private key will be encrypted by the password specified
BACKUP CERTIFICATE DEKCertificate TO FILE = 'E:\DEKCertificate.cert'
WITH PRIVATE KEY
(
FILE = 'E:\Instance1PrivateKey.key',
ENCRYPTION BY PASSWORD = 'Pa$$w0rd5454'
)
-- create the certificate in the second instance by using the backup
-- Private key will be loaded from the saved file.
USE [master]
GO
CREATE CERTIFICATE DEKCertificate
FROM FILE = 'E:\DEKCertificate.cert'
WITH PRIVATE KEY (
FILE = 'E:\Instance1PrivateKey.key'
, DECRYPTION BY PASSWORD = 'Pa$$w0rd5454'
)

The first part of the code backs up the certificate into a file. It backs up the private key of the certificate too. The password specified in the code is for encrypting the private key. The second part of the code needs to be run in the second instance of SQL Server 2008. It creates a certificate by using the backup certificate. Once the code is run, you will be able to either restore or attach the TestDatabase database into the new instance.

What needs to be considered before enabling the TDE?
There are few things you need think about before enabling TDE on your database. They are;

Does TDE harm the implemented disaster recovery plan?
Think about a simple disaster recovery plan, backup and restore. You may have developed this plan and it works without any problems. You enabled TDE, still no problems, scheduled jobs back up your databases. Assume that the server started producing fatal errors which led you to install the OS and SQL Server again. You may easily go for re-installation without thinking twice, because you have your database backup with you. The problem comes when the databases are restored. You may have full backups of the databases that are not in encrypted format, you may have some of transactional backups that are taken after the TDE enabled, hence encrypted. You do not have the backup of certificates you used for TDE. This will lead you in an unexpected situation. Since you do not have the backup of the certificate used, you will not be able to restore the transactional backups.

Think about the development disaster recovery plan before enabling TDE. If you have plan, make sure that the plan works even after enabling the TDE. This applies not only for the backup and restore strategy, it applies other plans such as log shipping and database mirroring too.

Are there read-only file groups in your database?
If the database has read-only file groups, TDE will fail. When I test this, once the TDE is enabled, the encryption_state will never be 3 (encrypted) but 2 (encryption in progress). I found that SQL Server did not throw any exception while running the TDE code. Somehow, if you open the properties window of the database after enabling TDE, you will see that the value of the property Encryption Enabled is set to true. Let's test this using the code below:

-- create a new database for testing TDE on readonly file groups
USE master
GO
CREATE DATABASE [TestDatabase2] ON PRIMARY
( NAME = N'TestDatabase2_Primary', FILENAME = N'E:\TestDatabase2_Primary.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ),
FILEGROUP [FG1_Default]
( NAME = N'TestDatabase2_FG1', FILENAME = N'E:\TestDatabase2_FG1.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ),
FILEGROUP [FG2_ReadOnly]
( NAME = N'TestDatabase2_FG2', FILENAME = N'E:\TestDatabase2_FG2.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'TestDatabase2_log', FILENAME = N'E:\TestDatabase2_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

-- Set the FG1_Default file group as the default one.
USE [TestDatabase2]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'FG1_Default')
ALTER DATABASE [TestDatabase2]
MODIFY FILEGROUP [FG1_Default] DEFAULT
GO

-- Add a table to the default file group
USE [TestDatabase2]
GO
CREATE TABLE TestTable1 (Id int PRIMARY KEY, [Text] varchar(100))
GO
INSERT INTO TestTable1 VALUES (1, 'hello')

-- Add a table to the FG2_ReadOnly file group
CREATE TABLE TestTable2 (Id int PRIMARY KEY, [Text] varchar(100))
ON [FG2_ReadOnly]
GO
INSERT INTO TestTable2 VALUES (1, 'hello')
GO

-- Set the file group FG2_ReadOnly file group as READONLY
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'FG2_ReadOnly')
ALTER DATABASE [TestDatabase2]
MODIFY FILEGROUP [FG2_ReadOnly] READONLY
GO

-- Create Database Encryption Key (DEK) in the user database
USE TestDatabase2
GO
IF NOT EXISTS (SELECT * FROM sys.dm_database_encryption_keys WHERE database_id = DB_ID('TestDatabase2'))
BEGIN
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE DEKCertificate
END
GO

-- Enable TDE on the database
ALTER DATABASE TestDatabase2
SET ENCRYPTION ON
GO


The code creates a database first with three data files named TestDatabase2_Primary, TestDatabase2_FG1 and TestDatabase2_FG2. The file group FG1_Default is set as the default file group and TestTable1 is created in it. The TestTable2 is created in the FG2_ReadOnly file group. Later, FG1_ReadOnly file group is marked as READONLY.

Finally, DEK is created in the TestDatabase2 and the Encryption property set as true. All statements are successfully executed. If you query sys.dm_database_encryption_keys, you will see that the encryption_state of the TestDatabase2 is 2 that says that encryption is being done but not completed. I am not sure the reason for not throwing an exception at the time of enabling TDE

Is FileStream data type used?
Databases that have filestream type used can be encrypted by using TDE but file stream data will not be encrypted.

What is the impact when TDE is enabled?
Enabling TDE on a database impacts few things;

Transaction log
Once TDE is enabled, SQL Server ensure that log file contains no clear text data by zeroing it out. SQL Server starts with new VLF with an encrypted form.

TEMPDB system database
This will be automatically encrypted once you enable TDE on any database. This may introduce slow performance on non-encrypted databases that use the tempdb database.

Log shipping and Database mirroring
If you enabled TDE on a database that ships logs to another database (means, log shipping enabled database), log shipping operation will fail at the secondary database, unless the certificate is available in the secondary server. I tested TDE on a log shipping enabled database, found that it fails once the TDE is enabled but starts works fine when the certificate used for TDE is available. BOL says that enabling TDE on a log shipping enabled database, encrypts both primary and secondary databases but when I checked, I found the encryption_state of the secondary database is 1 (unencrypted). I am not too sure about the reason, I will be exploring more on this anyway.

Compressed backup
I tested this by taking a compressed backup on a TDE enabled database, seems compression is not much effective on a TDE enabled database. This is how I tested;

-- create a new database for testing compressed backup on TDE enabled database
USE master
GO
CREATE DATABASE [TestDatabase3] ON PRIMARY
( NAME = N'TestDatabase3', FILENAME = N'E:\TestDatabase.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ),
LOG ON
( NAME = N'TestDatabase3_log', FILENAME = N'E:\TestDatabase3_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

-- Create a table and insert some records
USE TestDatabase3
GO
CREATE TABLE TestTable (Id int primary key, [Value] char(8000))
GO
INSERT INTO TestTable VALUES (1, 'value1')
INSERT INTO TestTable VALUES (2, 'value1')
INSERT INTO TestTable VALUES (3, 'value1')
INSERT INTO TestTable VALUES (4, 'value1')
INSERT INTO TestTable VALUES (5, 'value1')
INSERT INTO TestTable VALUES (6, 'value1')

-- Backup the TestDatabase3 without compressing it
BACKUP DATABASE [TestDatabase3]
TO DISK = N'E:\TestDatabase3Full.bak'
WITH NOFORMAT, NOINIT, NAME = N'TestDatabase3-Full Database Backup'
GO

-- Backup the TestDatabase3 with compression
BACKUP DATABASE [TestDatabase3]
TO DISK = N'E:\TestDatabase3Full_Compressed.bak'
WITH NOFORMAT, NOINIT, NAME = N'TestDatabase3-Full Database Backup (Compressed)', COMPRESSION
GO

-- Now enable TDE on this
-- Use the code we used with first example

-- Backup the TestDatabase3 again (after TDE enabled) without compressing it
BACKUP DATABASE [TestDatabase3]
TO DISK = N'E:\TestDatabase3Full_Encrypted.bak'
WITH NOFORMAT, NOINIT, NAME = N'TestDatabase3-Full Database Backup (encrypted)'
GO

-- Backup the TestDatabase3 with compression
BACKUP DATABASE [TestDatabase3]
TO DISK = N'E:\TestDatabase3Full_Encrypted_Compressed.bak'
WITH NOFORMAT, NOINIT, NAME = N'TestDatabase3-Full Database Backup (Encrypted and compressed', COMPRESSION
GO

The code creates a database and inserts some records to a table. Then the database is backed up twice, one without compression and another with compression. The you need to enable TDE on the database and execute the rest that is same code for backing up we used before enabling TDE. Backup files sizes are;

Full backup before enabling TDE	1,365 KB
Full backup with compression before enabling TDE	124KB
Full backup after enabling TDE	1,365 KB
Full backup with compression after enabling TDE	1,278 KB

You can see the difference. The result proves that compressing backup files for TDE enabled databases are not much effective.

This article contains what I tested on TDE. I may have missed something, you may have something that needs to be added to this. I highly appreciate if you can let me know, if you have anything new to be added to this. I would appreciate your comments on this, please send your comments to dinesh@sqlserveruniverse.com.