Introduction to Transparent Data Encryption

With SQL Server there are a variety of ways we have available to secure data within the databases, however if an attacker can gain access to the underlying data files we may be vulnerable to having that data interrogated. Similarly when we backup our databases these flat files could also be intercepted and restored elsewhere to allow browsing. Our data may be vulnerable at rest.

One option to help protect data in these instances is through Transparent Database Encryption (TDE) which is available in Enterprise edition in earlier versions (2017 or prior) and in Standard edition in more recent versions (2019 onwards).

This feature performs real-time encryption of the data and log files to help keep our data secure at rest regardless of if its inside or even outside our environment.

How does it work?

The encryption of data is done using an encryption key at the database level. This is produced based off a certificate we create within the SQL instance and can be used across multiple databases. They’re not the only components though as we also have an instance level Database Master Key which creates our certificate and the SQL Server Service Master Key which is automatically created during setup.

Below you can see a diagram of the TDE architecture from the official documentation which illustrates the hierarchy of keys which are used through the process:

If we work through this in the opposite direction – top-down – then we can see how it’s practically implemented:

• During the instance setup the Service Master Key is automatically created
• Before we can perform any encryption we have to create a Database Master Key (just the one per instance)
• We’re then able to create a Certificate which can be used to encrypt databases (as many as required per instance)
• For each database we want to encrypt we can then create a Database Encryption Key based on whichever certificate is appropriate
• We then enable the encryption at the database level and let the engine work through the data encrypting the data and log files

In a follow up post we’ll work through all of these steps practically with the relevant code samples as reference. The goal of this post is to lay out the basics so we understand what we’re doing before we dive in and find things the hard way.

Overhead of TDE

Transparent Database Encryption gets its name as its transparent from anyone using the data, unfortunately not from the overhead. The encryption of the data isn’t free – there’s an extra step in reading or writing data from the disk.

If ultra-high-performance is required this may not be an optimal solution, however if you’re considering TDE then its likely that security concerns may top these minor performance drawbacks.

Practically in my experience I haven’t hit situations where the encryption is holding me back with performance. There are likely better ways to optimise indexes or queries to see benefits in most circumstances, although of course your mileage may vary.

I thought it was also worth noting that if any database in the instance is encrypted then SQL Server will also encrypt TempDB so that could impact performance of all databases on the instance. I wasn’t even aware of this until I was having a browse over the documentation so quite an interesting find and likely more key than point above re performance for the encrypted database itself.

Back it up

The last and most critical point which I wanted to stress with TDE for me is this: you need to backup your keys and certificates.

Once you’ve got your databases encrypted if you lose these then your data will be worthless. If you can’t restore your certificates into another environment then you won’t be able to restore your database backups.

There will be other situations where you’ll need keys too, such as if you’re trying to restore a production database into a test instance, or if you’re looking to add your encrypted database into an availability group. You want to make sure they’re stored somewhere where you’re able to get to them when you need them.

Here’s the thing – you need to have them backed up. It doesn’t need to be anything fancy but make sure its secure and resilient so you can rely on them being there when you need them.

Wrap Up

In this post we’ve briefly covered the basics of Transparent Database Encryption and how its conceptually implemented. We’ve also touched on minor performance considerations and finished off by stressing the importance of backup up those certificates and keys.

Next time we’ll look at practically implementing those steps to get us moving with TDE and seeing it in action.