sync encrypted data from SQL to azure search

Question

Hi,

I have a SQL database with encrypted columns and a azure search.

what i am trying to do:

1. i am trying to sync the encrypted data from SQL to Azure Search and after syncing i want to search over that encrypted data.

is there any way or alternative to achieve it?

Answer 1

Hi @prem jha Thanks for posting your question in the Microsoft Q&A forum.

Unless you can add the same key for encryption with Encrypt data using customer-managed keys - Azure AI Search | Microsoft Learn and push to the index, and then decrypt the data with that same key,

I don't think there is other workaround. 

I hope this information helps.

Regards

Geetha

Answer 2

https://learn.microsoft.com/en-us/azure/search/search-howto-connecting-azure-sql-database-to-azure-search-using-indexers

as you can see from the link Always Encrypted columns aren't currently supported by Azure AI Search indexers.

but we can find a workaround for this

• For copying encrypted data between tables without decrypting it, Always Encrypted provides the “Allow Encrypted Value Modifications” switch. This option lets applications move encrypted data between databases without extra overhead or security risk
• This switch can be implemented via the AllowEncryptedValueModification flag in the SQL Bulk Copy API or the ALLOW_ENCRYPTED_VALUE_MODIFICATIONS user option
• Using this approach, Always Encrypted should be disabled for both the source and target database connections to ensure the data remains encrypted during the copy

https://techcommunity.microsoft.com/t5/sql-server-blog/best-practices-for-moving-data-encrypted-with-always-encrypted/ba-p/384608#:~:text=Always%20Encrypted%20is%20disabled%20for,them%20to%20the%20target%20table

then

you can use Azure Data Factory to create ETL workflows and the n you can encrypt data on Azure AI Search by using

Customer-Managed Keys (CMK)

https://learn.microsoft.com/en-us/azure/search/search-security-manage-encryption-keys