In Part 1 we looked at the reasons for encrypting xDB data and at creating a key in an Azure Key Vault. In this second article in the series we will look at creating Column Master Keys and Column Encryption Keys in SQL Server. The process needs to be performed on all xDB collection database shards and for each shard you need to create a Column Master Key (CMK) based on the key we created in Part 1 and a Column Encryption Key (CEK) based on the CMK.

Step 1 – create the Column Master Key

Open up SQL Server Management Studio (SSMS) and navigate to the xDB Collection databases. You will most likely have a Shard Map Manager (SMM) and at least 2 Shard databases. The changes you make in the rest of this process will be made to ALL shard databases but the ShardMapManager will not be affected.

Here’s a sample screenshot of the 3 relevant databases (the prefix ma-demo_ is irrelevant, the databases are for illustrative purposes only):

Expand Xdb.Collection.Shard0 and navigate to Security > Always Encrypted Keys > Column Master Keys.

From the context menu of the Column Master Keys node, select the New Column Master Key option:

A modal dialog window will pop up from which you can choose the location of the key (or certificate) from which you will create your Column Master Key (CMK). You can see below the options are available to use a Windows Certificate Store (Current User or Local Machine), Azure Key Vault, or CNG. I have selected Azure Key Vault.

You will then need to log into your Azure subscription:

Once logged in, you should see your Key Vault and the key that you created earlier:

Select the key – in this case “SQL-DEV-AlwaysEncryptedKey” and type a suitable name (in the screenshot above I have called it “DEV-CMK-Shard0”. Click Generate Key. You should now see the new Always Encrypted Column Master Key in your Shard0 database.

Step 3 – create a Column Encryption Key

Once you have a Column Master Key based on your key (or certificate) in the Azure Key Vault you can create a Column Encryption Key (CEK). The CEK is, as you might expect, the key that is used to encrypt (and decrypt!) the columns in the tables of your database. Each Shard database will have its own CMK and each CEK will be based on the CMK in the appropriate Shard database.

This bit is very simple. Click on the context menu for Column Encryption Keys and select New Column Encryption Key:

Give the new CEK a name, choose the CMK from the Column master key drop down and click OK.

If you experience an error like this:

then go back to Part 1 of this series of articles and revisit Step 2. You need to assign appropriate permissions to your account in order to create the CEK.

Otherwise you should see the following in your Shard0 database:

You should now have a new CEK in Shard0. Repeat the above steps for the other xDB collection shard database(s) and you will be (nearly) ready to encrypt the data. We will cover off the next step in Part 3 of this series.