connect ADF to SQL DB

Question

I have multiple VNETs, one for databases, one for application and others. I have configured NSG that allows traffic to flow between VNETS. I have spun a SQL server instance on Azure VM in database VNET. I spun ADF on my core VNET, created a linked server in ADF to connect to my SQL instance and I get the error 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.

Answer 1

Hi Ranga Sarvabhouman,
The connectivity error occurs because Azure Data Factory (ADF) using the default Azure Integration Runtime cannot reach SQL Server on a VM in a private VNET/subnet, even with VNET peering and NSG rules allowing port 1433 traffic between VNETs. Standard Azure IR operates from the public internet and lacks access to private VNET resources like your database VM.​

Required ADF Configuration:

Deploy a Managed Virtual Network (VNET) integration runtime in ADF or install a Self-Hosted Integration Runtime (SHIR) on a VM within the core VNET (or peered database VNET). Select this IR when creating/testing the SQL linked service.​

SQL Server VM Setup:

Enable TCP/IP protocol in SQL Server Configuration Manager, allow remote connections via SQL Server properties, and restart the SQL service. Add a Windows Firewall inbound rule for TCP port 1433 (or your dynamic port).​

:Network Verification Steps

Confirm VNET peering is bidirectional and effective (check Connected status).

Validate NSG rules on database VM/subnet: Allow inbound TCP 1433 from core VNET address space (source: core VNET range, destination: VM private IP or subnet).

Test connectivity: From a VM in core VNET, use telnet <sql-vm-private-ip> 1433 or SQL Server Management Studio with private IP.​

Use FQDN or private IP in ADF linked service; avoid public IP/FQDN

Answer 2

To resolve the error you are encountering while connecting Azure Data Factory (ADF) to your SQL Server instance, you can follow these troubleshooting steps:

1. Verify the Server Name: Ensure that the server name in your ADF linked service is correct. It should match the name of your SQL Server instance exactly. If you are using a named instance, the format should be <computer name>\<instance name>.
2. Check SQL Server Configuration: Make sure that your SQL Server is configured to allow remote connections. You can verify this in SQL Server Management Studio under the server properties.
3. SQL Server Browser Service: If you are using a named instance, ensure that the SQL Server Browser service is running. This service helps in resolving the named instance to the correct port.
4. Network Security Group (NSG) Rules: Double-check your NSG rules to ensure that they allow traffic from ADF to the SQL Server instance. Make sure that the appropriate ports (default is 1433 for SQL Server) are open.
5. Firewall Settings: If there are any firewalls between ADF and the SQL Server, ensure that they are configured to allow traffic on the necessary ports.
6. Connection String: Review the connection string used in ADF to ensure it is formatted correctly. You can refer to examples of SQL Server connection strings for guidance.

By following these steps, you should be able to diagnose and resolve the connectivity issue between Azure Data Factory and your SQL Server instance.

---

References:

• A network-related or instance-specific error occurred while establishing a connection to SQL Server