Always Encrypted


I’m certain most of us developers have been faced with the challenge of building an application that houses sensitive data. Here are the thoughts that come rushing through my mind at that request:

  • How do we make sure the data is secure and protected all the time?
  • What is the most trusted encryption and decryption method to handle the data?
  • What the heck happens to my clients if their application gets hacked?
  • I’d be super sad if my boss fired me : (

My legitimate concerns correlate with the rise of hacking activities throughout the universe these days.  I can’t make any light assumptions when it comes to dealing with sensitive data. I’ve got to think carefully before I jump into any sort of implementation.

One Solution Out of Many

So… I’m sharing this as one of the possible solutions for dealing with sensitive data.  By no means is this the only solution. I’m putting my disclaimer here by saying that you will still need to evaluate the application and see if this solution fits for your client. I came across this recently when another developer at Switchbox (Steve) had us check out SQL Server 2016 new Always Encrypted mode. I was intrigued by the word “Always Encrypted” and decided to learn more about it.

Here are a few things that I found out while messing around with it:

  • The “Always Encrypted” feature encrypts data both at rest and in motion. This means that data is kept encrypted while in memory. This protects the data from bad people that pirate your information and hold it hostage for bit coins. In other words, this means that data is encrypted always, just like its name. Data only gets decrypted by the ADO.NET drivers on the application level.
  • Another nice thing about this is that it allows you to choose which column you want to encrypt vs. the entire database. In some scenarios that can be exactly what you need.
  • This will only work with .NET framework 4.6 as this is the framework that does the encryption and decryption magic.
  • The small price for extra storage for encrypted columns far outweighs a data breach.
  • Currently this only supports equality operators. You can use WHERE, JOIN and GROUP BY. But no LIKE, SUBSTRING or any other pattern matching features. So keep that in mind when you go this route.
  • Best of all, this works with SQL Server 2016 SP1 all editions. (unlike TDE which requires Enterprise Edition.)

So there you have it. I honestly think this is a good feature to know about and hopefully it will be handy for any of you one of these days. I know for sure it will be for us at Switchbox.