Whether we like it or not, DBAs are becoming security experts. It’s not a job we want, but it’s been thrust upon us as we are the protectors of the organization’s data. Whether required by law, or just for self-protection, more and more of the data in our databases need to be encrypted.

In SQL Server 2000 and earlier, if we wanted to encrypt data in our databases, this usually meant client-side encryption, where all the encryption and decryption occurred in the application, not in the database. This required custom-written applications.

In SQL Server 2005, column-level (sometimes called cell-level) encryption became available. Now, encryption could occur within the database, but it was not easy to use, offered poor performance, and it required a re-architecture of the application, along with changes to the database schema. Even with these downsides, column-level encryption offered some advantages: such as granular security; data is encrypted in memory and disk; and explicit key management, which allows different users to protect their own data using their own keys, even preventing the DBA from seeing a user’s data. Even so, the disadvantages were so great that only the most sensitive columns of a table were generally encrypted, which meant that much of the data in a database was still left unencrypted.

In SQL Server 2008 (Enterprise Edition only), a new form of database encryption has been introduced: Transparent Data Encryption (TDE), which includes these major features:

• Encrypts the Entire Database: With essentially a flip of a switch, the entire contents of MDF files, LDF files, snapshots, tempdb, and backups are encrypted. Encryption occurs in real-time as data is written from memory to disk, and decryption occurs when data is read from disk and moved into memory. Encryption is done at the database level, so you can choose to encrypt as few or as many databases as you want. The major benefit of encrypting a database with TDE is that if a database or backup is stolen, it can’t be attached or restored to another server without the original encryption certificate and master key. This prevents those nasty situations you hear about in the news where a backup of a database has been shipped from one location to another and is “lost,” which potentially exposes a company to liability issues.
  
• Easy to Implement and Administer: As its name implies, Transparent Data Encryption is transparent to applications. This means that your applications, and database schema, don’t have to be modified to take advantage of TDE. In addition, initial setup and key management is simple and requires little ongoing maintenance.
  
• Uses Minimal Server Resources to Encrypt Data: While additional CPU resources are required to implement TDE, overall, it offers much better performance that column-level encryption. The performance hit averages only about 3-5%, according to Microsoft.

While TDE offers many benefits over other types of encryption, it has some of its own limitations, which are important to consider. These include:

• TDE does not protect data in memory, so sensitive data can be seen by anyone who has DBO rights to a database, or SA rights to the SQL Server instance. In other words, TDE cannot prevent DBAs from viewing any data they want to see.
• TDE is not granular. Then entire database in encrypted.
• TDE does not protect communications between client applications and SQL Server, so other encryption methods must be used to protect data flowing over the network.
• FILESTREAM data is not encrypted.
• When any one database on a SQL Server instance has TDE turned on, then the tempdb database is automatically encrypted, which can contribute to poor performance for both encrypted and non-encrypted databases running on the same instance.
• Although fewer resources are required to implement TDE than column-level encryption, it still incurs some overhead, which may prevent it from being used on SQL Servers that are experiencing CPU bottlenecks.
• Databases encrypted with TDE can’t take advantage of SQL Server 2008’s new backup compression. If you want to take advantage of both backup compression and encryption, you will have to use a third-party application, such as SQL Backup, which allows you to perform both of these tasks without penalty.

For some organizations, they might want to consider implementing both column-level encryption (which still is available in SQL Server 2008) along with TDE for a database. While more complex to set up and administer, this combination offers greater security and encryption granularity than does either method used alone.

How Transparent Data Encryption Works

TDE is able to minimize resource utilization and hide its activities from user applications and the Relational Engine because all encryption/decryption occurs when data pages are moved between the buffer pool and disk.

Let’s say that TDE has been turned on for a database that includes a single MDF file, a single LDF file, and tempdb. As I mentioned earlier, whenever any database on a SQL Server instance is encrypted using TDE, then the tempdb database for that instance is also encrypted.

As SQL Server moves data pages from the buffer pool to the MDF file, the LDF file, or tempdb, the data is encrypted in real-time before it is written to disk. On the flip side, as data pages are moved from the MDF file or tempdb to the buffer pool, they are decrypted. In other words, when data is on disk, it is encrypted, but when data in memory, it is not encrypted.

When a backup is made of an encrypted database, it cannot be restored unless the DBA has access to the certificate and master key that was used to encrypt the database. This prevents anyone from stealing a backup and restoring it on a different SQL Server. The same goes for when you detach and reattach a database to a different SQL Server.

TDE supports several different encryption options, such as AES with 128-bit, 192-bit, or 256-bit keys or 3 Key Triple DES. You make your choice when implementing TDE.

How to Implement Transparent Data Encryption

Now, let’s take a brief look at how to turn on TDE for a database. This can only be done using Transact-SQL code, as SSMS (SQL Server Management Studio) has no option to perform this task. Before we drill down into the details, let’s look at the four key steps required to turn on TDE. They include:

• Create a Master Key: A master key is first created. This key, which is accessible with a password, is used to protect a certificate, which we will create in the next step. This key is stored in the master database in an encrypted format.
• Create or Obtain a Certificate Protected by the Master Key: This certificate is used to protect the database encryption key we will create in the next step. In addition, this certificate is protected by the master key we created in the previous step. The certificate is stored in the master database in an encrypted format.
• Create a Database Encryption Key: This is the key that will be used by SQL Server to actually encrypt the data. It is protected by the certificate created in the previous step. This key is stored in the database that is encrypted, and is stored in an encrypted format.
• Turn TDE On: Once all the above has been created, a command is run to tell SQL Server to begin encrypting all of the data using the database encryption key created in the previous step. This process may take some time, depending on the size of the database. Ideally, the database should not be used in production until the database has completed the initial encryption process.

Creating a Master Key

Assuming one has not already been created for another reason, the first step is to create a master key. The master key is a symmetric key used to protect the private keys of certificates and asymmetric keys. In this particular case, the master key is used to protect the certificate which will be created in the next step. When a master key is created, it is encrypted using the Triple DES encryption method and protected by a user-provided password.

To create a master key, run the following code:

--Create a master key
--The master key must be created within the master database
USE master ;
CREATE MASTER KEY ENCRYPTION BY PASSWORD =
'User-Provided Password' ;
GO

Obviously, the password you provide should be very obscure, and you will want to document in a secure location.

Create or Obtain a Certificate Protected by the Master Key

For this example, we are going to create a new certificate, although you can use a pre-existing certificate if available. The certificate is used to protect the database encryption key that we will create next. In addition, this certificate is protected by the master key created in the previous step.

--Create a certificate by the name of TDECert
USE master ;
CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate' ;
GO

Create a Database Encryption Key

Now that the certificate has been created, the next step is to create a database encryption key and protect it with the certificate we created in the last step. This is the encryption key that is used by the database to encrypt all of the data. It is during this step that you choose which encryption method is used to encrypt your database.

--Create Database Encryption Key Inside Database to Be Encrypted,
--and Protect It with the Certificate
USE AdventureWorks ;
GO
CREATE DATABASE ENCRYPTION KEY WITH
ALGORITHM = AES_256 ENCRYPTION BY
SERVER CERTIFICATE TDECert ;
GO

Backup the Private Encryption Key and Certificate

Once you have created the master key and certificate, they should be backed up immediately. If you lose these, you can’t move or restore the database.

--Backup the private key and certificate to two separate disk files
USE master ;
GO
BACKUP CERTIFICATE TDECert TO FILE =
'c:\certificate_backups\AdventureWorks_Certificate.cer'
WITH PRIVATE KEY ( FILE =
'c:\certificate_backups\NorthwindCert_Key.pvk',
ENCRYPTION BY PASSWORD =
'User-Provided Password' ) ;
GO

When this command is run, the master key and the certificate are taken from the master database and written to separate files (both in an encrypted format).

Turn TDE On

The last step is to turn TDE on. Once you run the following command, the database will begin to encrypt itself. Depending on the size of the database, and the hardware running it, this process could be lengthy. While it is possible to keep the database in production during this process, it will cause some user blocking and performance will suffer. Because of this, ideally you should only turn TDE on when the database is not being used.

--Turn TDE on
USE AdventureWorks
ALTER DATABASE AdventureWorks
SET ENCRYPTION ON ;
GO

If you want to watch the progress of the encryption, run this statement:

SELECT DB_NAME(database_id), encryption_state
FROM sys.dm_database_encryption_keys ;
GO

When the statement above is run, a state is returned. A database encryption state of “2” means that encryption has begun, and an encryption state of “3” indicates that encryption has completed. Once the tempdb database and the user database you are encrypting reach a state of “3,” you are ready to put them back into production.

From this point on, the entire user database, and tempdb database will be encrypted, although your applications will never know the difference.

Summary

If you got lost with all the keys and certificates required to implement TDE, you are not alone. It is a complex topic and beyond the scope of this chapter. The focus of this chapter was to provide you an overview of what TDE is, how it works, and how to implement it. Because of the complexity involved in using TDE, you should only implement this technology when you full understand its many complexities and after thorough testing in a test environment.

Share this post :