Azure Data Factory connecting to Azure SQL returns SqlFailedToConnect.

Desmond Fernando 26 Reputation points
2022-08-12T22:11:18.637+00:00

I have deployed Azure Data Factory (ADF) via Azure Pipeline with the help of ARM Deployment. In the deployment I have overwritten the connection string component for DB Connection string.

When the Live Mode is selected and Linked Service is tested for system assigned managed identity it connects to the Azure SQL server. I have created the user in master and database, and db_owner role.

230891-screenshot-2022-08-13-055701.jpg

However, when the trigger is run for the pipeline it throws bellow error.

Operation on target Copy data1 failed: ErrorCode=SqlFailedToConnect,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Cannot connect to SQL Database: 'db_name', Database: 'dn_name', User: ''. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=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),Source=.Net SqlClient Data Provider,SqlErrorNumber=53,Class=20,ErrorCode=-2146232060,State=0,Errors=[{Class=20,Number=53,State=0,Message=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),},],''Type=System.ComponentModel.Win32Exception,Message=The network path was not found,Source=,'

I have already allowed Azure services and resources to access the server in Azure SQL Server. Image attached below.

230901-image.png

Azure SQL Database
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,526 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alberto Morillo 32,886 Reputation points MVP
    2022-08-12T22:26:52.333+00:00

    Please make sure the IP address of the Integration Runtime is whitelisted in your AzureSQLServer.

    Please make sure you have opened all the ports listed here.

  2. ShaikMaheer-MSFT 37,896 Reputation points Microsoft Employee
    2022-08-16T09:28:29.027+00:00

    Hi @Desmond Fernando ,

    Kindly recheck below all checkpoints once again and see if that helps.

    • Is your Azure SQL Server is running?
    • Is the linked service and datasets details are published to Live mode?
    • Triggers will run the pipeline version which is published to live mode. Hence kindly make sure everything published successfully or not.
    • List item

    I could see you selected Selected networks option. That means only whitelisted IP addresses alone can access your server. Kindly try to allow public network access enabled and see if that helps?
    231478-image.png

    Please note, Auto Resolve IRs will get IP addresses randomly as they pick resources based on multiple factors of availability and loan on datacenters. Hence allowing public access may help.

    In your case it says server itself is not accessible. I believe this is because of not allowing public access and allowing only few selected networks.

    Kindly let us know how it goes.