When we consider how secure a form of encryption is there are two things we want to consider.

• What threat scenarios we are protected from.
• How easy is it to break down the encryption.

Let’s discuss each of these in turn.

What are We Protected From?

TDE encrypts data stored on the file system, so it should be pretty clear that we are trying to protect ourselves from an attacker who gets access to our files. You would be right in suggesting that shouldn’t be allowed to happen. Access controls should be in place to prevent inappropriate access. The reality though is that sometimes we get hacked and someone is able to work around our access controls. Sometimes backup files are stored offsite with a different organization where we do not control the access. That is why we have encryption – encryption is an extra line of defense. TDE offers no protection however against individuals who have direct access to query the database.

Let’s say someone does get access to our files – does TDE mean we are still sufficiently protected?

The answer unfortunately is that it depends. If someone has managed to get admin access to your database server then if they are sufficiently motivated, they will be able to read your data. TDE only protects you against lower levels of access.

The fact that an admin can get around the protection is an inevitability of many forms of encryption. TDE is managed by an administrator who has the sysadmin permissions on the database instance. They can enable TDE, they can disable TDE. They can change keys or export existing ones with their own password – but also they have direct access to the data anyway, so they can just run queries to view data.

Not all users who have admin rights over the box will have admin rights over the SQL instance, but anyone who has admin rights over the box can add themselves as an admin of the instance – though that usually requires restarting the SQL instance.

There are also other ways an admin can extract the keys from the file system – this is more complicated, but can be done if someone is knowledgeable enough.

So TDE only offers a very specific, but still very important protection.  If you need more then you will have to consider other forms of protection – such as Always Encrypted – possibly in conjunction with TDE.

How Easy is It to Break Down the Encryption?

TDE implements symmetric key encryption using standard encryption algorithms based on AES (Advanced Encryption Standard). When you set up TDE you can specify which AES algorithm you wish to use, AES_128, AES_192 or AES_256. In each case the number specifies the length of the key to be used for encryption in bits. Currently the only known way to crack such encryption is by brute force, i.e. try all the possible keys until you get lucky.

Obviously the longer your key, the harder the encryption should be to break, however even for AES_128, estimations of how long it would take to break down the key by brute force vary between a thousand years, to numbers many times greater than the age of the universe – trillions of years.

The difference in those estimates is based on how we anticipate processing power to grow in the future. In particular, whether the development of quantum computing might allow such activities to be carried out millions or billions of times faster than with conventional processors.

Even with the lowest estimates AES_128 should theoretically be sufficient in most scenarios but most people go for AES-256 which requires the same number of operations squared to crack. I recommend using AES-256 which should remain safe even if we see a quantum leap in processing power that exceeds all current expectations.

Up to 2016, SQL also supported the TRIPLE_DES_3KEY encryption protocol. This is now generally not considered to be as secure as AES, and from SQL 2016 its use is deprecated. So, it is best if you stick to AES even if you are on an older version of SQL Server.

In the next post in this series we’ll look at setting TDE up.

This post is part of a comprehensive series on SQL Server Encryption. Subscribe to my blog for updates as new posts are published or you can buy my book through the link in the sidebar to get it all in one go.

And if you’re embarking on an encryption project (or anything else to do with SQL Server) and want some help, I’m available for consulting – please get in touch or check out my services page to find out what I can do for you.