Sitecore offers a number of features relating to the protection of Personally Identifiable Information (PII) data and GDPR compliance. The indexing of personal information data is disabled by default and details of how Sitecore addresses GDPR “data subject rights” can be found here. But what about the xDB SQL data ? Experience Profile data and custom facet data is stored in the xDB collection database shards and anyone with privileges to perform SQL queries against that data can easily read this information or, even more worrisome, dump the entire database out to a BACPAC and share that BACPAC file or accidentally leave it on a hard drive somewhere. The Red Cross database leak should be enough to keep you awake at night if you have sensitive personal information in your xDB database (or any other databases for that matter.)

Azure SQL databases created after May 2017 are encrypted “at rest” by default, however that isn’t the whole story. The issue of SQL query access to data or exporting databases from Azure SQL Server via BACPAC is not resolved by encryption at rest. A developer with access to connection strings would be able to query the data and anyone with SQL admin access could export the data and inadvertently or intentionally expose it (hmm, now where did I put that USB stick with 2 Gb of xDB data again?) What can we do to improve the security of the data ? Enter SQL Always Encrypted.

The Security Guide documentation for Sitecore contains a section on configuring SQL Always Encrypted which goes part way to explaining how to achieve this, however it outlines a number of steps that are not explicitly detailed and are a bit impenetrable if you’ve not done them before, as well as requiring you to hunt down the relevant documentation across various sites. There’s also a few gaps in the Sitecore documentation, although these should hopefully be updated on the Sitecore docs site soon.

I’m going to talk here about doing this via an Azure Key Vault rather than the Windows Key Store, since most Sitecore developers are familiar with using MMC and the Windows certificate store. Plus many production deployments will be on Azure PaaS so the Key Vault is a logical choice, but the steps have some commonality which hopefully will be apparent and useful in either case. Also, there are a number of ways to do this but I’m going to outline the approach that I used. For example, you could use a certificate to create your Column Master Key if you prefer. I ran into issues with the PowerShell approach outlined on various Microsoft documentation pages so I opted for the more manual approach outlined below. If you get the PowerShell approach to work, then please share.

Disclaimer: I am not a SQL Server DBA nor a Security Administrator. I accept no responsibility for damage or data loss caused by following these instructions. Please test this approach in a safe environment before performing changes to production data.

Step 1 – create a key in Azure KeyVault

Firstly you’ll need a KeyVault in your Azure subscription. Just add a Key Vault resource from the portal:

Then create a key by clicking Generate/Import:

Give the key a name and choose the key type and key size:

You should now have a key in your key vault:

Step 2 – Assign appropriate permissions to your user to access and use the key

Now comes a REALLY IMPORTANT STEP that is currently undocumented. You must ensure that you have sufficient rights to create and use the keys. If you run into problems later when creating the Column Encryption Keys then come back to this step.

Click on Access policies and then select your Azure portal user (in my case as per below):

The permissions blade will then open and you must select the policies as per below:

The bottom 4 cryptographic operations will probably not be checked – you absolutely need the following operations: Unwrap Key, Wrap Key, Verify, Sign. Without those you will encounter some, ahem, cryptic error messages.

Finally click OK to exit the permissions blade and then make sure you click SAVE on the Access Policies screen that is displayed after that. It’s an easy step to miss and it will cause you grief later on if you forget to save.

In Part 2, I will cover how to create Column Master Keys and Column Encryption Keys based on the new key we have just created. In subsequent posts I will cover the creation of Application Registrations and Client Secrets, assigning permissions, configuring xConnect, and encrypting the data, as well as some other related topics.