Transparent Data Encryption (TDE) in SQL Server

Introduction

Encryption brings data into a state that cannot be interpreted by anyone who does not have access to the decryption key, password, or certificates. Though encryption does not restrict the access to the data, it ensures that if data loss happens, then in that case data is useless to the person who does not have access to the decryption key/password/certificates. Though when you use encryption, there should be a maintenance strategy for passwords, keys, and certificates.

To meet the demands of regulatory compliance and corporate data security standards, SQL Server allows you to enable encryption at column/cell level or on the database level. You can even use file level encryption provided by Windows for database files.

Though there are several ways/levels to implement encryption in SQL Server, I am going to focus on Transparent Data Encryption (TDE) in this article, which was introduced in SQL Server 2008 and available in later releases.

Transparent Data Encryption (TDE)

Transparent Data Encryption (TDE) is a feature introduced in SQL Server 2008 and available in later versions for bulk encryption at the database file level (data file, log file and backup file) i.e. the entire database at rest. Once enabled for a database, this feature encrypts data into pages before it is written to the disk and decrypts when read from the disk. The best part of this feature is, as its name implies, it’s completely transparent to your application. This means literally no application code changes (only administrative change to enable it for a database) are required and hence no impact on the application code\functionalities when enabling TDE on a database being referenced by that application.

If there is a requirement to meet the demands of regulatory compliance and overall concern for data privacy, as a DBA you just need to enable it at the database level and rest of things are taken care by SQL Server to ensure data files, log files and even that specific database backup files are encrypted to prevent unauthorized access to the data in the TDE enabled database.

When you enable or disable TDE, the encryption and decryption operations are scheduled on background threads (called the encryption scan or scan – that scans all database files for encryption when enabling or decryption when disabling TDE) by SQL Server. You can view the status of these operations using the catalog views and dynamic management views as demonstrated next.

Please note, as I said before TDE works on data at rest for the entire database; in other words, it encrypts data when writing to disk and decrypts it when reading from disk at I/O level through the buffer pool. This means, data in the buffer pool remains there in clear text format. Hence, if you want to protect data in the buffer pool with encryption, you need to employ a different technique.

Getting Started with Transparent Data Encryption (TDE)

These are the steps you need to perform to enable TDE for a database, assuming you have the required permissions for creating a database master key and certificates in the master database and CONTROL permissions on the user database.

  • Create a master key – A master key is a symmetric key that is used to create certificates and asymmetric keys.
  • Create or obtain a certificate protected by the master key – Certificates can be used to create symmetric keys for data encryption or to encrypt the data directly.
  • Create a database encryption key and protect it by the certificate .
  • Set the database to use encryption – When you enable TDE on any user database, encryption is also automatically enabled for the tempdb database. This prevents temporary objects that are used by the user database from leaking to disk unencrypted via tempdb database. System databases other than tempdb cannot currently be encrypted by using TDE.
USE master;
--create a database master key (DMK) for the master database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyStrongPassword@4802';
--create a certificate for use as the database encryption key   (DEK) protector and is protected by the DMK.
CREATE CERTIFICATE Cert4TDE WITH   SUBJECT = 'Certificate for TDE';
 
Use   AdventureWorks2012
--Create the database encryption key (DEK) encrypted with the   certificate created
CREATE DATABASE ENCRYPTION   KEY
     WITH ALGORITHM   = AES_256 --Supported encryption algorithms are AES with 128-bit,   192‑bit, or 256‑bit keys or 3 Key Triple DES
     ENCRYPTION BY   SERVER CERTIFICATE   Cert4TDE
 
Use master
--Enable TDE for your database. As mentioned, this command   starts a background thread which runs asynchronously.
ALTER DATABASE AdventureWorks2012 SET ENCRYPTION ON
 
--To monitor encryption progress you can use this query
SELECT db_name(database_id), encryption_state,   percent_complete, key_algorithm, key_length
FROM sys.dm_database_encryption_keys

Table

Use master
--Disable TDE for your database. As mentioned, this command   starts a background thread which runs asynchronously.
ALTER DATABASE AdventureWorks2012 SET ENCRYPTION OFF

It’s very important and essential to take backup of the keys and certificates in order to restore or attach the encrypted database on another SQL Server instance after restoring these keys\certificates there.

When you use a certificate without taking a backup of it, SQL Server gives this warning:

“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.”

Please note, enabling encryption does not necessarily change the storage requirement but it requires CPU cycles to encrypt and decrypt the data. Additionally, you need to have maintenance strategy in place to store and protect keys. Hence, it’s recommended to first evaluate the need and then plan for implementation. For example, you have internet facing applications or have possibility of data files being lost, you might need to use TDE whereas if you have an internal applications, you might not need it.

Conclusion

In this article, I talked about Transparent Data Encryption (TDE), which transparently encrypts the entire database at rest (before data is written to the disk) and decrypts when it is read from the disk once enabled. I also talked about how to get started with this feature for user databases.

Resources

Securing SQL Server

Transparent Data Encryption (TDE)

See all articles by Arshad Ali

Arshad Ali
Arshad Ali
Arshad Ali works with Microsoft India R&D Pvt Ltd. He has 8+ years of experience, mostly on Microsoft Technologies. Most recently, as a SQL Developer and BI Developer he has been working on a Data Warehousing project. Arshad is an MCSD, MCITP: Business Intelligence, MCITP: Database Developer 2008 and MCITP: Database Administrator 2008 certified and has presented at several technical events including SQL-School. On an educational front, he has an MCA (Master in Computer Applications) and MBA-IT. Disclaimer : I work for Microsoft and help people and businesses make better use of technology to realize their full potential. The opinions mentioned herein are solely mine and do not reflect those of my current employer or previous employers.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles