Introduction

You may have heard about Transparent Data Encryption (TDE), which was introduced in SQL Server 2008. But what does TDE do? How does it help us DBA's?

One of the biggest concerns for a DBA is the "data leak". For instance, suppose your company is dealing with payment processors and stores credit card information, banking information and other personnel information of your clients. What would be your primary concerns? Likely they would be loss of data due to corruption, a hardware issue that could bring your database down, and security.

In this article we will look at the physical security of the database files. Until the release of SQL Server 2008, there was no native method of securing the physical files of the database. There were a couple of third party tools that could secure some things, but they were add-ons. SQL Server 2008 introduced a new feature called Transparent Data Encryption. We will look into some details about what this is, how to implement it, what is the impact of enabling TDE, and the known issues with TDE.

First let us look at what encryption is? Encryption is the process of transforming information in plain text using a cipher, or algorithm, to make it unreadable to everyone other than the person who has the key. There are two types of keys; symmetric and asymmetric. When the same value is used to encrypt and decrypt, then it is known as a symmetric key. An asymmetric key has two parts: one is a private key and the other is a public key. The private key is used to encrypt the data and the public key is used to decrypt the data.

Disclaimer

Please do not execute any of the scripts provided in the article on your production environment before validating and planning them in a test environment.

What is TDE?

TDE is a full database level encryption that protects the data files and log files. As per Microsoft documentation "Transparent data encryption (TDE) performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an EKM module."

One main advantage of TDE is that it does not require any changes to your existing application. This enables DBAs to encrypt data using AES and 3DES encryption algorithms without having to change the applications that will be connecting to the database.

How is the Data Encrypted?

When TDE is enabled, the Server starts a background thread that scans all the database files and encrypts them. This thread actually creates a shared lock on the database. Even when a DDL statement is executed and an Update lock is taken on the database, the encryption thread will run asynchronously using a shared lock. The only operations that are not allowed while the encryption thread runs are modifying the file structure and taking the database offline by detaching it. The scan also rolls over the virtual log file so that the future writes to the log are encrypted.

The supported encryption algorithms are AES with 128, 196 and 256 bit keys or 3 key triple DES. The encryption does not do any padding to the database file. Therefore the size of the database file will stay the same as it currently is even when TDE is enabled. But log files will be padded, thus making them bigger in size.

How do we implement TDE?

Setting up TDE is quite simple. Just four step and you are done. How much simpler can it be?

• Create a master key
• Create or obtain a certificate protected by master key.
• Create a database key and protect it by the certificate.
• Set the database you want to protect to use the encryption.

Now let us try to follow these steps. For creating a Master key, you have to execute a Create Master Key command in the Master Database. Before we create a Master Key, let us check if it already exists.

USE master;
GO
SELECT * FROM sys.symmetric_keys WHERE name LIKE '%MS_DatabaseMasterKey%'
GO

If no other key has been created before, this should not return any rows. Now let us try to create the Master Key.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '';
GO

Once this is executed, if you run the above query, you will get one row with the column name as ##MS_DatabaseMasterKey##. The first step is now done.

The second step is to create a certificate protected by this Master Key. This is done by executing the command Create Certificate in the Master database.

CREATE CERTIFICATE MyTDECert WITH SUBJECT = 'My TDE Certificate'
GO

This statement will create the certificate in sys.certificates. You can see the details of this certificate by executing this query.

SELECT * FROM sys.certificates where [name] = 'MyTDECert'
GO

This will return one row with the column [name] as MyTDECert and with the description as ENCRYPTED_BY_MASTER_KEY. Step number two is also done. We are now half way through.

The next step will be to create an encryption key and protect it by the certificate we just created for the database that needs to be encrypted. From here on please be very careful. You are about to encrypt the database of your choice.

By running the command below, you will create an encryption key for your database.

Use AdventureWorks
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyTDECert
GO

As soon as you execute this in SSMS, it will give the message below. Please read it carefully.

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.

If you run this select statement, you will see that you have created an encryption key for that database. We are almost done now. The only thing that is left to do is to alter the database to set the encryption ON.

ALTER DATABASE AdventureWorks
   SET ENCRYPTION ON
GO

That's it. We are done. We have successfully encrypted the database. Let us confirm it with a couple of steps. If you execute the select statement, it should provide the name and details of the encrypted database.

SELECT DB_NAME(database_id) AS DatabaseName, * FROM sys.dm_database_encryption_keys

When you run this query, it should return one row since we encrypted only one database. But it returns two rows TempDB and AdventureWorks. This is because when you set up one Database to encrypt, it will automatically encrypt tempdb as well. Actually in my test I am not encrypting Adventure works. I am encrypting a test database that I have with a size of 12 Gig. In my case, when I run this query more than once, I can see the last column (Percent_completed) incrementing. Once the encryption is complete, the Percent_complete column will show the value as 0.

Did it really work?

What is a better way to find out if this actually made our database physically secure other than doing a couple of small tests. The two methods we will use to test are restore and attach. First let us take a backup of the Encrypted database.

BACKUP DATABASE [AdventureWorks] TO DISK = N'D:\BackUp\AdventureWorks_Encrypted.bak' WITH NOFORMAT, NOINIT, 
NAME = N'AdventureWorks-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Once this is completed, we will test the restore in a different Server or a different instance of SQL Server 2008. In this case, I tried a restore on a different server using this command.

RESTORE DATABASE [AdventureWorks]
FROM DISK = N'D:\AdventureWorks_Encrypted.bak'
WITH FILE = 1,
MOVE N'AdventureWorks' TO N'D:\BackUp\AdventureWorks.mdf',
MOVE N'AdventureWorks_log' TO N'D:\BackUp\AdventureWorks_log.ldf',
NOUNLOAD, STATS = 10
GO

This will give an error like the one provided below.

Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Now we proved that the backup taken after the encryption cannot be restored on another server without the certificate. This was just the backup restore option. Let us now try the attach database option.

For this test, first we will copy both the LDF and MDF file to the other server. Then run the SQL statement to attach the DB.

CREATE DATABASE [AdventureWorks] ON
( FILENAME = N'D:\BackUp\AdventureWorks.mdf'),
( FILENAME = N'D:\BackUp\AdventureWorks_log.ldf')
FOR ATTACH
GO

When you execute this command, you will get the same error of not finding the certificate with the thumbprint as before. So now we have proven that the database files are secure. The only way to restore or attach the encrypted Database is by adding the same certificate on to the other SQL Server.

To add the certificate to the other server we have to do two things. First you have to back up the certificate onto a file and then create the certificate on the other SQL Server. To back up the certificate, execute a Backup Certificate command.

Use Master
GO


BACKUP CERTIFICATE MyTDECert TO FILE = 'D:\MyTDECert.cert'
 WITH PRIVATE KEY
 (
 FILE = 'D:\EncryptPrivateKey.key',
 ENCRYPTION BY PASSWORD = 'TryToUseOnlyStrongPassword'
 )
GO

Now you have successfully backed up the certificate. You can use this certificate that was backed up to create a certificate in the target server. First you have to create a Master Key on the Target Server. Then we will use the files generated to create the certificate.

USE [master]
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '';
GO
CREATE CERTIFICATE MyTDECert
 FROM FILE = 'D:\MyTDECert.cert'
 WITH PRIVATE KEY (
 FILE = 'D:\EncryptPrivateKey.key'
 , DECRYPTION BY PASSWORD = 'TryToUseOnlyStrongPassword'
 )

You have successfully created a certificate on the other server. Once this is complete, you can restore the encrypted database.

RESTORE DATABASE [AdventureWorks] 
FROM DISK = N'D:\AdventureWorks_Encrypted.bak' 
WITH FILE = 1, 
MOVE N'AdventureWorks_data' TO N'D:\BackUp\AdventureWorks_Data.mdf', 
MOVE N'AdventureWorks_log' TO N'D:\BackUp\AdventureWorks_Log.ldf', 
NOUNLOAD, REPLACE, STATS = 10
GO

You will see that the database has been restored successfully. This proves that the database can only be restored when you have the certificate created in the target server.

Things That Need to be Kept in Mind.

Now we will look at what needs to be thought out in advance before enabling TDE. We will also look at what the after effects of enabling TDE are and the known issues of TDE.

Read Only File Groups and the FileStream Data Type

This is one known issue with TDE. If there are read-only file groups in your database and you try to enable TDE, it will not be successful. Note, I said not successful but I did not say it will fail. There is a subtle difference in this case. If you go through all the steps for setting up TDE like previously stated for a database that has read only filegroup, the encryption will not complete. If you look at
sys.dm_database_encryption_keys,
you will see that the column Encryption_state shows that the value is 2 instead of 3. Value 2 means Encryption in progress. Since the filegroup is set as read-only, the encryption state will not be reached.

When FileStream data type is used, you can encrypt the database but the actual data on the servers file system will not be encrypted.

Do you have a Maintenance/Recovery/Warm Standby plan set for this database?

Let us say that you have a simple maintenance plan where you have a daily backup and transaction log backup every 2 hrs. Let us consider a couple of scenarios.
1. Warm Standby - This is bound to throw errors since the database server where the backup file and transaction log is restored does not have the certificate. You have to create the certificate first before you start restoring.

2. Disaster Recovery Plan of Weekly Back up and Transaction Log - Let us consider the scenario where your OS on your server started throwing some fatal errors due to hardware issues. Since you have your back up and your transaction log, the decision is made to rebuild the server or migrate to a new server. When you try to restore the database backup and transaction log it will throw an error since you do not have a backup of the certificate. So make sure you backup the certificate and keep it safe for emergencies like this.

3. Mirroring of the database will also have issues when enabling TDE. TDE affects database mirroring. You need to install an encryption certificate on the partner servers to be able to mirror the database.

After Effects of enabling TDE.

Every new feature will have its benefits and problems. We have seen the benefits of TDE: encryption on the physical level. Now let us look at the deficiency and problems that arises when using TDE.

TEMPDB

Even though you have encrypted only one database (AdventureWorks), when you do a

SELECT DB_NAME(database_id) AS DatabaseName, * FROM sys.dm_database_encryption_keys

it returns two rows instead of one. One is the database that you encrypted and the other is the TempDB. This will make the non-encrypted database that resides in the same server slow down in performance when it utilizes TempDB.

Compressed Backups

Once TDE is enabled, the compression rate of compressed backups drops down drastically. Making compressed backups is virtually useless. I have shown below figures (Size of Back Up file) of a test database that I used for testing this aspect of TDE.

Full backup with no Compression, No TDE : 11.9 GB
Full Backup with Compression, No TDE : 2.18 GB
Full Backup with no Compression, TDE Enabled : 11.9 GB
Full Backup with Compression, TDE Enabled : 11.9 GB

The actual size in KB that was different between compressed and non compressed back up once TDE was enabled was 22,949 KB.

Known Issue with TDE

While I was doing my research on TDE, I stumbled upon an issue that was raised in SQLServerCentral.com. One of the posters by the name of Amit raised this issue. This issue was then posted in connect by Grant Fritchey aka the Scary DBA who is a well regarded poster. Grant then posted about this issue in Connect. Therefore I am thankful to both of you. Let us look at the Issue.

If you can set a database to use encryption, we should be able to set it to NOT use encryption as well. Here is where the issue starts. To set the encryption off, we have to alter the Database.

ALTER DATABASE AdventureWorks SET ENCRYPTION OFF
GO

This will set the encryption OFF. We can double check if encryption is off by running a select statement.

SELECT *
FROM sys.dm_database_encryption_keys

You will see that the Encryption_state column is 1. That means that the database is now unencrypted. If you take a backup of this database and try to restore data, you will end up with an error.

RESTORE DATABASE [AdventureWorks] 
FROM DISK = N'D:\AdventureWorks_no.bak' 
WITH FILE = 1, 
MOVE N'AdventureWorks_data' TO N'D:\BackUp\AdventureWorks_Data.mdf', 
MOVE N'AdventureWorks_log' TO N'D:\BackUp\AdventureWorks_Log.ldf', 
NOUNLOAD, REPLACE, STATS = 10
GO

This will give the Stats of how much percentage is restored and in the end, when it is supposed to finish with the restore, it throws an error.

Msg 3283, Level 16, State 1, Line 1
The file "AdventureWorks_log" failed to initialize correctly. Examine the error logs for more details.

From what I have read so far from the issue, once TDE is enabled, even after you disable it there is always a reference to the certificate. This causes the restore of backup file to fail.

Conclusion

We have seen that the Transparent Data Encryption works, but has some small issues and drawbacks. It is easy to set up, but be very careful. Make sure you have a backup of the certificate in triplicate. If one media gets corrupted where the backup of certificate is stored, you have the other two media.

Now here is a point to ponder. If you had read through the article, I had specified that compressed backups are next to useless once we have TDE enabled. That means that a small/medium sized database (Anywhere above 50 GIG) will probably have a backup file of that size itself. If someone is able to steal the backup file of that size, you have a very big issue.

Please keep in mind that you have to test everything thoroughly in a test environment before you think of setting up TDE in production.