Always Encrypted

Majority of my current projects are clients of Insurance industry, and similar to any sector which handles and stores personal data they also follow stringent security policies.

Audits are regular thing in any organization and in one of the internal audits it was decided to hide the confidential data like Social Security Number from everyone including all the DBAs.

Prior to SQL Server 2016 there wasn’t any feature that could have helped but starting from SQL Server 2016 we have Always-Encrypted.

The concepts that helps in protecting the data even from DBA are:
1. Column Master Key (CMK)
2. Column Encryption Key (CEK)

Column Master Key

It is a key encrypting key (for example, a key that is used to encrypt other keys) that is always in a client’s control, and is stored in an external key store. An Always Encrypted-enabled client driver interacts with the key store via a CMK store provider, which can be either part of the driver library (a Microsoft/system provider) or part of the client application (a custom provider). – Taken from docs.

Column Encryption Key

It is a content encryption key protected by CMKdocs.

What it basically means is that we have two encryption keys one (CMK) is stored somewhere where even the DBAs don’t have access to and the second key (CEK) stored in the database which is encrypted by the first.

Configuring Always Encrypted

We can configure AE using SSMS or using Powershell. We cannot use T-SQL for this because we need to have a certificate created at Windows or in Azure Key Vault and that is not possible using T-SQL.

Configuring Always Encrypted is easy at SQL Server end but there are many things to consider. Refer this link for the limitations.

To configure AE using GUI, right click the database and select “Encrypt Columns”

Configuring AE

Encryption type is deciding if the same plain text will lead to same cipher or a different one every time.

MS suggests to use deterministic encryption for columns that will be used as search or grouping parameters. And use randomized encryption for data such as confidential investigation comments, which aren’t grouped with other records and aren’t used to join tables

Once the columns are selected choose the Master Key, either the Windows certificate store or from Azure Key Vault and choose an already created certificate or create a new one.

Choosing CMK

In the next tab you will be given the option to either generate PowerShell script or to proceed to finish.

Hiding the data from a DBA

The main thing to consider is that the server from which the Windows certificate is generated should not be the database server itself. If we are creating the server certificate from database server than after configuring AE take the backup of the certificate and move it to the application server.

If using WCS make sure to restore the backup of the certificate on every server from where the data will be accessed.

Notes and things to consider

  1. The database only stores the metadata of the Column Master key, original key is stored in the WCS or Azure Key Vault.
  2. Security admins can be given the permission or access to create CEK and CMK but not the actual data. And DBA can have access to the data and the metadata but not to the WCS or AKV. And so restricting the data visibility from both.
  3. The client application issues a parameterized query. And has to use the “Column Encryption Setting = Enabled” in the connection string.
  4. Temporal and CDC is not supported, only change tracking is.
  5. Replication is not supported.
  6. Always On and Log Shipping can be be configured as long as we have the certificate at the other end too.
  7. When using a Web Server in between like IIS then the certificate should be stored there, or that server should have access to AKV.


There are four permissions for Always Encrypted:

ALTER ANY COLUMN MASTER KEY (Required to create and delete a column master key.)

ALTER ANY COLUMN ENCRYPTION KEY (Required to create and delete a column encryption key.)

VIEW ANY COLUMN MASTER KEY DEFINITION (Required to access and read the metadata of the column master keys to manage keys or query encrypted columns.)

VIEW ANY COLUMN ENCRYPTION KEY DEFINITION (Required to access and read the metadata of the column encryption key to manage keys or query encrypted columns.)

Changing the CMK or CEK

In case the keys are compromised we can always rotate the key. To rotate the CMK:
1. Database -> Security -> Always Encrypted Keys -> Right click and create new Master Key.
2. Once created right click the original CMK and choose “Rotate”

CMK Rotate

Choose the new CMK in Target and click OK. It will decrypt all the CEK using the older CMK and re-encrypt it using the new CMK and store it.

To change the CEK: Object Explorer -> Right the database -> Choose encrypt Columns and in the Column Select tab select another CEK.

CEK change

Removing Always Encrypted

To remove AE go back to Encrypt Columns and select plain text for the encrypted columns.

Back to plain text using Encrypt Columns

That’s all for today, hope it helps you.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s