Configure SQL Always Encrypted for the xDB Collection database

Version: 10.1

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:

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:

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.

  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:

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

    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.

  1. Generate a script that includes every stored procedure from the xDB Collection database, except all *Tvp procedures. 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_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

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

    • [Contacts]

    • [ContactFacets]

    • [Interactions]

    • [InteractionFacets]

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

  7. 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]
    
  8. 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,