SQL 2016 – Always encrypted

By | January 11, 2016

Here I try to summarize a lot of the information available on the MSDN website for the Always Encrypted feature being launched in SQL 2016. The primary concept to keep in mind here is that the application is the owner of the encryption keys and therefore the data comes to the database already encrypted.

The intention here being that when the data is stored in the database even the DBA has no ability to decrypt and view the data. Previously with features like cell level encryption and TDE the DBA was the owner of the server and therefore had control over the Master/ Symmetric / Asymmetric Key / Certificates etc. This meant that the DBA needed to be very trustworthy. Actually not. This feature is not meant to prevent the DBA from misusing his authority but more importantly just to improve customer confidence when moving to the cloud or outsourcing database activities. Personally I think this is a great idea since one of the most frequent concerns my clients have is about the security of their data. With this feature hopefully they would have more confidence in the security of the data that it would be easier to bring on board external vendors when needed. As you can see from the above diagram previously the keys were stored within the database itself and this meant the DBA (internal or external) has the ability to decrypt data. However if the data comes to the database encrypted right off the bat then the potential loopholes are plugged. This does pose some challenges to the DBA since performance and search behavior is now impacted which I will discuss later.

Primarily this feature is being implemented in two different way one is Deterministic encryption and the other is Randomized encryption. Most of us will recognize the first approach as Hashing and the second as encryption. Deterministic encryption always generates the same encrypted value for the same input while Randomized encryption will generate different encrypted values for the same input string.

Why is this important?

Well the database still needs to perform a lot of other activities like grouping and equality searches on top of the encrypted data and this becomes a challenge if you can’t know the value of the column without decrypting its contents first. With Deterministic encryption since the encrypted value will always be the same we can use it to hash and find matching columns. This will help in cases where you want to do Joins, equality searches and groups etc.

Data that is typically unique either by itself or when used in combination with another un-encrypted column might still prefer randomized encryption. An example of randomized encryption would be Username and password, passwords need to be randomized to protect commonly used combinations while at the same time they are almost never queried without the username which is still SARGable. An example of Deterministic encryption might be Designations in government organizations where multiple people share the same designation and you will need to group people by their job titles/depts. for audits etc.

So how much extra effort is this going to take?

Surprisingly little based on what we have seen so far. A lot of the functionality is handled by the Provider/Driver that connects to SQL server. The pre-reqs is the client machine should have .Net framework 4.6 installed. There is a wizard within SSMS that helps setup everything on the Server side. The changes will require the application developer to rewrite the connection string and there are some limitations when it comes to functionality but most of them are minor issues.

You can find a quick post on how to set it up here.

What is the down side?

There are quite a few compatibility issues with other features within SQL Server, for a detailed list click here. But from the look of things it seems performance is one of the big drawbacks since indexes are practically useless for these columns if you choose Randomized encryption. For a detailed test of performance issues with normal encryption vs Always Encrypted stayed tuned.

References

https://msdn.microsoft.com/en-us/library/mt163865.aspx#Anchor_3

Leave a Reply