Import data from SQL Managed Instance to Azure AI Search

Question

Import data from SQL Managed Instance to Azure AI Search

Viswavaageesh Chandramouli 25

How do I connect to an MS SQL Server data base via the Import data option in Azure AI search?

I'm able to connect to an Azure SQL data base but all my data is stored in a SQL Server. My connection string syntax is added below:

Server= ;Database=;Persist Security Info=True;User ID= ;Password= ;Encrypt=False; TrustServerCertificate=True

User's image

Thanks in Advance.

VenkateshDodda-MSFT 24,951 Reputation points Microsoft Employee Moderator

2024-09-19T11:16:59.0033333+00:00
@Viswavaageesh Chandramouli Thanks for posting your question in Microsoft Q&A.

If your SQL Server Database is hosted on Azure VM and if you are trying to import the data from it to Azure AI search? if yes, can you confirm whether you have followed all the documented steps mentioned here.

If you are trying to connect your Azure SQL database, then in the data Source then you need to select Azure SQL Database instead of SQL Server on Azure VMs. Also check whether "public access" is enabled on your Sql Server under Networking Tab.
Refer to these documented steps to connect to sql database with Azure AI Search.
VenkateshDodda-MSFT 24,951 Reputation points Microsoft Employee Moderator

2024-09-20T09:08:11.6333333+00:00

@Viswavaageesh Chandramouli Following up to see if you have a chance to check my previous response and helped. Do let me know if you have any further questions on this.
Viswavaageesh Chandramouli 25 Reputation points

2024-09-20T09:32:13.83+00:00

Thanks a lot @VenkateshDodda-MSFT for the reply. I've sent across the link's you've shared with my data base developer. Will soon given an update here.

Appreciate your swift responses.
Viswavaageesh Chandramouli 25 Reputation points

2024-09-20T14:36:07.6066667+00:00

@VenkateshDodda-MSFT Is there any other way to overcome Public access endpoints ?

My development manager raised red flags as he is not at all comfortable that opening up public endpoints on the SQL servers is a valid operational way to go.

The connections to a SQL Managed Instance (Indexer connection to SQL Managed Instances - Azure AI Search | Microsoft Learn) seems to be via a public endpoint.

This is alarming to say the least as we have done everything possible to prevent public access to our SQL servers, but the documentation seems to indicate that we need to enable the public endpoints to the SQL servers. Yes, there are security rules that will restrict access, but this still makes me incredibly nervous. (The connections to the SQL on VM servers also require public IP access – same issue: “Azure AI Search requires an encrypted channel for all indexer requests over a public internet connection”).
VenkateshDodda-MSFT 24,951 Reputation points Microsoft Employee Moderator

2024-09-23T14:14:34.3033333+00:00

@Viswavaageesh Chandramouli Thanks for your response and apologize for the delay in my response.

I understand your concern then did you get any chance to check this Connecting to SQL MI using private link with AI search. I hope this will suits your requirements as you are looking to private communication rather than public.
Viswavaageesh Chandramouli 25 Reputation points

2024-09-23T14:41:25.38+00:00

@VenkateshDodda-MSFT Appreciate the link a lot. Just sent it over to my Development manager. Will get back to this Thread as soon as I get a reply.
VenkateshDodda-MSFT 24,951 Reputation points Microsoft Employee Moderator

2024-09-25T04:27:39.0566667+00:00

@Viswavaageesh Chandramouli Thanks for you response,

Following up to see if we have any update from your development team. I hope the above shared are helpful, let me know if you have any further questions on this.
Viswavaageesh Chandramouli 25 Reputation points

2024-09-25T08:38:42.0366667+00:00

Will give an update by COP tomorrow (UK time). Once again, Thanks. It's very reassuring to have your support.

2 answers

Your answer

VenkateshDodda-MSFT 24,951 Reputation points Microsoft Employee Moderator

2024-09-19T11:16:59.0033333+00:00

@Viswavaageesh Chandramouli Thanks for posting your question in Microsoft Q&A.

If your SQL Server Database is hosted on Azure VM and if you are trying to import the data from it to Azure AI search? if yes, can you confirm whether you have followed all the documented steps mentioned here.

If you are trying to connect your Azure SQL database, then in the data Source then you need to select Azure SQL Database instead of SQL Server on Azure VMs. Also check whether "public access" is enabled on your Sql Server under Networking Tab.
Refer to these documented steps to connect to sql database with Azure AI Search.
VenkateshDodda-MSFT 24,951 Reputation points Microsoft Employee Moderator

2024-09-20T09:08:11.6333333+00:00

@Viswavaageesh Chandramouli Following up to see if you have a chance to check my previous response and helped. Do let me know if you have any further questions on this.
Viswavaageesh Chandramouli 25 Reputation points

2024-09-20T09:32:13.83+00:00

Thanks a lot @VenkateshDodda-MSFT for the reply. I've sent across the link's you've shared with my data base developer. Will soon given an update here.

Appreciate your swift responses.
Viswavaageesh Chandramouli 25 Reputation points

2024-09-20T14:36:07.6066667+00:00

@VenkateshDodda-MSFT Is there any other way to overcome Public access endpoints ?

My development manager raised red flags as he is not at all comfortable that opening up public endpoints on the SQL servers is a valid operational way to go.

The connections to a SQL Managed Instance (Indexer connection to SQL Managed Instances - Azure AI Search | Microsoft Learn) seems to be via a public endpoint.

This is alarming to say the least as we have done everything possible to prevent public access to our SQL servers, but the documentation seems to indicate that we need to enable the public endpoints to the SQL servers. Yes, there are security rules that will restrict access, but this still makes me incredibly nervous. (The connections to the SQL on VM servers also require public IP access – same issue: “Azure AI Search requires an encrypted channel for all indexer requests over a public internet connection”).
VenkateshDodda-MSFT 24,951 Reputation points Microsoft Employee Moderator

2024-09-23T14:14:34.3033333+00:00

@Viswavaageesh Chandramouli Thanks for your response and apologize for the delay in my response.

I understand your concern then did you get any chance to check this Connecting to SQL MI using private link with AI search. I hope this will suits your requirements as you are looking to private communication rather than public.
Viswavaageesh Chandramouli 25 Reputation points

2024-09-23T14:41:25.38+00:00

@VenkateshDodda-MSFT Appreciate the link a lot. Just sent it over to my Development manager. Will get back to this Thread as soon as I get a reply.
VenkateshDodda-MSFT 24,951 Reputation points Microsoft Employee Moderator

2024-09-25T04:27:39.0566667+00:00

@Viswavaageesh Chandramouli Thanks for you response,

Following up to see if we have any update from your development team. I hope the above shared are helpful, let me know if you have any further questions on this.
Viswavaageesh Chandramouli 25 Reputation points

2024-09-25T08:38:42.0366667+00:00

Will give an update by COP tomorrow (UK time). Once again, Thanks. It's very reassuring to have your support.

Answer 1

Viswavaageesh Chandramouli 25

To use private connection to the SQL Managed Instance, please follow the instructions here: https://learn.microsoft.com/en-us/azure/search/search-indexer-how-to-access-private-sql

Thanks @VenkateshDodda-MSFT

Answer 2

@Viswavaageesh Chandramouli Thanks for your patience on this.

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer.

Issue: Customers want to import data from SQL managed Instance to Azure AI Search over Private network.

Solution:

To use private connection to the SQL Managed Instance, please follow the instructions here: https://learn.microsoft.com/en-us/azure/search/search-indexer-how-to-access-private-sql

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

I hope this helps!

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

Share via

Import data from SQL Managed Instance to Azure AI Search

2 answers

Your answer