Looking for ADF to connect on-prem SQL server. Need help or suggestions

In order to access your On prem database from ADF, you would need to use a Self hosted IR.
For that you need to have a server within your on prem network which is able to communicate with the On prem SQL server and install the Integration run time within that server which would act as a bridge between the On Prem database and ADF.

https://learn.microsoft.com/en-us/azure/data-factory/create-self-hosted-integration-runtime?tabs=data-factory

Assuming you just want to read the data from the on Prem SQL database, you can either create a SQL account with just read only access on the On Prem database and leverage SQL authentication or use a service account with read only access on the database and use windows auth (no need of admin/owner access)

For Azure Data Factory to connect to an on-premises SQL Server for migration, the following areas must be in place: network connectivity, SQL Server configuration, firewall rules, and correct linked service configuration.

1. Network and runtime prerequisites

• Use either:
  • A self-hosted Integration Runtime (SHIR) installed on a machine that can reach the on-prem SQL Server, or
  • A Managed VNet Data Factory with private connectivity to on-prem (for example, via VPN/ExpressRoute and private endpoint) as shown in the managed VNet tutorial.
• Ensure the network path from the runtime host to SQL Server works (ping or name resolution, and port reachability).

2. SQL Server configuration on-premises Follow the standard remote-connectivity requirements:
3. In SQL Server Management Studio, on the on-prem instance:
   • Right-click the server → Properties → Connections → enable Allow remote connections to this server.
4. In SQL Server Configuration Manager:
   • Under SQL Server Network Configuration → Protocols for <instance>:
     • Enable TCP/IP.
5. In TCP/IP Properties:
   • On the IP Addresses tab, in IPAll, confirm the TCP Port (default 1433). Note this port.
6. On the Windows Firewall of the SQL Server machine:
   • Create an inbound rule to allow TCP traffic on that SQL port.
7. Verify connectivity from another machine (ideally the SHIR host or a test machine on the same network) using SSMS with the FQDN and port, for example:
   • server.domain.corp.company.com,1433

If these are not configured, the error A network-related or instance-specific error occurred while establishing a connection to SQL Server and Server was not found or was not accessible is expected.

3. Data Factory with Managed VNet / private endpoint (if used) If using Managed VNet and private endpoint to reach on-prem SQL Server:
4. Ensure prerequisites are met:
   • Azure subscription.
   • Virtual Network.
   • Connectivity between the VNet and on-prem network (VPN or ExpressRoute) or a private link setup.
   • Data Factory with Managed VNet enabled.
5. In the tutorial scenario, when using a NAT VM and multiple SQL Servers/ports, explicitly include the port in the linked service server name (for example, server.domain.corp.company.com,1433). If the port is not specified, connections can time out.
6. For troubleshooting, from the backend server VM, confirm:
   • telnet <FQDN> 1433 succeeds.
7. Linked service configuration in ADF
8. In the ADF UI:
   • Go to Manage → Linked services → + New → SQL Server.
   • Enable Interactive Authoring if using Managed VNet.
   • Enter the FQDN of the on-prem SQL Server and, if needed, append the port (for example, servername.domain.com,1433).
   • Provide the database name (optional) and credentials.
9. Test the connection from the linked service blade.
   • If there are multiple SQL Servers or custom ports, ensure the correct port is explicitly specified; otherwise, the connection can time out.
10. Authentication and permissions

• The error shown (SqlFailedToConnect with network-related error) indicates a connectivity issue, not a permission issue.
• For connectivity testing and migration, a SQL login with sufficient rights on the source database is required (for example, db_owner on the databases to be migrated). Using sa is technically sufficient but not required; a dedicated SQL login with appropriate rights is recommended.
• Domain vs local account:
  • For SQL authentication (like sa), domain membership is not required.
  • Domain accounts are relevant if using Windows authentication from SSIS or Azure-SSIS IR joined to a VNet. In that case, ensure:
    • The Azure-SSIS IR is joined to a VNet that can reach the on-prem SQL Server.
    • TCP/IP is enabled and firewall allows access.

6. Specific checks for the current error Given the error message:

• Confirm the SQL Server instance is running and listening on the expected port.
• Confirm TCP/IP is enabled on the instance.
• Confirm the firewall on the SQL Server host allows inbound TCP on that port.
• Confirm the SHIR host or Managed VNet environment can resolve the server name and reach the port (telnet test).
• Confirm the linked service uses the correct server name and port.

Once these are correct, the SqlFailedToConnect and network-related errors should be resolved, and authentication/permissions can then be addressed if any login failures appear.

References:

• Copy and transform data to and from SQL Server by using Azure Data Factory or Azure Synapse Analytics
• Tutorial: How to access on-premises SQL Server from Data Factory Managed Virtual Network using Private Endpoint
• Access data stores and file shares with Windows authentication from SSIS packages in Azure