SQL Server 2016 - Always Encrypted

Dear Friends,

As SQL 2016 going to be launched very soon, I’m taking an opportunity to talk about interesting feature SQL Server 2016 – “Always Encrypted” & some of you would be looking for years (honestly speaking I was for sure).

Either its physical data security, logical data security OR fetching data by DBAs, developers and other users having access to SQL Server Databases, security is always an area of concern. Quite of us mostly have faced situations when compliance questions are directly or indirectly come to us, either in form of mandatory trainings, audits (SOX,HIPAA) or how data & SQL databases are being managed to address “SQL Server Security”.

In this blog, I’ll be talking about SQL 2016 most interesting topic i.e. Always Encrypted , also how this works, and when this should be used to protect data in SQL Server 2016 databases.

Being a DBA over a decade I’d been questioned at many places for compliance of databases and I’m super excited to implement this feature with SQL SERVER 2016 RTM Version in my most critical environment(s) to safeguard data & databases.

“Always Encrypted” is not a replacement of TDE (Transparent Data Encryption) J, it’s a compliment to TDE. In reality TDE safeguards the data(/database) “In-Rest” (before it’s written to disk) means addresses security breaches to physical data & log files. BUT – (a big one) money hungry folks can still query the data and misuse for their own purposes which is absolutely not in organizational favor.

In addition to TDE, Always Encrypted addresses security of data/database at “every point in life cycle of database”, starting from where the data is being originated (application) –> going through network –> pushed into SQL Server. In other words “Help protect data at rest and in motion, on-premises & cloud”. In conjunction to it ONLY & ONLY a trustworthy application can decrypt the data (basically at client side) and that all compliance needs.

How trust is going to setup and all, this blog is out of scope of. Probably I’ll put forward in my upcoming blogs. J

Two types of Encryption are available in Always Encrypted.

Randomized Encryption – As name says “Random”, yes it generates a random and un-deterministic value for SELECTED column every time it’s being queried. This is more secure due to the more random nature of the encryption. However because of this nature this allows for transparent retrieval of encrypted data with NO OPERATIONS. Sad! nevermind, everything comes in this world with a price tag. Here advance level security is price tag.
Ex:Encrypt(‘123-45-6789’) = 0x17cfd50a
Repeat: Encrypt(‘123-45-6789’) = 0x9b1fcf32

See both “returned” values are different from each other and if you keep on querying, such values will continue un-deterministic & random.

Deterministic Encryption – If you feel Randomized Encryption defeats the purpose of your data, this type of encryption is a great relief. This allows for transparent retrieval of encrypted data AND equality comparison E.g. in WHERE clauses and joins, distinct, group by. Every time data is being queried, always generates the same encrypted value for any given plain text value. Great! I got some relief.
Ex:

Encrypt(‘123-45-6789’) = 0x85a55d3f
Repeat: Encrypt(‘123-45-6789’) = 0x85a55d3f

See both “returned” values are same and if you keep on querying, such values will continue same & remain deterministic.

Isn’t that cool!!!

So, you’ve got a good understanding of what’s Always Encrypted and what kind of encryption are available with. Now question comes, how this advance feature internally works, at least from high level understanding prospective.

In this figure – There are two sides on same house. (Excuse me for using Finacle DB. I’d spent good amount of time with financial institutions but never worked with Finacle so thought to use in my examples atleast. J )

Application side
SQL Server side

Both sides are talking to each other but application can see the encrypted data unlikely from SQL Server side. So what’s helping application to access & see the real data, “ADO.NET 4.6 drive”. This driver is capable of decrypting the data as far as encryption keys are available at client side.

So let’s talk a bit about “Encryption Keys”.

Always Encrypted uses keys of two types: Column Encryption Keys (CEK) and Column Master Keys (CMK)

Column master keys are protecting keys used to encrypt column encryption keys. Column master keys must be stored in a trusted key store. Information about column master keys, including their location, is stored in the database in system catalog views.

Column encryption keys are used to encrypt sensitive data stored in database columns. All values in a column can be encrypted using a single column encryption key. Encrypted values of column encryption keys are stored in the database in system catalog views. You should store column encryption keys in a secure/trusted location for backup.

Each CEK can have 2 encrypted values from 2 CMKs to allow master key rotation.

Column Master Keys must be deployed on each client machine that needs access to the unencrypted data. In this whole process interesting thing is SQL Server does not contain the keys needed to decrypt data in plain text. So even a DBA can’t decrypt the encrypted columns though he/she is admin. Amazing feature, indeed.

(There are good amount of examples available on different blogs about syntax to create CMK & CEK. So I’ll be squeezing them off here. Please provide your feedback if you really want to include them here.)

Good thing about CEK or CMK they are sitting together in user database unlikely from TDE (SystemDBs). So every time your databases are going to get a new house (migration) nothing to worry about those keys as far as those are safeguarded properly @ Application Level and/or CMK Store i.e. Certificate Store, HSM or Azure Key Vault.