Transparent Data Encryption Helps Secure Corporate Data

In today's world, data is available anytime and anywhere, from your hard-wired network machines to your handheld cell phones. But the possibilities of data piracy are more of a concern than ever before. We hear almost every day that another corporate data breach occurred and credit card numbers, social security numbers, or some other piece of confidential data was lost, stolen, or made available to unauthorized personnel.

The costs of lost data can bankrupt a company. Because of this, developers and DBAs need to be more diligent in establishing procedures to secure their corporate data. Security has been a top priority for Microsoft over the past few years. With the introduction of SQL Server 2008, Microsoft provided a number of new security features, including Transparent Data Encryption (TDE). This article will dive in and discuss what TDE is and the minimal amount of effort it takes to encrypt your corporate data using it.

What is Transparent Data Encryption?
IT professionals are not only concerned about securing network traffic or access to data, but they are also concerned about encrypting data at rest. Database backups and detached database files (data and log files) are easy prey for data thieves. All it takes is for a backup file to be restored or disconnected database files to be reconnected and bingo! The thieves have taken your data and made it available for their use.

Data pirates were able to obtain database information this way because, prior to SQL Server 2008, Microsoft didn't provide an easy way to encrypt the entire database. For a long time you have been able to encrypt data and store it in the database, but you had to build that into your application logic. Building encryption/decryption algorithms takes many hours to develop. In SQL Server 2008, Microsoft introduced TDE as a way to encrypt an entire database in a matter of minutes without requiring changes to any application code. TDE is available in the Developer and Enterprise Editions of SQL Server 2008.

TDE is done by the database engine, which makes it totally transparent to the application, hence the name. TDE allows DBAs to meet the security requirement of encrypting data at rest.

How does all of this work? The general concept is when each page of data is written to a physical disk platter it is encrypted in the background via the database engine. The data is decrypted when it is read back from the disk. This allows the SQL Server engine to present data to the application in a decrypted format. TDE is implemented by the database. All of your databases on an instance don't need to be encrypted, but only those you enable to be encrypted. Once a database is encrypted, not only are the database MDF and LDF files encrypted, but the database backups created from the encrypted databases are also encrypted. In order to set up a database to be encrypted the DBA needs to perform a number of steps.

Before you consider implementing TDE you should also understand the drawbacks associated with it. TDE does not encrypt data in memory, or while it travels across the wire between the server and the client. Also, since the data is only encrypted when it is written to disk, anyone who has SELECT permissions on an encrypted database can view the data in an unencrypted format. If you plan to store FILESTREAM data it will not be encrypted. There is some overhead associated with encrypting the data, so it doesn't come for free.

Another thing to consider is when you encrypt any database on an instance the tempdb database will also be encrypted. Another drawback to TDE is that the new backup compression feature of SQL Server 2008 does very little compression on a TDE'd database. If you try to restore a database backup of a TDE'd database to another server you will need to make sure you have restored the appropriate certificate backup prior to performing a restore of an encrypted database. Lastly, if you have third- party utilities that directly access data pages at the platter level, TDE will break these utilities because they will no longer be able to read the pages in their encrypted form.

Setting Up Transparent Data Encryption
It is relatively easy to setup TDE. You implement TDE by database. Here are the four steps needed to set up TDE:

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

Let's go through each one of these steps in detail.

A master key is a symmetric key used to encrypt the private keys of certificates and asymmetric keys that are stored in a database. The master key is encrypted using the Triple DES algorithm and a user-supplied password. If you have not already set up a master key on your server for some other reason then a master key will need to be created prior to creating a certificate. Here is the code I used to set up the master key on my server:

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyEncrypt!0nP@ssw0rd';

The second step is to create a certificate that will be protected by the master key. This certificate is created in the master database. I set up my certificate for TDE using this following code:

USE master;
GO
CREATE CERTIFICATE MyTDECert WITH SUBJECT = 'My TDE Certificate';
GO

The next step is to create a database encryption key. A database encryption key can be secured by a certificate or an asymmetric key. Prior to creating a database key you need to determine the type of encryption you want to use. TDE encryption supports four different encryption algorithms: AES_128, AES_192, AES_256, and TRIPLE_DES_3KEY. Here is an example of how I create a database encryption key that is secured via the certificate I created in the prior step:

use MyTDEDB;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyTDECert;
GO

In this example I created my database encryption key using the AES_128 encryption algorithm.

The last step is to enable the database to support TDE. Here is the command I used to enable my database for TDE:

use MyTDEDB;
GO
ALTER DATABASE MyTDEDB SET ENCRYPTION ON
GO

This ALTER statement tells the SQL Server engine to encrypt the physical data and log files using the encryption method identified by the database encryption key.

As you can see there is not a lot to setting up TDE for a database. But you need to think beyond just setting up TDE. You need to plan for the different kinds of restores you might need to perform.

Restoring or Attaching Your Encrypted Backup onto Another Instance
In order to restore or attach an encrypted database on another instance of SQL Server some special steps will need to be taken. Remember the database files and backup files are encrypted; therefore in order for the SQL Server engine to decrypt transparently they will need the certificate that encrypted them. When I tried to restore my encrypted database from a database backup without having the certificate available I got the following error:

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

In order to eliminate this error when restoring my encrypted database I needed to first restore my certificate. To do a restore I need a backup of the certificate from the source server. When you are first setting up your environment to support TDE I would recommend you create a backup of your certificate. Make sure to store that backup in a safe place. This way, should you need to restore the encrypted database, you will have the certificate backup. You don't have to backup the certificate as soon as you create it. It can be taken anytime. Just don't wait until your server crashes and you can't take a backup. Here is the code I used to backup the TDECert certificate that I created above:

USE master;
GO
BACKUP CERTIFICATE MyTDECert TO FILE = 'c:\temp\MyTDECert_Cert'
WITH PRIVATE KEY ( FILE = 'c:\temp\MyTDECert_Key' ,
ENCRYPTION BY PASSWORD = '$$MyTDECert123##' );

You can see I used the "BACKUP CERTIFICATE" statement to backup my certificate. Note that I also specified a password for that backup file. I will need to remember this password in order to restore the backup. I would suggest you develop some kind of password management process for your environment so you don't forget the password of your different certificate backups. Without the password you will not be able to restore the certificate backup files and you will not be able to restore your encrypted database to another instance of SQL Server.

Prior to restoring or attaching an encrypted database you will first need to restore the certificate that was used to encrypt the database being restored or attached. Here is the code I used to restore my certificate from the backup I created above.

USE master;
GO
CREATE CERTIFICATE MyTDECert
FROM FILE = 'c:\temp\MyTDECert_Cert'
WITH PRIVATE KEY (FILE = 'c:\temp\MyTDECert_Key',
DECRYPTION BY PASSWORD = '$$MyTDECert123##');

If the instance where you are restoring the certificate backup does not have a master key you will need to create one first before you can restore the certificate from the backup files. Once you have created a certificate from the backups you can restore or reattach your encrypted database just like you would any other database.

Securing Your Data Using SQL Server 2008 at Rest
TDE is a simple way to encrypt your data at rest. It allows you to encrypt all the data in a database without changing your application. TDE is a big time saver compared to developing your own method to encrypt and decrypt data. Keep in mind that TDE is only available in the Developer and Enterprise Edition of SQL Server 2008. If you decide to implement TDE, remember to backup the certificate you used to create the database encryption key, and practice the restored process to make sure you can successfully restore your encrypted database.