Unable to connect the Azure SQL DB from ADF

Question

Hi,

I am trying to pull data from an on-prem SQL Server database into an Azure SQL database, a simple pipeline in ADF. I have installed Integration runtime on a on-prem VM which is configured and seems to be linked successfully to my data factory. I have added the public facing IP address of the VM to the firewall rules in Azure SQL Server and the 'Allow Azure services and resources to access this server' is enabled.

However, when I trigger the pipeline I keep on getting the following error which suggests that the target Azure SQL database cannot be accesses which doesn't make sense as the connection seems to be fine. Any help would be greatly appreciated

Operation on target SQL_to_SQLAzure failed: ErrorCode=SqlFailedToConnect,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Cannot connect to SQL Database: 'XXXXX', Database: 'XXXX', User: 'XXXX'. 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: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.),Source=.Net SqlClient Data Provider,SqlErrorNumber=10061,Class=20,ErrorCode=-2146232060,State=0,Errors=[{Class=20,Number=10061,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: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.),},],''Type=System.ComponentModel.Win32Exception,Message=No connection could be made because the target machine actively refused it,Source=,'

Answer 1

Resolved by opening port 1433 on the VM.

Answer 2

You can check following suggestion from MSFT in this thread: https://learn.microsoft.com/en-us/answers/questions/55061/cannot-connect-to-sql-database-adf-pipeline-gt-dat.html

Answer 3

Is the Test connection successful for Azure SQL DB in Linked service? Are you using Azure IR in that linked service?