On-premise SQL Server connection with Azure Data Factory Error

Question

A VM is connected to SQL Server using vpn and I have done port forwarding of the SQL Server ip with the windows 11 virtual machine VM1 ip.

SQL Server ip:1433 ====>> VM1 ip:1433

netsh interface portproxy 
      add v4tov4 listenaddress=VM1 ip listenport=1433 
          connectaddress=sql server ip connectport=1433

Another virtual machine VM2 in same subnet with VM1 after turning off the firewall and whitelisting the subnet ip with the port, the VM2 can connect with the database.

And I have created the data factory in azure with a private endpoint in same subnet.

The problem is when I try to connect VM1 from data factory with Azure runtime enabled and SQL Server authentication I cannot connect:

Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'vm1ip', Database: 'dbname', User: 'username'. 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

but if I expose the VM1 1433 port in internet the datafactory can make a connection.

enter image description here

Any solution for this?

Answer 1

Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'vm1ip', Database: 'dbname', User: 'username'. 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

As per this Ms Document below are the possible solutions to resolve the above error:

1. Install the Self-Hosted Integration Runtime in the Azure VM and whitelist the Machine’s IP address in Azure SQL VM inbound NSG rules and open Port 443 & 1433 to allow communication from Azure Data Factory to the SQL VM. Then you should be able to connect to SQL VM from Data Factory by choosing the Self-Hosted Integration Runtime in SQL Server linked service to connect.
2. Add the IP address of the self-hosted integration runtime machine to the list of allowed IP addresses.

It tested successfully after following above steps:

For more information you can refer this.