The “SQL Guy” Post # 22: Understanding Transparent Data Encryption

You may have heard about Transparent Data Encryption (TDE), which was introduced in SQL Server 2008. But what does it do, what are its advantages and disadvantages and how can you leverage this technology in SQL Server? Keep reading this tips and tricks article to learn how.

Transparent Data Encryption (also called as TDE) is a technology in SQL Server that offers encryption of data-at-rest. This feature automatically encrypts the entire database (data and log files), as well as database backups, without requiring any programming or code changes to your application. The process is entirely transparent, hence the name Transparent Data Encryption.

 

When TDE is first enabled for a specific database, SQL Server encrypts the database in the background. During this process, the database remains online and responsive to client requests (similarly, when encryption is disabled, SQL Server decrypts the database in the background). Encryption is performed at the page level, and does not increase the size of the database in any way. Once the entire database is encrypted, new data gets encrypted on the fly as it is written to disk, and all data gets decrypted when read back. Figure 1 illustrates the typical key hierarchy used for transparent data encryption.

 

If you are worried about protecting your sensitive data at rest, the solution is to use Transparent Data Encryption in SQL Server.

 

Figure 1: Transparent Data Encryption

 

 

Benefits of using TDE:

(1) Ease of implementation and transparency: TDE is essentially a “flip-the-switch” solution that allows you to encrypt your entire database and log files without application modifications.

(2) Additional security compared to cell-level encryption: TDE automatically encrypts tempdb and the database log files to prevent data leakage.

(3) Any data-type supported: Unlike cell-level encryption that returns only varbinary data, TDE allows you to store data using any native data type.

(4) Support for indexes: Because data is decrypted in the buffer pool, TDE allows the SQL Server query processor to use existing indexes on the data.

 

Drawbacks of TDE:

(1) Lowest support encryption granularity is the database.

(2) The data is not protected from authenticated, authorized database users, including the DBA.

 

HOW TO ENABLE TDE IN 5 EASY STEPS

--Step 1: Create a Database Master Key in for the master database if --it does not already exist.   USE Master; GO   IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')   CREATE MASTER KEY ENCRYPTION BY PASSWORD = '1GoodPassw0rd';   --Step 2: Create a server certificate for the TDE. This certificate is --used to protect the Database Encryption Key (DEK).   IF NOT EXISTS (SELECT 1 FROM sys.certificates WHERE name = 'SampleDatabase TDE Certificate')   CREATE CERTIFICATE SampleDatabaseTDEcertificate WITH SUBJECT = 'SampleDatabase TDE Certificate';   --Step 3: Create a Database Encryption Key (DEK) in the user database --(SampleDatabase) for use by TDE.   USE SampleDatabase; GO   IF NOT EXISTS (SELECT 1 FROM sys.dm_database_encryption_keys WHERE database_id = ( SELECT dbid FROM master..sysprocesses WHERE spid=@@SPID ))   CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE SampleDatabaseTDEcertificate   --Step 4: Turn on TDE encryption ALTER DATABASE SampleDatabase SET ENCRYPTION ON   --Step 5: Query DMV to confirm TDE is in effect SELECT DB_NAME(database_id) as DB,encryption_state FROM sys.dm_database_encryption_keys WHERE database_id=DB_ID(); GO

 

HOW IS DATABASE ENCRYPTION KEY MANAGED?

Since the symmetric database encryption key (DEK) is used the encrypt the database in TDE, it cannot be left un-encrypted. To learn more on how SQL Server encrypts the DEK refer to this blog post.

 

CERTIFICATE EXPIRY – SHOULD I WORRY ABOUT IT?

TDE encryption does NOT enforce expiration dates for certificates. You can continue to decrypt data with a certificate in TDE even if it is expired. This is different than service brokering and mirroring features where certificate expiry needs to be acted upon by the DBA.   However, don’t forget to backup your TDE certificates with the private key. The certificate backup is required while restoring / attaching the encrypted database.

 

TDE AND BACKUP COMPRESSION – CAN’T HAVE YOUR CAKE AND EAT IT TOO!

Encryption introduces randomization in the data to maintain confidentiality. The high entropy of encrypted data makes it very difficult to compress, possibly even growing in size, because there is no statistical tendency to eliminate. It is because of this reason that backup compression is not recommended on a database with TDE enabled.   Figure 2 : Backup compression with TDE (BUFFERCOUNT = default, MAXTRANSFERSIZE = default, 1 backup device)   Observations : (1) On a TDE enabled database, backup compression doesn’t reduce the size of the database. (2) CPU utilization for the compressed backup is higher than the uncompressed backup, even though the backup size is not much different because the CPU resources are wasted in the compressed backup operation since attempts are made to compress the data even though it is not very compressible. (3) On a TDE enabled database, it takes longer to perform a compressed backup that it takes to perform an uncompressed backup. Click here for more information on backup compression.