sync encrypted data from SQL to azure search

Prem Jha 6 Reputation points


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?

Azure SQL Database
Azure AI Search
Azure AI Search
An Azure search service with built-in artificial intelligence capabilities that enrich information to help identify and explore relevant content at scale.
507 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. GeethaThatipatri-MSFT 24,197 Reputation points Microsoft Employee

    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.



    1 person found this answer helpful.

  2. Sedat SALMAN 10,495 Reputation points

    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,them%20to%20the%20target%20table


    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)

    0 comments No comments