Search service – add data source from SQL database in private network

Published May 30 2022 01:09 AM 890 Views
Microsoft

Pre-requirements:

Before we start, please read these documents Introduction to Azure Cognitive Search and Data sources gallery - Azure Cognitive Search.

Referred from these documents above you should understand the relationship between Azure Search service, Index and Data source. The Search Service saves un-indexed data from data sources to its indexes, then client application could search the results from index through queries. The highlight part in below image shows the process of the indexing.

Scarlett_liu_1-1653897781431.png

 

But how about the data sources under private network, how could we set the connection between them? According to our document Connect through a private endpoint - Azure Cognitive Search | Microsoft Docs we have shared private endpoint as the outbound of Search service.

Below is the test I did to explain how we use shared private endpoint between Azure search with SQL Database.

Test steps:

  • SQL Server- At first, I created a SQL server which had SQL DB in selected network with a Virtual network.

Scarlett_liu_2-1653897781455.png

 

  • Add shared private endpoint- As this SQL server in a selected network, then follow the document Connect through a private endpoint - Azure Cognitive Search | Microsoft Docs I wanted to add a shared private endpoint. I went to Azure portal, found my search service. And then selected “Networking” in the left menu. Clicked the third tab “Shared Private Access”, created a new shared private endpoint.

Scarlett_liu_3-1653897781480.png

 

 

  • Approve Private endpoint- Then I backed to Azure SQL server, found the networking->private access. There was a private endpoint need to be approved.

Scarlett_liu_4-1653897781498.png

 

So I approved it, after that I backed to my Search Service shared private access again, this shared private endpoint showed connection state “Approved”.

Scarlett_liu_5-1653897781512.png

 

 

  • Add in data source--After above steps to add a shared private endpoint, I tried to add this SQL database as a data source of Search Service. But it got an error like this in below screenshot.

Scarlett_liu_6-1653897781525.png

 

What’s the reason? Here please be careful, before we add this SQL DB as the data source of Search Service, we need to test the connection through that button “Test connection”. Here we test between the Azure portal and Azure SQL database. Like this error messages in the screenshot, we need to add the client IP of portal in SQL server firewall list.

Thus, I added the Client Ip in my SQL server firewall list. After that tested add data source again. It passed the test connection and added my table in the SQL DB.

Scarlett_liu_7-1653897781534.png

 

  • Create Index and indexer- We can create index, indexer in Azure portal or reset API from Create Indexer (Azure Cognitive Search REST API. Please remember to add the red color words  part of “executionEnvironment” in your Indexer parameter to make sure the data in private.

{

    "name": "indexer",

    "dataSourceName": "sql-datasource",

    "targetIndexName": "index",

    "parameters": {

        "configuration": {

            " executionEnvironment ": "private"

        }

    },

    "fieldMappings": []

}

 

In conclusion to add a SQL Database from private network please remember these steps in below:

  1. Add shared private endpoint in search service.
  2. Approve the shared private endpoint in SQL server.
  3. Add client IP of Azure portal in SQL server firewall list (If you know the IP you can add it as the first step)
  4. Test connection and add the data source.
  5. Add index and indexer. Set the indexer parameters configuration “executionEnvironment” to private.

If you want to add other data source like Storage Account, it doesn’t have the step to test connection. Then it’s not necessary to add Azure portal IP in the firewall list to set up Search Service data source.

Co-Authors
Version history
Last update:
‎May 30 2022 01:05 AM
Updated by: