Use Azure Data Factory + Azure Integration Runtime to connect to on-premises SQL Server

0
votes

I have a SQL Server instance installed on a Virtual Machine, said VM is connected to an Azure VNet. My goal is to consecutively get data out of this SQL Server and store it in an Azure Data Lake.

Is it possible to use an Azure Data Factory + Azure Integration Runtime (NOT a self-hosted integration runtime!) to do this?

If I create a new Data Factory (including Private Endpoints so the factory gets an IP address of the VNet) and use the standard Integration Runtime "AutoResolveIntegrationRuntime", it's possible to specify a SQL server name and credentials, however, the connection test fails:

enter image description here

After installing a self-hosted integration runtime on the VM and using this IR, it appears to work:

enter image description here

What exactly is the integration runtime doing that wouldn't be possible without it? In the end, couldn't the Azure Integration Runtime (hosted on Azure) just open up an ODBC connection, especially since all resources are in the same network?

1
azureazure-data-factoryazure-integration-runtime

1 Answers

0
votes

We cann't use Azure IR to dispatch the following transform activities against compute resources in on-premises or Azure VNet.

Referencing this document. We can conclude that:

  1. An Azure IR can:
  • Run Data Flows in Azure
  • Run copy activity between cloud data stores
  • Dispatch the following transform activities in public network.
  1. A self-hosted IR is capable of:
  • Running copy activity between a cloud data stores and a data store in private network.
  • Dispatching the following transform activities against compute resources in on-premises or Azure Virtual Network

Update:
According to the chapter Integration runtime location, we can see the Azure IR is dynamically built.And Auto-resolve Azure IR is running in public network by default. I think it is the reason why you can't connect your sql server located in your Azure VNet via Azure IR.

We can see the sub type of Azure IR is public as follows: enter image description here