We looked earlier at the various places our data lives – on disk, in memory, in-transit across the network. A good strategy will protect all of these locations – sometimes with multiple layers. Exactly how you go about doing that for your applications may vary – but as long as you are on SQL Server 2016 or higher there is a default strategy that you should consider. This combines a number of the available SQL Server features to provide the best protection.
- TDE. For at-rest protection of all your data.
- Always Encrypted. To encrypt all (or most) columns that contain personal identifiable, or sensitive, information.
- TLS. To make sure network communications between the application and server are encrypted.
- Hashing and Salting of passwords. To make sure passwords are secure – and we never need to store the actual password in the database.
On top of that you should definitely consider using EKM. In previously times, when we had to have dedicated hardware to support it, there was a fair barrier to entry. Now with easily usable and cost-effective cloud solutions it’s easy to get started with EKM, and certainly has many advantages.
Encryption is easiest to build in “by-design” when developing new applications, but more often than not we are implementing or enhancing encryption against our existing applications. It’s great if you are in that situation and have the scope to implement a full encryption strategy as outlined above. Such projects often have time and budget constraints though, or need to be delivered incrementally. If that’s where you are at, then you may want to focus first on where you can achieve the most with the least effort. You need to assess whether implementing any of the features above is going to have a performance impact that worries you, once you’ve dealt with that you might want to look at things in this order:
- Hashing and Salting of passwords. I put this item first because you should never be storing passwords in plaintext in a database. If you are encrypting passwords before storing them then that’s better than plaintext but still you should be looking at a hashing method.
- TLS. You really should have this on for all your connections between your applications and SQL Server that could contain data or other information you care about protecting. TLS is very easy to set up and can be done very quickly.
- TDE. TDE is again very easy to setup, and it comes for free with the standard edition of SQL Server 2016 SP1 onward. It used to require you to be on enterprise edition so that was a good reason why many people didn’t use it. TDE is only going to protect you against a limited number of scenarios, but you still get that for not much more than the flick of a switch.
- Always Encrypted. Column encryption with Always Encrypted is a little harder to understand and there are limitations on the ways you can work with encrypted data. It is however the best tool for protecting your personal and sensitive data. If your project has limited resource (what project doesn’t) focus on encrypting first the most sensitive items, and those where you won’t need to make code changes to work around the fact that data is encrypted. Code changes are likely to be required where you need to search against, or perform calculations on, data that you wish to encrypt.
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.