1

I am following the below Sitecore documentation to enable Sql Always Encrypted in Sitecore 9.3 https://doc.sitecore.com/xp/en/developers/93/platform-administration-and-architecture/configure-sql-always-encrypted-for-the-xdb-collection-database.html

While execution step 5: "Restore the stored procedures using the script created in step 1" in "Configure Always Encrypted on all shards" section, I am getting below error restoring stored procedure "xdb_collection.UnlockContactIdentifiersIndex" and all stored procedure whose name is ending with "TVP" and

Msg 402, Level 16, State 2, Procedure XXXXX, Line 27 [Batch Start Line 9] The data types varbinary and varbinary(700) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'Key-Local-Shard0', column_encryption_key_database_name = 'DB_Xdb.Collection.Shard0') are incompatible in the equal to operator.

1 Answer 1

1

It looks like the official documentation is incomplete and you should also enable columns encryption on the UnlockContactIdentifiersIndex_Staging table on the following columns:

Column Encryption Type
[UnlockContactIdentifiersIndex_Staging].[Identifier] DETERMINISTIC
[UnlockContactIdentifiersIndex_Staging].[Source] DETERMINISTIC

For the errors related to stored procedures ending in Tvp, I believe these stored procedures are used by the xDB Collection SQL provider with the Table-Valued Parameters execution strategy only (introduced in SC 9.3) and this strategy is not compatible with Always Encrypted. The documentation indeed asks to switch to the Staging Tables strategy. So these specific errors should be ok to ignore.

Not directly related to the error that you described in your question, but still related to the same step 5 that is failing for you, I have also experienced the following errors during the script execution:

Msg 22105, Level 16, State 1, Procedure GetContactFacetsChanges, Line 57 [Batch Start Line 1476]
Change tracking is not enabled on table 'xdb_collection.ContactFacets'.
Msg 22105, Level 16, State 1, Procedure GetContactsChanges, Line 57 [Batch Start Line 2042]
Change tracking is not enabled on table 'xdb_collection.Contacts'.
Msg 22105, Level 16, State 1, Procedure GetInteractionFacetsChanges, Line 57 [Batch Start Line 2532]
Change tracking is not enabled on table 'xdb_collection.InteractionFacets'.
Msg 22105, Level 16, State 1, Procedure GetInteractionsChanges, Line 57 [Batch Start Line 3039]
Change tracking is not enabled on table 'xdb_collection.Interactions'.

The documentation asks to re-enable the change tracking at step 6 after the script executed at step 5, but it looks like the re-enabling needs to occur before to avoid these errors.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.