Couldn't establish connection between Azure SQL VM and ADF which are located in 2 Different resource groups.

Thanuja 20

Hi,

In my Azure environment, Under one subscription, there are 2 resource groups, In resource group A I have Azure SQL VM and it has tables I need, now I have to replicate these tables to another Azure SQL Server which is in Resource group B and I am trying to achieve this through ADF, But I am not able to establish the connection between my ADF and the Azure SQL VM through linked services. Can someone help me with this?

Nandan Hegde 32,421 Reputation points MVP

2024-03-06T07:48:00.0633333+00:00

what error are you getting while trying to access the SQL from the ADF?

Which integration run time are you using?

Since the SQL database is an IaaS, you would have to set up a Self hosted IR and leverage that in ADF linked service to access the database
Thanuja 20 Reputation points

2024-03-06T17:29:13.0433333+00:00

Hi, Nandan,

Below this error, I am receiving, and for the integration, I did create a new integration run time with self-hosted. Also, I added the IP of this IR in the Azure SQL VM in the inbound rules which is in another resource group to allow ADF to connect to the Azure SQL VM.

Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'xx.xxx.xx.xxx', Database: 'xxxx', User: 'XXXX'. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server), SqlErrorNumber=53,Class=20,State=0, The network path was not found
phemanth 10,480 Reputation points Microsoft Vendor

2024-03-07T07:06:01.09+00:00
@Thanuja

Thanks for reaching out to Microsoft Q&A.

The error message you’re seeing is often due to a network-related issue or the SQL Server not being configured to allow remote connections.

Here are a few things you can check:

Server Name: Verify that the instance name is correct. The server name should be the fully qualified domain name (FQDN) of the Azure SQL VM.

Firewall Settings: Check the firewall settings on the Azure SQL Database to make sure that the Azure Integration Runtime is allowed to access the database You can do this by going to the “Firewalls and virtual networks” section in the Azure SQL Database blade in the Azure portal.

Network Connectivity: Make sure that the Azure Integration Runtime is able to communicate with the Azure SQL Database over the private endpoint. You can check this by using a tool like ping or traceroute to test connectivity between the two resources.

SQL Server Configuration: Verify that SQL Server is configured to allow remote connections.

Azure Integration Runtime Logs: Check the logs for the Azure Integration Runtime to see if there are any additional error messages or details that can help to identify the cause of the issue.

If you’ve checked all of these and are still experiencing issues, please do let us know.
phemanth 10,480 Reputation points Microsoft Vendor

2024-03-08T09:39:13.1366667+00:00

@Thanuja

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Thanuja 20 Reputation points

2024-03-20T16:23:47.3333333+00:00

Hi,

I was able to resolve the issue as the VM I was trying to connect did not have a SQL DB. When I tried connecting to the storage account the connection was established. Thanks for the help :)
phemanth 10,480 Reputation points Microsoft Vendor

2024-03-21T04:30:59.05+00:00

@Thanuja Glad to know your issue has been resolved. 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.

1 answer

phemanth 10,480 Reputation points Microsoft Vendor

2024-03-21T04:28:21.7333333+00:00

@Thanuja

Welcome to Microsoft Q&A platform and thanks for posting your question.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.

Ask: In my Azure environment, Under one subscription, there are 2 resource groups, In resource group A I have Azure SQL VM and it has tables I need, now I have to replicate these tables to another Azure SQL Server which is in Resource group B and I am trying to achieve this through ADF, But I am not able to establish the connection between my ADF and the Azure SQL VM through linked services. Can someone help me with this?

Solution: I was able to resolve the issue as the VM I was trying to connect did not have a SQL DB. When I tried connecting to the storage account the connection was established.

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.

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

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Couldn't establish connection between Azure SQL VM and ADF which are located in 2 Different resource groups.

1 answer

Your answer