Setting up Transparent Data Encryption (TDE)

Transparent Data Encryption (TDE) has been a topic of conversation recently and I felt like it would make a good post.

What is TDE?

TDE is all about encrypting data at rest. If you are in a situation where you’re only worried about encrypting one column or a few columns in a database table, Always Encrypted would be an option to look into. TDE isn’t going to do any good against someone who can already access the database. Instead, TDE is used for more broad encryption and protection.

One example would be to protect stolen backups from being accessible. With TDE in use, an attacker will not be able to take a stolen backup and successfully restore it on their own environment without the necessary certificate from the source server. If they try, they will get this type of error when attempting to restore:

Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint ‘thumbprint_here’.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Also, if someone gets their hands on the data/log files for a database they will not be able to attach the files. If they try, they’ll get this similar message:

Up until SQL Server 2019, TDE had been an Enterprise Edition only feature. Fortunately, with SQL Server 2019 Microsoft started including TDE in Standard Edition. I’ve heard arguments on both sides about SQL Server 2019 and where it’s at as far as stability and adoption but I think everyone would agree that including TDE in Standard Edition is good news. Everyone can appreciate more options on securing their data.

TDE Setup

Let’s look at how to setup TDE. The first step in setting up TDE is to create a master key.

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeyPassword';
GO

Next, we will create a certificate to use in our encryption:

CREATE CERTIFICATE SQLServerCert WITH SUBJECT = 'DEK Cert';
GO

Now, we can go to our database and create a database encryption key protected by the certificate we just created:

USE ExampleDB;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE SQLServerCert;
GO

Finally, we run the following to turn on encryption:

ALTER DATABASE ExampleDB
SET ENCRYPTION ON;
GO

Enabling could take anywhere from minutes to hours depending on your database size so keep that in mind. The rest of your SQL Server shouldn’t notice a significant impact while encryption is taking place but I’d still recommend implementing off hours if possible.

Make sure you take a backup of your certificate and keep it safely secured. We’ll take a look at that process along with the process of restoring a database using TDE to a different server next week.

Thanks for reading!