I love theatre. In six months I am putting on two one-act plays for a local festival, because I don’t already have enough on my plate.
Security theatre, on the other hand, I don’t like. It is security for the sake of appearances, that offers little to no solution to the problem it claims to solve.
Arguments have been made that airport security qualifies as security theatre. This is not that argument.
I am here to argue that Transparent Data Encryption (TDE), an Enterprise-level feature in SQL Server (and available in all service tiers on Azure SQL Database), is not security theatre.
What is Transparent Data Encryption?
The short version is that our data, log and backup files are encrypted at rest (i.e., on the storage layer, see Perry’s comment below), so that an attacker cannot simply copy and attach the data and log files, or restore a backup, without having access to the master key. If backup tapes or drives are stolen, the data on those devices cannot be recovered.
We can also use what is known as a Hardware Security Module (HSM) to provide keys to secure the database. This is a dedicated physical or virtual device, separate from SQL Server, that generates keys for various services in an organization.
When securing a database with TDE, the documentation makes it clear that the keys should be backed up immediately, and then copied securely offsite.
One of the biggest arguments against TDE is that it doesn’t protect data in motion, which is a primary attack vector. It is thus considered nothing more than a checkbox item for the compliance officer’s security audit.
At the risk of sounding flippant, obviously it doesn’t protect data in motion. That’s not what it’s for.
Defence in depth
Taken in isolation, there are many ways to thwart practically every security measure used to protect data.
What a modern security strategy requires is multiple layers of defence, with sensible alerting systems in place at each layer, so that if an attacker manages to break through a particular layer, an organization is aware of the attack and can take action against it.
The important question when designing a good strategy to defend our data, is to ask who our enemy is.
If we just want to prevent the developers from having access to the production system, that’s fairly trivial to implement. Certainly a lot of breaches are the result of an inside job.
But if we need to protect our massive, highly-available online system from a nation-state, the security profile is going to look a whole lot different. TDE definitely forms a part of that larger picture, even if it’s just a small part.
Consider the rogue administrator, fired or made redundant, who decides to steal a copy of the database before heading out to the competition. With TDE as well as Always Encrypted and several other features included in the box with SQL Server, we’ve just made that person’s life a whole lot more difficult.
Transparent Data Encryption was designed to do one thing, and it does that one thing really well. Instead of calling it a compliance checkbox item, implement it as part of a broader defence strategy.
Photo by John Salvino on Unsplash.
You mention encryption at rest and the storage layer, reading, this implies that the storage is encrypted. It would be more accurate to state that the physical file level of the database is encrypted not the storage itself.
You also state that the database cannot be attached or restored without access to the database master key, this is incorrect. The DMK is not required, what you do require is a backup of the source certificate which is restored to the target instance prior to attaching or restoring the database.
TDE can be negated for the most part by simply hardening the windows server host
Thanks for your comment, Perry. You say “negated for the most part” (emphasis mine).
What exactly isn’t covered by hardening the underlying host, keeping in mind that SQL Server can also run on Linux (and through Docker containers using macOS, Linux and Windows)?
Well said and +1 for Bruce Schneier reference. Multi-layer security is necessary and TDE satisfies data at rest. Hopefully people are using the right encryption key algorithm i.e. AES 256 instead of 3DES nowadays.