Transparent Data Encryption (TDE) was introduced in SQL 2008 as a way of protecting “at rest” data. It continues to be available in all versions of SQL right up until the present, until recently it was only available in the Enterprise editions of SQL Server but from SQL 2019 it was made available in standard edition.
When we talk about “at rest” data we are referring to data that has been written to disk. In terms of our SQL databases that includes:
- Any data files for our database
- Any log files for our database
- All backup files for the database, be they Full, Log or Differential backups
- Database snapshot files
- Any data written to disk in the TempDB database
The last item in that list, TempDB, needs to be included for completeness. Imagine that you query your database and as part of the query execution TempDB is used. If that data was 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.
Data “at rest” of course 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 e.g. TLS and Always Encrypted.
There are also some less obvious exceptions which occur where SQL doesn’t use the buffer pool – and therefore there isn’t an in-memory version of the data:
- Filestream data
- Data persisted to disk using Buffer Pool Extensions
And there are a couple of other exceptions that can occur in certain circumstances:
- Where the buffer pool gets paged to disk due to memory pressure
- SQL dump files when there is a crash
That’s summarised in the below diagram:
TDE mainly uses standard encryption protocols based on AES (Advanced Encryption Standard). When you set up TDE you can specify which AES algorithm you wish to use, AES_128, AES_192 or AES_256. In each case the number specifies the length of the key to be used for encryption in bits.
Obviously the longer your key, the harder the encryption should be to crack, however even for AES_128, estimations of how long it would take to break down the key by brute force vary between a thousand years, to numbers many times greater than the age of the universe – trillions of years. The difference is based on how we anticipate processing power to grow in the future. Even with the lowest estimates AES_128 should be sufficient in most scenarios but most people seem to go for AES-256 which should take the same time squared to be beaten.
Up to 2016, SQL also supported the TRIPLE_DES_3KEY encryption protocol. This is now generally not considered to be as secure as AES, and from SQL 2016 its use is deprecated. So, it is best if you stick to AES even if you are on a SQL version where DES is an option.
Let’s have a look at 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:
CREATE DATABASE TestTDE;
USE TestTDE;
CREATE TABLE dbo.SomeData(Id INT IDENTITY(1,1), SomeText VARCHAR(255));
INSERT INTO dbo.SomeData (SomeText) VALUES('This is my data');
I’ll close my connection from the database, and detach it so I can open the files in a Hex Editor. Then I search for my text in the data file:
As you can see the data is stored clear as day in the data file.
Now let’s look at the same data file once TDE has been enabled. This time if I search for my string it’s not found and my data looks like this:
Even where the previous file was all zeros where there was free space at the end, the encrypted version also has those encrypted:
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.