Encryption of data at rest is one of the most important defenses we have in our architectural arsenal. When firewalls, authentication and authorization fail, correctly encrypted data gives the attacker nothing but a jumble of valueless bytes.

Leaked data is bad, but correctly encrypted data is substantially less worse.

I really want to stress ‘correctly encrypted’, by this I mean appropriate algorithms, keys, key derivation, key length, key storage etc.

This brings us to SQL Server Transparent Data Encryption (TDE).

Please note that TDE-type technology exists in other database engines, not just SQL Server.

What TDE Does

TDE was first introduced in SQL Server 2008 and allows an administrator to configure SQL Server so that it automatically encrypts and decrypts data at rest. The key word in the last sentence is ‘automatically.’ No code updates are needed whatsoever, no updates to client code, triggers, or stored procedures. It all ‘just works.’

But this is where knowing what TDE is designed to mitigate is critical. TDE mitigates a stolen drive or database file. If an attacker accesses a TDE-encrypted database (ie; tables etc) perhaps through a SQL injection attack, then the bad guy will get plaintext not ciphertext. This is the ‘transparent’ aspect of TDE.

Let me say that last part again, because it’s important:

TDE + SQLi == Plaintext

The Value of TDE

If the attacker can read the database file directly (eg; database.mdf), for example via a directory traversal vulnerability at the server, then they will only get ciphertext back, because SQL Server does not decrypt the data automatically.

Note this last point is also true for a database backup. It’s encrypted using TDE even when held in another location away from the SQL Server database itself.

I would argue that when using Azure SQL Database (the PaaS version, not SQL Server IaaS, that resides in a Windows or Linux VM), the value of TDE in a stolen hard drive scenario is considerably lower than on-prem. TDE provides protection against a lost or stolen disk, and Azure takes physical custody of the disks seriously; and while these scenarios are unlikely, mistakes happen, and TDE provides an extra defensive layer.

As a side note, you can read more about Azure data center physical security here.

If you use TDE, then you should use it in conjunction with keys you manage, that way if you know of an attack you can pull the keys or deny access to the keys. This way, the attacker has access only to cipher text once you pull the keys because SQL Server no longer has access to the keys. It’s not perfect, but it’s better than the attacker getting everything.

For many customers, TDE (using customer managed keys) offers protection against an internal Azure attack. Again, the likelihood of this scenario is slim, but it’s never zero.

Now What?

So what are our options?

One option is to use SQL Server “Always Encrypted“, but there are implications you must be aware of.

First, you will probably need to make code changes to an existing system. I can’t go into what you have to do because it varies by app, but don’t think you can take an existing application, change SQL Server to use Always Encrypted and expect everything to work. It probably won’t.

In my opinion, if you’re designing a new system, you should seriously consider using Always Encrypted on top of TDE. You can certainly update an existing system to use Always Encrypted, but as noted, it’s not trivial.

Always Encrypted allows you to perform only equality operations over encrypted columns that use a specific version of Always Encrypted called “Deterministic Encryption” so you will need to change the way some of your queries work. SQL Server 2019 adds support for performing more complex queries in a secure enclave, but that is available only in the non-PaaS version of SQL Server.

One final feature to look at is Dynamic Data Masking, it’s not a true security boundary, but it does help mitigate casual snooping.

Wrap Up

For an existing Azure SQL Database system that uses TDE, continue to use TDE, but I would suggest you use keys held in Key Vault if you want 100% control of the keys.

See if there’s an opportunity to move some of the more sensitive fields to take advantage of Always Encrypted. The fewer the number of columns using Always Encrypted, the smaller the chance of regressions.

Big thanks to Andreas Wolter, Shawn Hernan and Jakub Szymaszek from the SQL Server Security and Azure Security Assurance teams for their review and valuable comments.