How to connect to On premises SQL server which is on Linux VM from Azure Data Factory.

Shiva Kumar 20 Reputation points
2023-09-15T13:49:32.6+00:00

I was trying to connect to an on premises SQL server which is on Linux VM from Azure Data Factory. The Documentation is saying to install self hosted Integration run time and connect using it but self hosted IR cannot be installed on Linux VM. How to connect to On Premises SQL server without any self hosted IR ?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,290 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,103 questions
{count} votes

Accepted answer
  1. Amira Bedhiafi 19,616 Reputation points
    2023-09-15T16:13:58.1633333+00:00

    It is a little bit tricky what you are trying to do but let's try to find a workaround :

    W1 : You may need to set up a VPN between your on-premises network and Azure can create a secure connection. Once the VPN is set up, Azure resources can directly communicate with resources in your on-premises network.

    • Deploy an Azure VPN Gateway in the Virtual Network.
    • Set up a Site-to-Site (S2S) VPN connection between Azure and your on-premises network.
    • Once the VPN is established, Azure Data Factory can communicate with your on-premises SQL Server as though it's part of your local network.

    **W2 :**If setting up a VPN is not feasible, you can use a jump server or a bastion host:

    • Deploy a Windows VM in your on-premises network.
    • Install Self-Hosted IR on this Windows VM.
    • Make sure that this Windows VM can communicate with the SQL Server on Linux VM.
    • Use this Self-Hosted IR to connect Azure Data Factory to your SQL Server.

    W3 : Azure Hybrid Connections can be another way to solve this problem. It's a feature of Azure Relay and doesn't require VPN or opening firewall ports.

    • Set up an Azure Hybrid Connection.
    • Install the Hybrid Connection Manager on a Windows VM in your on-premises network.
    • Use this connection to link Azure Data Factory and the SQL Server.

    W4: (I don't recommend this, especially in PROD environments) try to expose your SQL Server to the internet and connect directly from Azure Data Factory. This approach is risky and should only be used temporarily or in testing. Ensure you have strong security measures in place, such as firewall rules, encryption, and authentication.


1 additional answer

Sort by: Most helpful
  1. Bruce (SqlWork.com) 61,026 Reputation points
    2023-09-15T16:33:56.2633333+00:00

    all the above options beyond a VPN which requires a VPN device require you host a windows server for the gateway. so your simplest option is install windows server (could be a VM) to host the IR. the windows server will need network access to the sqlserver.

    0 comments No comments