The most striking change in feature set of Standard Edition SQL Server 2019 is by far Transparent Data Encryption(TDE) support. That means you don’t have to pay Enterprise License just to secure your data at rest. What does “at rest” that mean? I will explain in a bit.

Standard Edition License is much cheaper(around 4 times) compared to Enterprise Edition License. Naturally, it lacks numerous cool features like Always On Availability Groups (Standard only has Basic Availability Group), Online Page Restore, Online Index Create and Rebuild to name a few. With every new version of SQL Server, Microsoft adds a couple of features into Standard Edition set, this year it is TDE. GDPR and information security is a very hot topic nowadays, thus this looks like a nice and neat strategy from Microsoft. Enough talk, lets get to business.

What is TDE?

TDE is an encryption mechanism that secures data at rest which means it encrypts data on physical disk. Thus, data is really transparent to the client so they don’t have to install a new driver or change their code base. It is a physical security which means your data is still vulnerable in buffer cache, network traffic and so on. Nevertheless, In case of a burglary in your data center, thieves will be sad when they realize your data is encrypted on disk. Encryption of the database file is performed at the page level. Thus, pages are encrypted first then written to the disk, they are decrypted when read into memory.

TDE secures data in:

• Database Data Files
• Database Transactional Log Files
• Database Snapshots
• Database Backups
• TempDB

TDE doesn’t deal with:

• SQL Dump Files
• FILESTREAM data
• Buffer Pool Extension(BPE)

TempDB may be a surprise for you. Yes, TempDB will be encrypted if any database is encrypted with TDE. This may cause performance degradation for unencrypted databases on the same server according to Microsoft.

How it works?

It is all about hierarchy.

1. Windows Data Protection API(DPAPI) protects Service Master Key(SMK).
2. SMK protects Database Master Key(DMK)
3. DMK protects all objects in that database such as Certificates, Keys etc
4. Certificate in master database protects Database Encryption Key(DEK)
5. DEK encrypts all pages in the candidate database for TDE.

How to setup TDE?

I’d like to list steps first and explain them in detail one by one.

1. Create Master Database Key
2. Create Certificate
3. Backup Certificate
4. Create Database Encryption Key
5. Enable Encryption

That’s it. Pretty straightforward and easy steps to implement TDE.

Lets have a closer look

Understanding how each step works and why it is needed is crucial to perform administrative tasks on databases, especially with encryption technologies. SQL Server Encryption terms are not very clear and A BIT confusing for a lot of people so I will explain each of them in detail.

1. Create Database Master Key(DMK)

First of all, this has nothing to do with master database. DMK is a symmetric key which protects other keys, like King of Kings. You have to have a DMK to be able to encrypt all the encryptable objects within the database such as

• Symmetric Keys
• Asymmetric Keys
• Certificates

DMK can be created for any database, but for TDE, you need a DMK in master database. DMK is protected both with a password and SMK. A copy of DMK is encrypted by Service Master Key(SMK) so that whenever database is used, the service account can decrypt the SMK and use that key to decrypt the DMK without us entering the password.

PS: For a super secure environment, you can break this link between SMK and DMK so that the only way to open and use encrypted database is a DBA enters the password. That is a very rare case and so I’m not going to discuss it here.

Service Master Key:

SMK is the root of SQL Server Encryption Hierarchy and it is created during SQL Server Setup and protected by Windows Data Protection API(DPAPI).

Command:

USE master; 

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘VeryStrongPassword’;

GO

2. Create Certificate

Now that we have a DMK in master database, we are able to create keys and certificates then encrypt them using DMK.

A certificate is a digitally signed security object that contains a public (and optionally a private) key for SQL Server. We are going to use a Certificate to protect Database Encryption Key(DEK) in next step.

Why do we need a certificate? Why don’t we use DMK to secure DMK?

Because when we want to migrate our database to a new server, we have to migrate our DMK too since it is required for decryption. If new server already uses TDE, then there will be a problem because new server has its own DMK already. There are additional reasons but this is the main one.

Command:

USE master; 

CREATE CERTIFICATE TDECert WITH SUBJECT= ‘TDE Certification‘; 

GO

3. Backup Certificate

This is super important because if you lose your certificate, you lose your data, period.

That’s why, best practice is taking backup immediately after you create it.

BACKUP CERTIFICATE TDECert
TO FILE = ‘D:\BK\MyTDECert.cer’
WITH PRIVATE KEY
(
FILE = ‘D:\BK\CertPrivateKey.key’,
ENCRYPTION BY PASSWORD = ‘VeryStrongPassword’
);
GO

We are safe now. If we need to restore or migrate our database into a new server, we need below things to access data:

• Backup of the database
• Certificate file
• Private Key file
• Encryption Password

4. Create Database Encryption Key

It is time to create our main key, Database Encryption Key. The certificate in master database will protect DEK and DEK will encrypt data on every page.

You can only have one DEK in each database. It is stored in the database so that means it will move with the database. That’s why it is not included to the list of required items above.

T-SQL Command is pretty easy to grasp. I will recommend using AES_256 algorithm.

Command:

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECert;

5. Enable Encryption

Last step is turning encryption on for the database. This will start a thread to encrypt entire database using Database Encryption Key. According to my experiences, percentage column in sql server dmv is not working very precise, especially with large databases, but it gives you an idea.

TDE Scan

I am glad to announce this new feature we have in SQL 2019: TDE Scan. We can suspend and resume TDE process now using TDE Scan as described here. During encryption, you can track the process using dynamic management view sys.dm_database_encryption_keys.

Commands:

SELECT db_name(database_id) as db_name, percent_complete, * FROM sys.dm_database_encryption_keys
GO

ALTER DATABASE TDETest SET ENCRYPTION ON;

ALTER DATABASE TDETest SET ENCRYPTION SUSPEND;

ALTER DATABASE TDETest SET ENCRYPTION RESUME;

Database is encrypted now on the physical layer.

Summary

Let’s go over all one more time.

We have data stored in pages on physical layer. This pages are encrypted using Database Encryption Key.  DEK is a symmetric key which means you can encrypt and decrypt data using same key. SQL Server uses it while accessing data.

DEK is secured by the certificate. Certificate is encrypted using Database Master Key. Both of them are in master database. DMK is encrypted using Service Master Key. Finally SMK is secured using Windows DPAPI.

Don’t forget that database backups will be encrypted also. You will need DMK password and certificate to be able to restore database.