Unable To Connect On Prem SQL Server From Azure ADF

Question

Hi

We are trying to connect an on-prem sql server from our Azure ADF. There are firewalls involved. What are we missing . Please see the error :

"

Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'servertest.test.loc', Database: 'test_sirius', User: 'test\testdevsvc'. 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

"

Answer 1

Here are several steps to troubleshoot your issue:

• Ensure the configuration for the linked service is accurate. This includes verifying the server name, database name, and user credentials are correct.
• Review the firewall settings in your Azure SQL Database. To allow access from the Azure Integration Runtime, navigate to the "Firewalls and virtual networks" section in the Azure portal for your SQL database.
• Confirm that the Azure Integration Runtime can connect to the Azure SQL Database through a private endpoint. Use network diagnostic tools such as ping or traceroute to test the connectivity.
• If you are using AAD for authentication, verify that the user account in use has the appropriate permissions for the Azure SQL Database.
• Finally the Azure Integration Runtime logs for any additional error messages or details that might clarify the root cause of the problem.

https://learn.microsoft.com/en-us/azure/private-link/tutorial-private-endpoint-sql-portal

Answer 2

Hello Akshay Goel,

<In addition to Amira's answer>

Please try to connect to the SQL Server via the SHIR UI’s diagnostic tab and see if you can be able to reproduce the connectivity issue.

If the SHIR is behind a firewall, please work with your network admin so that it can communicate with the SQL Server.

A similar issue was discussed on the below stack overflow thread:https://stackoverflow.com/questions/72215532/adf-sql-server-untrusted-domain

Please let us know if you need any further help here.