Sep 01, 2016

Hello again everyone, both of you. Seriously, I have no idea what my audience is, this could end up being just a therapeutic exercise for my own peace of mind. In any case, I always feel much better.

Time has come, I think, to chat about some of the wonderful new features included in SQL Server 2016. What I would like to talk with you about in this blog is one of those new features. And that is, Always Encrypted.

Alrighty then, what is it, does it come in different colours. and is it contagious? Let me address the first of those questions, I'll leave the others for you to determine through experimentation.

The magic of Always Encrypted revolves around a special driver. One of the encryption keys is not actually stored in the database for security reasons. I kind of think of this like having one of those strong boxes for petty cash in your top drawer. There's not a lot of value to it if the keys are stored right alongside it. If, on the other hand, the person who is authorised to hand out petty cash retains the key separately from the petty cash box, this is somewhat more secure. Let's not get too carried away with the analogy, I'm sure it'll break down eventually and we'll all be left in a small puddle of confusion.

Now, unlike Transparent Data Encryption (or TDE as us cool kids call it), with Always Encrypted (should we call it “AE” just to save keystrokes? – maybe not) the data is encrypted both “at rest” and “in motion”. So, with Always Encrypted, the encrypted data is only decrypted once it reaches its' desination, the client application. It's important to remember that Always Encrypted is applied at the column level while TDE is applied at the database level.

There are two encryption types we need to understand with Always Encrypted. They are: Deterministic encryption, in which a given piece of plain text will always give the same cypher value. This has the advantage of allowing us to filter and group by ranges of encrypted values. The downside is, of course, that if one were clever enough, and determined enough, you could use pattern analysis to determine the cypher key. The other encryption type is Randomised encryption (I'm sure Microsoft will continue to spell it 'randomized' no matter how wrong they are). This is indecipherable based on the plain text value. A pattern analysis would yield nothing useful as the cypher is random. Excellent, but there must be a downside? Yes, there is. Using the randomised encryption type, you can't do the same filtering and grouping that you could with the deterministic encryption.

Always Encrypted relies on two types of encryption keys. They are: Column Master Keys (which must be stored in a trusted key store) which are used to create and protect the Column Encryption Keys. And obviously the other type is the Column Encryption Key which is used to encrypt the column data. These keys are stored in the database.

When an Always Encrypted column is referenced in a query the Always Encrypted driver retrieves the relevant Column Encryption Key from the database, retrieves the relevant Column Master Key from the trusted key store, uses the Column Master Key to decrypt the Column Encryption Key, and finally uses the Column Encryption Key to decrypt the column data. Phew!

Of course, none of this comes without a small price to pay. There are some limitations, including a restriction on the datatypes of the column to be encrypted, such as text, ntext and image (seriously, why are you STILL using these!?).

So, there, in a rather small nutshell, is Always Encrypted for you. Of course, if you came along to a course (see what I did there?) you'd find out vastly more about Always Encrypted. Now, I can hear you asking 'Which course, Adam?'. Easy, you'll find the details on Always Encrypted in our course 20764 – Administering a SQL Database Infrastructure. And you'll find the details for the course here on our brilliant website.

Thanks for stopping by, I hope you've found this brief blog about Always Encrypted enlightening. See both of you again soon.

For more information, take a look at our SQL Server 2016 training courses.

Cheers – Adam