TDE is commonly described as “at-rest” encryption, i.e. it protects your data wherever it is stored on disk. This includes the database files, any backups taken (including Log and Differential), and any data that may get temporarily persisted to TempDB (when you use TDE to encrypt any database on an instance TempDB will get automatically encrypted also).

TDE does not however give any additional protection against those accessing data by querying the database. If you have access to the database then TDE transparently allows that to continue without any change in the functionality.
Understanding that, if we are implementing TDE we can only expect it to fully protect us from people who have access to the file system but do not have access to the data stored through any other avenue – for instance by querying SQL Server.

In most scenarios we probably don’t plan on there being such a person, i.e. we don’t grant the world read access to our database servers and say “go on, feel free, have a play”. In most production systems access is tightly tied down, and if you work in an environment where this is not the case then you probably have things you should address before you start looking at implementing encryption.

In reality though, mistakes or oversights happen, or sometimes malicious parties are able to gain access they should not have. What TDE offers us is, like many other forms of encryption, an additional layer or protection. The first line of defence gets breached, but there is still the next one to get past.

There is also the scenario of database backups, where these might be stored off the database server and even offsite. TDE gives us some assurance that in these scenarios the data is protected irrespective of the access restrictions in force in those other places. We’ve all read of breaches which were enabled by a database backup being stored on a location that was easier to access than it should have been.

Of course this protection of the backups could simply be achieved by using backup encryption – which is a separate feature of SQL Server and does not have the performance overhead associated with TDE.

So, if we are specifically implementing TDE rather than just backups it might be that we are specifically trying to protect ourselves from someone gaining direct access to the database files. That could be seen as quite an edge case – but that doesn’t mean it isn’t worth protecting against.

One thing to remember with TDE is that it is not (on its own) going to protect you against someone who has Admin right either over the box where SQL Server is installed, or over SQL itself. A local Admin can add themselves as an administrator on SQL – though they will need to restart the instance to do so. And an administrator on SQL can do pretty much whatever they like, including exporting a copy of the certificate and key used by TDE so that they can use that to read stolen data.

Still, there is some level of protection gained from TDE and if you combine it with tight access controls, and auditing, then it can help you reduce the scenarios where your data is at risk.

Hacking TDE

There is a great post and accompanying video out there by Simon McAuliffe that shows a method that can be used to break TDE:
https://simonmcauliffe.com/technology/tde/

Simon’s not a big fan of TDE and he makes a good argument, the reality is that a lot of the time people are using it to tick a box “yes, all our data in encrypted at rest” and no-one questions the particular scenarios where it is – and is not – offering protection.

While I agree with Simon’s points, I don’t feel as strongly as he does. You just need to be making an informed decision to implement TDE based on a full understanding of what it can and cannot protect you from. It’s not a magic bullet, but it’s not totally useless either.

Nonetheless the method Simon outlines for breaking TDE is concerning as it only requires basic level read access over the server to be able to grab all the objects you need to crack a TDE protected database. Sure, you need to have a certain knowledge base to be able to do that, but protection that is based on an attacker not having the right skillset, is not of the greatest value.

I won’t go into Simon’s method in full detail – you can check out his post for that. But I’ll give you enough of a summary so you can hopefully understand the principles involved.

What he details is an inevitable vulnerability that he specifies would not be unique to TDE – but common to any similar technology.

It’s back to the “T” of TDE again, “Transparent”. The concept of TDE is that it is fully self-contained and managed in the background for you by SQL Server. It doesn’t require you to supply additional passwords or keys when querying data, and it doesn’t need to access any objects stored outside of the SQL instance.
Let’s look again at the diagram with the encryption key hierarchy:

Starting at the bottom, each key is protected by the one above it. But what happens when we get to the top? There is nothing left to protect the uppermost DPAPI keys, so they must be stored unencrypted somewhere on the system.

You simply cannot get around that, SQL needs to be able to – all on its own – encrypt and decrypt data. Even if we say we’ll encrypt the DPAPI keys, what will we encrypt them with and where will we store that object? You can’t get away from the fact that at some point we need to store an unencrypted key, or password, or whatever, somewhere.

As a consequence, it’s remarkable easy to pick these root DPAPI keys up, though you need a few basic hacker skills/tools to be able to do anything with them.

Once you have the DPAPI keys, you can use those tools – in combination with a copy of the database files (or backup) from the Master database to obtain the SMK and thus break encryption all the way back down the hierarchy.

So what has just happened? A skilled and motivated hacker with mere read permissions to your server was able to steal your files and decrypt your data.

Just the scenario you implemented TDE to prevent.

They keys are held in the following directory on most Windows systems:
C:\Windows\System32\Microsoft\Protect\S-1-5-18

And by default, as long as you have read access to the file system you can read them.

Preventing this Hacking of TDE

Fortunately there is a basic action you can take to mitigate this. Strangely, this step isn’t mentioned (as far as I know) in any of the other documentation about setting up TDE.

The solution is that when you are setting up TDE you should take an additional step. Very simply, you need to secure this directory so that only Local Administrators to the server, the LOCAL SYSTEM account, and the SQL Server service account can read anything within it.

Once that’s done TDE should be offering you what you expect, i.e. casual read access to the server will not allow people to read your data. Only administrators would be able to gain access to the data, and as we’ve discussed already – Admins can access your TDE protected data anyway.

The only downside of doing this is that you are restricting access to the DPAPI (Data Protection API) from any other accounts. If you are running a pure SQL server then this should not be a problem – i.e. the server is for an installation of SQL and nothing else. If you also have application services running on the same box though, and these services wish to use encryption, then you may need to extend the permissions assigned to that directory. Though a better choice would be to move these services off the box.