Configure SQL Always Encrypted for the xDB Collection database

Abstract

How to enable the Always Encrypted feature on all Sitecore xDB shards using Windows Key store or Azure Key Vault.

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.

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:

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

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:

<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:

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

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

    <UseAlwaysEncrypted>true</UseAlwaysEncrypted>
    
  2. 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.

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

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

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

  1. Generate a script that includes every stored procedure from the xDB Collection database. Save the script.

  2. Remove all the stored procedures from the xDB Collection database.

  3. Disable Change Tracking for the following tables:

    • [Contacts]

    • [ContactFacets]

    • [Interactions]

    • [InteractionFacets]

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

    Column

    Encryption Type

    [ContactFacets].[FacetData]

    RANDOMIZED

    [ContactFacets_StagingTable].[FacetData]

    RANDOMIZED

    [InteractionFacets].[FacetData]

    RANDOMIZED

    [InteractionFacets_StagingTable].[FacetData]

    RANDOMIZED

    [DeviceProfileFacets].[FacetData]

    RANDOMIZED

    [DeviceProfileFacets_StagingTable].[FacetData]

    RANDOMIZED

    [ContactIdentifiers].[Identifier]

    DETERMINISTIC

    [ContactIdentifiers].[Source]

    DETERMINISTIC

    [ContactIdentifiers_StagingTable].[Identifier]

    DETERMINISTIC

    [ContactIdentifiers_StagingTable].[Source]

    DETERMINISTIC

    [ContactIdentifiersIndex].[Identifier]

    DETERMINISTIC

    [ContactIdentifiersIndex].[Source]

    DETERMINISTIC

    [ContactIdentifiersIndex_Staging].[Identifier]

    DETERMINISTIC

    [ContactIdentifiersIndex_Staging].[Source]

    DETERMINISTIC

    [GetContactIdsByIdentifiers_StagingTable].[Identifier]

    DETERMINISTIC

    [GetContactIdsByIdentifiers_StagingTable].[Source]

    DETERMINISTIC

    [GetContactsByIdentifiers_Staging].[Identifier]

    DETERMINISTIC

    [GetContactsByIdentifiers_Staging].[Source]

    DETERMINISTIC

    [CheckContacts_Staging].[Identifier]

    DETERMINISTIC

    [CheckContacts_Staging].[Source]

    DETERMINISTIC

  5. Restore the stored procedures using the script created in step 1.

  6. Re-enable Change Tracking for the following tables:

    • [Contacts]

    • [ContactFacets]

    • [Interactions]

    • [InteractionFacets]

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

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