Configure SQL Always Encrypted for the xDB Collection database

Current version: 10.3

Applies to	xDB Collection database (SQL) only.
Sitecore Installation Framework	Always Encrypted is not enabled by default.
Azure Toolkit	Always Encrypted is not enabled by default.

The xDB Collection database supports Always Encrypted for certain columns that contain sensitive data. This topic describes how to enable Always Encrypted for an existing xDB Collection database.

Switch to the staging tables execution strategy

In Sitecore 9.3 and later, the xDB Collection SQL provider uses the TVP (Table-Valued Parameters) execution strategy by default. You cannot use TVP and Always Encrypted together. Therefore, you must switch to the staging tables strategy before enabling Always Encrypted. You must also do this to Index Worker files.

Configure keys

The instructions for generating Column Master Keys (CMK) and Column Encryption Keys (CEK) differ depending on the type of key store that you are using.

Option 1: Windows Key Store

The following steps apply if you are using Windows Key Store.

Create keys

Refer to Microsoft’s Always Encrypted documentation for information about creating and using Column Master Keys (CMK) and Column Encryption Keys (CEK). The overall steps are:

Create Column Master Key (CMK) and Column Encryption Key (CEK).
Export the CMK and import it into the appropriate certificate stores on roles that connect to the xDB Collection database.

Ensure that each role’s user has permission to access the CMK. If you are using the Local Machine certificate store, each role user requires Read permission.

Topology	Roles	Example User (<IdentityType>\<username>)
XP Single	xConnect (standalone) (w3wp)	IIS AppPool\AppPoolName
	xConnect Search Indexer (Windows Service)	NT AUTHORITY\LocalService
XP Scaled	xConnect Collection Search (w3wp)	IIS AppPool\AppPoolName
	xConnect Collection (w3wp)	IIS AppPool\AppPoolName
	xConnect Search Indexer (Windows Service)	NT AUTHORITY\LocalService

Note

If you are using Azure Web App Services but not the Azure Key Vault, see Using SQL Always Encrypted with Azure Web App Service.

Configure xConnect roles

The following steps must be completed on every instance of the roles that access the xDB Collection database:

xConnect Search Indexer
xConnect Collection Search service
xConnect Collection service

Important

Remember to change the configuration on Index Worker files as well.

To configure xConnect roles to use Always Encrypted:

Open the <role-root>\App_data\config\Sitecore\Collection\sc.Xdb.Collection.Data.Sql.xml configuration file and set the UseAlwaysEncrypted element to true:

RequestResponse

<UseAlwaysEncrypted>true</UseAlwaysEncrypted>

Option 2: Azure Key Vault

The following steps apply if you are using Azure Key Vault.

Create keys

Refer to Microsoft’s Azure Key Vault documentation for information about setting up Azure Key Vault. The overall steps are:

Create a key vault and a new key.
Register applications (xConnect roles) in Azure Active Directory.
Authorize the applications to use the key/secret.
Create a Column Master Key (CMK) and Column Encryption Key (CEK) based on the Azure Key Vault. For information on how to do this, see the Master Key Configuration section in https://docs.microsoft.com/en-us/azure/sql-database/sql-database-always-encrypted-azure-key-vault

Configure xConnect roles

The following steps must be completed on every instance of the roles that access the xDB Collection database:

xConnect Search Indexer
xConnect Collection Search service
xConnect Collection service

Important

Remember to change the configuration on Index Worker files as well.

Open the <role-root>\App_data\config\Sitecore\Collection\sc.Xdb.Collection.Data.Sql.xml configuration file and set the UseAlwaysEncrypted element to true:
RequestResponse
```
<UseAlwaysEncrypted>true</UseAlwaysEncrypted>
```
In Sitecore 9.1 Update-1 and later, enable App_Data\Config\Sitecore\Collection\sc.Xdb.Collection.Data.Sql.KeyStoreProviders.xml.disabled. In Sitecore 9.1 Initial Release and earlier, enable <role-root>\App_Data\Config\Sitecore\CoreServices\sc.Xdb.Sql.Common.Encryption.AzureKeyVault.xml.disabled.

Note

This file defines the names of the ClientIdAppSettingsKey (devault value: AzureKeyVaultClientId) and ClientSecretAppSettingsKey (default value: AzureKeyVaultClientSecret) settings. You do not need to change these settings.

In <role-root>\App_Config\App.config, add the following app settings. Copy values for it from the Azure Key Vault (ClientId, SecretId):

RequestResponse

<add key="AzureKeyVaultClientId" value="1fb143e0-b103-472b-a281-c8727d3dfb2f" />
<add key="AzureKeyVaultClientSecret" value="mfCEb0z5r3UOIBMG38tpbdi1OvC9NvUSJV9u+VwpGEg=" />

Configure Always Encrypted on all shards

The following steps must be completed on each shard and assumes that the CEK and CMK keys have been created.

Generate a script that includes every stored procedure from the xDB Collection database, except all *Tvp procedures. Save the script.
Remove all the stored procedures from the xDB Collection database.
Disable Change Tracking for the following tables:
- [Contacts]
- [ContactFacets]
- [Interactions]
- [InteractionFacets]

Configure Always Encrypted for the following columns with the corresponding encryption types.

Column	Encryption Type
[ContactFacets].[FacetData]	RANDOMIZED
[ContactFacets_Staging].[FacetData]	RANDOMIZED
[InteractionFacets].[FacetData]	RANDOMIZED
[InteractionFacets_Staging].[FacetData]	RANDOMIZED
[DeviceProfileFacets].[FacetData]	RANDOMIZED
[DeviceProfileFacets_Staging].[FacetData]	RANDOMIZED
[ContactIdentifiers].[Identifier]	DETERMINISTIC
[ContactIdentifiers].[Source]	DETERMINISTIC
[ContactIdentifiers_Staging].[Identifier]	DETERMINISTIC
[ContactIdentifiers_Staging].[Source]	DETERMINISTIC
[ContactIdentifiersIndex].[Identifier]	DETERMINISTIC
[ContactIdentifiersIndex].[Source]	DETERMINISTIC
[ContactIdentifiersIndex_Staging].[Identifier]	DETERMINISTIC
[ContactIdentifiersIndex_Staging].[Source]	DETERMINISTIC
[GetContactIdsByIdentifiers_Staging].[Identifier]	DETERMINISTIC
[GetContactIdsByIdentifiers_Staging].[Source]	DETERMINISTIC
[GetContactsByIdentifiers_Staging].[Identifier]	DETERMINISTIC
[GetContactsByIdentifiers_Staging].[Source]	DETERMINISTIC
[CheckContacts_Staging].[Identifier]	DETERMINISTIC
[CheckContacts_Staging].[Source]	DETERMINISTIC
[UnlockContactIdentifiersIndex_Staging].[Identifier]	DETERMINISTIC
[UnlockContactIdentifiersIndex_Staging].[Source]	DETERMINISTIC

Re-enable Change Tracking for the following tables:
- [Contacts]
- [ContactFacets]
- [Interactions]
- [InteractionFacets]
Restore the stored procedures using the script created in step 1.

Grant the following permissions to the restricted user (by default, this user is named collection_user)

RequestResponse

GRANT VIEW ANY COLUMN MASTER KEY DEFINITION TO [collection_user]
GRANT VIEW ANY COLUMN ENCRYPTION KEY DEFINITION TO [collection_user]

If your database included contacts before you applied encryption, run the following query on all shards:

RequestResponse

UPDATE [ContactIdentifiersIndex] SET [IdentifierHash] = DATALENGTH([Identifier])
UPDATE [ContactIdentifiers] SET [IdentifierHash] = DATALENGTH([Identifier])

Do you have some feedback for us?

If you have suggestions for improving this article,