Encrypting Your SQL Data Should be Your Default Option

Cyber-attacks and data breaches seem to be perpetually on the rise. Every week we hear news of large companies that have had data stolen with both financial and reputational repercussions. It should be clear that access controls aren’t sufficient to fully protect us from attacks and we need a second line of defence – which is encryption.

Brent Ozar ran a twitter poll a few months ago asking if people are encrypting their databases and backups. We see the results here:

You can see that a small majority of DBAs who follow Brent on Twitter and respond to polls use some form of at-rest encryption. I guess that means that in the larger population the majority aren’t encrypting their data at all.

There are a number of reasons why that might be the case, the only good one is that your database doesn’t hold any personally identifiable information (PII) or sensitive data which will certainly be true in some cases but not in most.

Other reasons are likely to be around effort and expense involved. With the advances Microsoft has made in the encryption feature set over the last several versions of SQL Server I’d argue that those reasons are not as valid as they might have once been.

There are four areas you should be thinking about in terms of properly protecting your data.

Passwords are the most sensitive piece of information we hold in our systems. If these are accessed then an attacker can access all a user’s information in our system, and likely in other services they use if they re-use passwords. We shouldn’t even store passwords at all, but should use hashing and salting instead. Most people are probably pretty good about following good practices around passwords, but you’re not then stop reading now and go start having a conversation with your team about how you can address that.

At-rest Encryption
Protecting our data where it is stored on disk is an important part of an overall encryption strategy. Some people will use disk encryption for this but it is better if you can use TDE (Transparent Data Encryption) which will protect you against those who get read rights against the file systems on your servers. I get that TDE can be expensive as it required Enterprise Edition to use it, but that changed with SQL 2019. If you are on an older version then that alone can be a compelling reason to upgrade. Where that is not possible, if you are on SQL 2014 or higher then you can at least use the backup encryption feature to give protection to your backup files.

In-transit Encryption
In my experience most people do use TLS to protect their data is it is transmitted across the network, so that is good news. Again, if you’re not doing this then start making plans to change that. TLS is easy to implement and has little downside in return for the protection it offers.

Column Encryption
At-rest and in-transit encryption are great, but they don’t protect you if someone gains direct read access against your database. That’s where column encryption comes in. Column encryption used to require significant effort to implement, but with the introduction of Always Encrypted in SQL Server 2016 it became a lot easier. In many cases data can be encrypted with no code change required in your applications and giving you the highest level of protection possible. With Always Encrypted, data is protected at-rest, in memory and in-transit. It only ever exists in unencrypted form when it reaches your application.

We should use column encryption against our PII and sensitive data. I know Always Encrypted does give you limitations around how you work with your data, but in most cases you’ll have some data where those limitations are not an issue. I’d recommend you start encrypting those items now, and then over time work out to to encrypt the rest bit by bit.

I’m going to go into deep detail about all these forms of encryption on this blog over the coming months. If you want to know more about TDE and Always Encrypted I’ve already got a lot of posts – just use the search bar on the right.

Or, if you want the comprehensive guide to SQL Server Encryption in one go you can buy my book available through the link below:


Thanks for reading!

If this post has helped you, consider buying me a coffee to say thanks.

One thought on “Encrypting Your SQL Data Should be Your Default Option

Leave a Reply