In this post we’re going to go through the steps to set up Always Encrypted and create an encrypted column. As with my last post we’re looking at the flavour of Always Encrypted without enclaves, we’ll look at working with enclaves in detail later on.

It is a straightforward process to set up everything required for Always Encrypted. In fact, there is a wizard provided in SQL Server Management Studio (SSMS) that will do it all for you. In these examples, however, we will focus on performing the individual steps manually as that gives you a better view of what is going on. For all the objects involved we’ll look in detail at what is created so that you have a good level of understanding.

Before starting it is best to make sure that the version of SSMS you are using is up to date so that it has the full support for Always Encrypted. Anything above version 18 is fine.

Create Keys and Certificates

Before we start, we need a database we’re going to work in. We’ll call it TestAlwaysEncrypted – you can create it with the following SQL:

CREATE DATABASE TestAlwaysEncrypted;

Creating the Certificate and Column Master Key

First, we’ll create the Column Master Key (CMK). We have a few options for a CMK; in this post we’ll focus on the option of using a certificate stored on the local machine which contains an asymmetric key. You can also use an asymmetric key stored in an external key store such as Azure Key Vault; we’ll look at that option in a later post. When you create the CMK through SSMS, it also creates a CMK object in your database that identifies the location and identity of the actual key.

If you expand your database in the SSMS Object Explorer, you will find Always Encrypted Keys under the Security folder for the database. Right-click over Column Master Keys and select to create a new one, as shown in below:

This is the GUI that comes up:

At the top you can see I’ve given my new CMK the name TestCMK. In the box below we see a list of certificates in the selected key store available for use by Always Encrypted. I created the highlighted one simply by clicking the Generate Certificate button at the bottom. The Key Store for the certificate to be created in defaults to the Current User store. It is worth noting that when you generate a certificate, it is created on the local machine you are running SSMS on – not the SQL Server you are connected to – unless you are running SSMS on the server itself. Your account will need permissions to create certificates in the selected certificate store, or the generate certificate button will be grayed out – in that case you may have to run SSMS as an administrator to resolve the issue.

The decision of whether to create the certificate in the Local Machine or Current User store becomes more relevant once you are deploying the certificate to environments other than your local development environment. In those cases, I prefer to deploy to the Current User store for the account the application runs under – or if deploying to Local Machine, you can set the access for the certificate so only the application user account can access it. This minimizes the set of accounts that can access the certificate.

Click OK and the new CMK will be created. We can then see it listed under the Column Master Keys folder in SSMS, as shown here:

To understand what this key actually is, I’m going to right-click and generate the SQL definition. This also shows you the code if you want to create the SQL Server CMK object through T-SQL:

CREATE COLUMN MASTER KEY [TestCMK]
WITH
(
KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
KEY_PATH = N'CurrentUser/My/CE751A6A9CB3732508D6A7E8368E5B3770CF7328'
);

We only have two values, KEY_STORE_PROVIDER_NAME and KEY_PATH. All this is telling us is where to find the certificate we just created; KEY_PATH shows the path in the certificate store including the certificate’s thumbprint. If you are following along, your certificate will have its own unique thumbprint. You can see that the Column Master Key object stored in your database is just a pointer to the actual CMK, in this case a certificate stored on client machines.

If you want to create your certificate without using SSMS, you can do so with PowerShell. Here is an example:

$cert = New-SelfSignedCertificate -Subject "AlwaysEncryptedCert" -CertStoreLocation Cert:CurrentUser\My -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage DataEncipherment -KeySpec KeyExchange

In practice, when deploying your applications, you are likely to create the certificate ahead of time and deploy it as part of a deployment package. At a minimum you are also likely to want to use different certificates in your dev/test vs. production environments.

Creating the Column Encryption Key (CEK)

This is the last thing we need before we can set up encryption on our columns. The CEK is a symmetric key that will be used to encrypt our actual data and will be stored in the database encrypted by the CMK. You can find the Column Encryption Keys folder underneath Column Master Keys in SSMS which you saw earlier. Again, right-click to create a new key and bring up the GUI:

You can see I’ve called mine TestCEK, and in the dropdown, I’ve selected the CMK that I just created. Click OK and the CEK is created. We can see it in the object browser:

As with the CMK we’ll script the key out through SSMS so you can see what is created and the code involved:

CREATE COLUMN ENCRYPTION KEY [TestCEK]
WITH VALUES
(
COLUMN_MASTER_KEY = [TestCMK],
ALGORITHM = 'RSA_OAEP',
ENCRYPTED_VALUE = 0x016E000001630075007...
);

We can see it tells us what CMK this is based on, what Algorithm has been used to encrypt the CEK, and a long binary Encrypted Value (which I’ve truncated here to make things more readable). The Encrypted Value is the actual key used to encrypt data, itself encrypted by the CMK. The Encrypted Value here is unique to my system. If you create your own CEK, you will have a different value.

The unencrypted value of the CEK never exists on the SQL Server, so both the CMK and CEK are required to interact with encrypted data. This is what makes Always Encrypted so secure – even if you have admin rights on the SQL instance, you don’t have access to the unencrypted value of the CEK, so you can’t read data unless you have access to the CMK too.

You can deploy a pre-created CEK using the preceding code, but you cannot generate a fresh CEK using T-SQL. part of the reason for that is that SQL Server cannot access the CMK which is required to encrypt the CEK. you have to use the GUI in SSMS, or if you wish to do it through code, then there are methods available with PpowerShell.

Create an Encrypted Column

We’re going to look here at how you define a column as encrypted when you create a new table. In later posts we will look at methods for encrypting existing data.

Always Encrypted can work with most types of data, but there are some restrictions on which data types you can encrypt – we’ll look at that later too. In general, you can encrypt both numbers and strings, but you are limited in the extra functionality you can have against encrypted columns, such as constraints, and you wouldn’t be able to encrypt columns with properties like IDENTITY. A specific restriction that it is good to be aware of is that string-based columns must use a BIN2 collation type. You’ll see what I mean by that when we create the table.

With Always Encrypted you can also use one of two types of encryption, randomized or deterministic. The difference is that with randomized encryption, where you encrypt the same value multiple times (where the value exists in multiple rows within your data), the encrypted values will all be different. With deterministic, the same unencrypted value will always end up with the same encrypted value. Randomized encryption is more secure and you should use it where you can, but it does limit your functionality. For instance, you can’t have an index on a column with randomized encryption; if you think about it, then that makes sense; if all the underlying values that are the same are stored as different values in the table, then how would it be possible to index them. If you want to index an encrypted column, then it must use deterministic encryption.

Another key restriction is that you cannot query with a WHERE clause against a column with randomized encryption. Let’s understand that. Say I have an encrypted text column using randomized encryption and want to find all rows that match a particular value. I have to try doing that by encrypting the value I am searching for and matching that against the encrypted values in the table, but because we are using randomized encryption, my encrypted search value will be different to all the encrypted values in the table even when the unencrypted value might be the same, so no match would be possible. In such scenarios SQL Server will return an error to tell you the operation is not allowed so you don’t attempt to execute queries where the result would be incorrect.

Deterministic encryption however is slightly less secure. Imagine that you are encrypting a text column which has a limited number of possible values; it may be possible to use frequency analysis to analyze the number of occurrences of each encrypted value and thereby match/guess what real value each corresponds to.

Let’s go ahead and create a table with two encrypted text columns. For the sake of example, we’ll use deterministic encryption for one and randomized for the other. The following SQL creates the table:

USE TestAlwaysEncrypted;
CREATE TABLE dbo.EncryptedTable(
Id INT IDENTITY(1,1) CONSTRAINT PK_EncryptedTable PRIMARY KEY CLUSTERED,
LastName nvarchar(50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = TestCEK, ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
FirstName nvarchar(50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = TestCEK, ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL
);

We execute our SQL statement, and the table is created. You can see that what we are doing is specifying the details of encryption for each column we want to encrypt. We specify which CEK to use, which type of encryption (deterministic or randomized), and finally the algorithm used to encrypt the data in the column – though the algorithm specified is the only option at the current time.

Let’s just discuss the requirement for a BIN2 collation in more detail as you might be wondering why this is needed. A collation specifies the way data is compared and sorted. One feature of BIN2 is that it is a case-sensitive collation. That means, for instance, that if you search for a string like “Matthew,” it won’t match with “MATTHEW” or “matthew.” If you think about it, then it makes sense that you can’t have case-insensitive comparisons on an encrypted column. When you perform a comparison based on encrypted data, what the engine is doing is comparing one encrypted value with another. To enable a case-insensitive comparison there would have to be some deterministic pattern so that you can tell that two different encrypted values differ only by case. That would be more complicated to implement, would weaken the encryption, and isn’t supported by the encryption algorithm used by Always Encrypted. The requirement to use a BIN2 collation is driven by what will happen in practice when you compare two encrypted values looking for an exact match and also how such data is sorted. That creates some limitations on things like searching against encrypted columns – we’ll talk more about that later.

In terms of setup, that’s all you need to do, so you can see Always Encrypted is quite simple to implement.

Summary

Setting up Always Encrypted is straightforward. The key points to understand are:

• You need a certificate and key pair which sit on the client machine or application server; this is the Column Master Key (CMK). Alternatively you can use an asymmetric key in an external key store.
• In the database we have a CMK object, but this is just a pointer that tells us where the actual CMK can be found.
• The Column Encryption Key (CEK) is stored in the database but is stored encrypted by the CMK. This key is what is used to encrypt or decrypt data.
• To access data, you need access both to the CEK and the CMK.

This post is part of a comprehensive series on SQL Server Encryption. Subscribe to my blog for updates as new posts are published or you can buy my book through the link in the sidebar to get it all in one go.

And if you’re embarking on an encryption project (or anything else to do with SQL Server) and want some help, I’m available for consulting – please get in touch or check out my services page to find out what I can do for you.