--- title: "Configure SQL Always Encrypted for the xDB Collection database" slug: "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." --- # Configure SQL Always Encrypted for the xDB Collection database | **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](https://doc.sitecore.com/xp/en/developers/104/platform-administration-and-architecture/configure-the-tvp-or-staging-tables-execution-strategy-for-the-xdb-collection-sql-provider.html "Configure the TVP or staging tables execution strategy for the xDB Collection SQL provider"). 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](https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-2017) 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)](https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/create-and-store-column-master-keys-always-encrypted?view=sql-server-2017). - [Export the CMK and import it into the appropriate certificate stores](https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/configure-always-encrypted-using-sql-server-management-studio?view=sql-server-2017) on roles that connect to the xDB Collection database. - Ensure that each role’s user has [permission to access the CMK](https://docs.secureauth.com/display/KBA/Grant+Permission+to+Use+Signing+Certificate+Private+Key). If you are using the **Local Machine** certificate store, each role user requires **Read** permission. | **Topology** | **Roles** | **Example User** (\) | | --- | --- | --- | | 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](https://blogs.msdn.microsoft.com/sqlcat/2016/09/15/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 `\App_data\config\Sitecore\Collection\sc.Xdb.Collection.Data.Sql.xml` configuration file and set the `UseAlwaysEncrypted` element to true: ```text true ``` ### 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](https://docs.microsoft.com/en-us/azure/key-vault/key-vault-get-started) for information about setting up Azure Key Vault. The overall steps are: - [Create a key vault and a new key](https://docs.microsoft.com/en-us/azure/key-vault/). - 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](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. 1. Open the `\App_data\config\Sitecore\Collection\sc.Xdb.Collection.Data.Sql.xml` configuration file and set the `UseAlwaysEncrypted` element to true: ```text true ``` 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 `\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 `\App_Config\App.config`, add the following app settings. Copy values for it from the Azure Key Vault (ClientId, SecretId): ```xml ```

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) ``` 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: ``` UPDATE [ContactIdentifiersIndex] SET [IdentifierHash] = DATALENGTH([Identifier]) UPDATE [ContactIdentifiers] SET [IdentifierHash] = DATALENGTH([Identifier]) ```