TDE is referred to as a “transparent” form of encryption. What that means is that the process of encrypting and decrypting data is fully performed in the background. The queries we write to access data are unchanged whether TDE is enabled or not. So, enabling TDE has no impact on application functionality, does not require refactoring of code, and is therefore relatively easy to implement. TDE encrypts all the data in a database, so you don’t need to choose which data items to encrypt.

TDE allows us to encrypt “at rest” data. When we talk about “at rest” data we are referring to data that has been written to disk. TDE works at the IO level, encrypting data automatically as it is written to disk, and decrypting it as it is read from disk.

In terms of our SQL databases the assets that are protected include:

• Any data files for our database.
• Any log files for our database.
• All backup files for the database, Full, Log or Differential backups.
• Database snapshot files.
• Also the TempDB database data and log files are encrypted.

The last item in that list, TempDB, needs to be encrypted for completeness of protection. Imagine that you query your database and as part of the query execution TempDB is used. If that data were written to disk, then that creates a hole in our protection, someone could potentially read or copy the TempDB files and might see some of the data we are trying to protect. As a result, when you enable TDE against any database on your SQL Server instance, the TempDB database is automatically encrypted as well to prevent this from happening.

It’s reasonably obvious to state that data “at rest” doesn’t include the following things:

• Data loaded/stored in memory (buffer pool).
• Data returned from a query and being passed across the network.
• Data received by a client as a result of a query.

If you want to cover those scenarios as well then you need to look at other forms of encryption such as TLS and Always Encrypted.

There are also some less obvious exceptions:

• Filestream data.
• Data persisted to disk using Buffer Pool Extensions.

And there are a couple of other exceptions that can occur in particular circumstances:

• Where the buffer pool gets paged to disk due to memory pressure.
• SQL dump files when there is a crash.

What does and doesn’t get encrypted by TDE is summarized in the below diagram:

Let’s have a look at the contents of some SQL data files so you can see the difference with and without TDE. I’ve created a database with a single table and inserted a row of data with the following code:

CREATE DATABASE TestTDE;
GO    
USE TestTDE;
GO
CREATE TABLE dbo.SomeData
(Id INT IDENTITY(1,1), SomeText VARCHAR(255));
GO
INSERT INTO dbo.SomeData (SomeText) VALUES('This is my data');
GO

I’ll close my connection from the database, and detach it so I can open the files in a Hex Editor. You can detach the database with the following SQL:

USE master;
GO
EXEC master.dbo.sp_detach_db @dbname = N'TestTDE';

Then I open the file in my Hex Editor and search for the text “This is my data” in the data file:

As you can see the data is stored as clear as day in the data file.

Now let’s look at the same data file once TDE has been enabled (we will look at enabling TDE in later posts). This time if I search for the same text it’s not found, and my data looks like that shown below.

It’s interesting to also look at the end of the database file where there is free space. In the unencrypted version that free space would have simply been represented by zeros. In the encrypted version that free space too has been encrypted, so an attacker cannot even see where your data ends (Figure 2-4).

TDE works by using an encryption key that is stored in the database being encrypted – but that key is itself stored encrypted by an object outside of the database. We’ll look at the various objects involved in the next post.

This post is part of a comprehensive series on SQL Server Encryption. Subscribe to my blog for updates as new posts are published or you can buy my book through the link in the sidebar to get it all in one go.

And if you’re embarking on an encryption project (or anything else to do with SQL Server) and want some help, I’m available for consulting – please get in touch or check out my services page to find out what I can do for you.