1

I need to update the table which is in the on-premises sql server database using dataflow in azure data factory

  1. I have self hosted Integration Runtime
  2. Linked services created based on this self hosted Runtime
  3. Dataset connection works fine

However when I use the same dataset in dataflow it gives me this error -

The TCP/IP connection to the host has failed. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.

If I need to modify the self hosted Integration runtime to auto-resolve Azure hosted for connecting an on-premises sql server - what are the perquisite for this for the on-premises sql server database perspective (as I tried to use autoresolve azure hosted runtime it gives me an error)? Also please let me know if anything needs to be modified in Azure.

David Makogon
  • 69,407
  • 21
  • 141
  • 189
yatish mehta
  • 11
  • 2
  • 1
    This doesn't get at what you are asking, but you cannot use dataflows with self-hosted IR as noted here: https://learn.microsoft.com/en-us/azure/data-factory/concepts-integration-runtime#integration-runtime-types. So I would start with using the Azure IR and then see what connection issues arise from there – Mark Wojciechowicz Jul 21 '22 at 19:41
  • Have you referred this [tutorial](https://learn.microsoft.com/en-us/azure/data-factory/tutorial-managed-virtual-network-on-premise-sql-server) that explains how to access on-prem SQL via Azure Data Factory? – Anand Sowmithiran Jul 22 '22 at 07:52

2 Answers2

0
  1. Open SQL Server Configuration Manager, and then expand SQL Server Network Configuration which you are trying to connect with Self-hosted IR.
  2. Click Protocols for InstanceName, and then make sure TCP/IP is enabled in the right panel and double-click TCP/IP.
  3. On the Protocol tab, notice the value of the Listen All item.
  4. Click the IP Addresses tab: If the value of Listen All is yes, the TCP/IP port number for this instance of SQL Server is the value of the TCP Dynamic Ports item under IPAll. If the value of Listen All is no, the TCP/IP port number for this instance of SQL Server is the value of the TCP Dynamic Ports item for a specific IP address.
  5. Make sure the TCP Port is 443.
  6. Click OK.

Note: Make sure to restart the server after above configurations.

Refer: Firewall requirements for on-premises/private network

Utkarsh Pal
  • 4,079
  • 1
  • 5
  • 14
0

Thank you all for taking out time and answering my queries.

I took below approach to resolve the issue with data flows and on premises self hosted limitation.

  1. Created the staging tables in on cloud db.
  2. Using pipeline copy data activity (self-hosted IR) copied data from on premises to on cloud staging tables.
  3. Using the on cloud table in data flows for lookup and perform few transformation.
  4. Modified the on cloud tables in data flows (Azure IR)
  5. Added more one copy activity in pipeline to copy on cloud (modified data) to on premises.

Thanks, hope this helps if anyone is facing similar issues.

yatish mehta
  • 11
  • 2