SQL Server 2016: Always Encrypted

| by Jonathan Allen Follow 639 Followers on Jun 16, 2015. Estimated reading time: 3 minutes |

Database security, and the lack thereof, has been constantly in the news. It seems like at least once a month we hear about a major breach in a large corporation or government agency. Some of this can be mitigated by applying encryption and hashing techniques, but doing so is tedious so developers often only do it for the most sensitive of data such as passwords.

SQL Server 2016 seeks to make encryption easier via its new Always Encrypted feature. This feature offers a way to ensure that the database never sees unencrypted values of sensitive columns without the need to rewrite the application. In order to maintain reasonable performance, non-sensitve columns such as primary keys are left unencrypted.

The actual process of encrypting and decrypting data is handled in the database driver level. While the database only sees encrypted values, the application code works exclusively with unencrypted data. When a query is executed, the driver automatically looks up the master key in the Windows Certificate Store (or other OS-dependent location). The master key is then used to decrypt a column specific key, which in turn is used for encrypting and decrypting fields and parameters.


Microsoft offers three use-cases for Always Encrypted.

Client and Data On-Premises

A customer has a client application and SQL Server both running on-premises, at their business location. The customer wants to hire an external vendor to administer SQL Server. In order to protect sensitive data stored in SQL Server, the customer uses Always Encrypted to ensure the separation of duties between database administrators and application administrators. The customer stores plaintext values of Always Encrypted keys in a trusted key store which the client application can access. SQL Server administrators have no access to the keys and, therefore, are unable to decrypt sensitive data stored in SQL Server.

Client On-Premises with Data in Azure

A customer has an on-premises client application at their business location. The application operates on sensitive data stored in a database hosted in Azure (for example in SQL Server running in a virtual machine on Microsoft Azure). The customer uses Always Encrypted and stores Always Encrypted keys in a trusted key store hosted on-premises, to ensure Microsoft cloud administrators have no access to sensitive data.

Client and Data in Azure

A customer has a client application, hosted in Microsoft Azure (e.g. in a worker role or a web role), which operates on sensitive data stored also stored in Microsoft Azure. The customer uses Always Encrypted to reduce security attack surface area (the data is always encrypted in the database and on the machine hosting the database).

Types of Encryption

SQL Server offers two encryption modes: deterministic and random. Deterministic encryption ensures that a given value always has the same encrypted representation. This allows you to use the column for equality comparisons, joins, and grouping.

The downside of deterministic encryption is that is can “allow unauthorized users to guess information about encrypted values by examining patterns in the encrypted column”. This is especially true when there are a small number of possible values.

For more security, you can use random encryption. Like salting a password, this prevents guessing by ensuring that a given value’s encrypted representation is never the same twice. Microsoft continues,

Use deterministic encryption for columns that will be used as search or grouping parameters, for example a government ID number. Use randomized encryption, for data such as confidential investigation comments, which are not grouped with other records, or used to join tables.

General Limitations

If a column is encrypted, then all range-like operations such as greater/less than, pattern matching using LIKE, etc. are disallowed. Furthermore, you can’t pass encrypted values to functions, user-defined or otherwise, because the database doesn’t have access to the unencrypted values.

Equality comparisons can only be performed on columns that use deterministic encryption.

Indexes can only be applied to deterministically encrypted columns.

If joining between two columns, both columns need to use the same column encryption key.

Constants expressions that refer to encrypted columns are not allowed. For example, you cannot write WHERE SSN = '111-11-1111', but you can write WHERE SSN = @SSN. This is necessary because the driver works with the SqlParameter class to handle encryption requirements.

Unsupported data types include: xml, rowversion, image, ntext, text, sql_variant, hierarchyid, geography, geometry, and user defined-types.

Currently the only driver that supports this feature is .NET 4.6.

Rate this Article

Adoption Stage

Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Tell us what you think

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

This doesn't solve the real problem by Mark N

The problem is - people get a userid and pwd and get access to the db. The data must be encrypted at the "application" level. It also doesn't solve the problem of - sometimes your data is not in a relational db.

Do the right thing, not the "easy" thing

Re: This doesn't solve the real problem by Jonathan Allen

If you are using the direct access model, say a WPF application that talks to the database, the username and password aren't enough. You also need the certificate that will allow you to decrypt values.

If you are using the middle tier model, then yea, it's open season.

Still, this will reduce the damage caused by someone getting a copy of your database backups. That's a scenario people often forget about.

Re: This doesn't solve the real problem by Muhammad Zeeshan

Isn't Transparent Data Encryption (TDE) removes database backup scenario?

Re: This doesn't solve the real problem by Thuru Pathan

Yes TDE encrypts the DB backups as well, but the main use case of the Always Encrypted feature is, it keeps the data encrypted at rest and also during the flight.

The only place the data gets decrypted is in the memory of your application / application server.

When using TDE if any one who has access to database (like backup admins, auditors, ...) can view the data, but Always Encrypted doesn't allow this.

Finally I think this is a lesson Microsoft learnt from the cloud, and in the documentation they have mentioned that Always Encrypted will provide safety for your data from the highly privileged people who seek access to your data like governments.

Think that you enable Always Encrypted in the server running on a Azure VM and you have your own HSM lives on premise where you have stored your CMK, when someone asks for the data from Microsoft, they can hand over the access server in some unavoidable circumstances but still the intruders cannot get the data decrypted without accessing the HSM you have. Microsoft can simply say we don't have the key.

Great post! by John Walker

I do appreciate it and am more trying to understand than argue or any of that and have found some other useful KB article you might like

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

5 Discuss