Connect to On-Prem SQL server in ADF using Azure Relay

Question

Hi, I want to connect to on-prem sql server and run a SQL view to fetch data in ADF. I am looking for options how we can achieve this using azure relay? Need a detailed steps and answer for this.

Answer 1

What do you need before ?

• An Azure subscription.
• An on-premises SQL Server.
• Azure Data Factory instance.
• Azure Relay namespace.

Step 1: Set up Azure Relay

1. In the Azure portal, navigate to Azure Relay and create a new namespace. Follow the on-screen instructions to set it up.
2. Within your Azure Relay namespace, go to "Hybrid Connections" and create a new connection. Note the connection string, as you'll need it for the client and listener configuration.

Step 2: Configure the On-premises SQL Server for Azure Relay

1. Install Relay Hybrid Connection Manager on the machine hosting your SQL Server.
2. Open the Hybrid Connection Manager and add a new Hybrid Connection using the connection string you obtained during the Hybrid Connection setup in Azure Relay. This links your on-premises SQL Server to the Azure Relay.

Step 3: Set up Azure Data Factory

1. In ADF, navigate to "Manage" > "Integration runtimes" > "New". Choose "Self-hosted" and follow the instructions. The self-hosted IR acts as a bridge between your on-premises data sources and ADF.
2. Install the Self-hosted Integration Runtime on an on-premises machine that can communicate with your SQL Server. This machine will use the Azure Relay to securely access your on-premises SQL Server.
3. After installation, register the IR with ADF using the key provided during the IR setup in ADF. Ensure the IR machine has network access to your SQL Server.

Step 4: Create a Linked Service to SQL Server in ADF

1. In ADF, navigate to "Manage" > "Linked services" > "New".
2. Select "SQL Server" as the data store and use the self-hosted IR for connectivity.
3. Provide the necessary connection details for your on-premises SQL Server, such as the server name, database name, authentication type, username, and password.

Step 5: Create and Run a Pipeline

1. Create a new pipeline in ADF and drag a "Copy Data" activity onto the canvas.
2. Configure the source to use the SQL Server linked service you created. Specify the SQL query or table you want to use.
3. Configure the sink (destination) where you want to store the data, such as Azure Blob Storage, Azure SQL Database, etc.
4. Debug and trigger the pipeline to start the data transfer process from your on-premises SQL Server to Azure through Azure Relay.

See old threads :

https://learn.microsoft.com/en-us/answers/questions/1413305/how-to-access-on-premise-sql-server-using-azure-re

https://learn.microsoft.com/en-us/azure/azure-functions/functions-hybrid-powershell

https://learn.microsoft.com/en-us/azure/logic-apps/logic-apps-gateway-connection

https://learn.microsoft.com/en-us/azure/logic-apps/logic-apps-azure-functions?tabs=consumption

Answer 2

Hello @Santhi Dhanuskodi

To connect to an on-premises SQL Server and run a SQL view to fetch data in Azure Data Factory, you can use the Self-hosted Integration Runtime (IR) feature of Azure Data Factory.

The Self-hosted IR allows you to create a data integration pipeline that can access data on-premises, behind a firewall, or in a virtual private network (VPN).

Azure Relay is not required for this scenario. Instead, you can follow these steps:

1. Install and configure the Self-hosted IR on a machine in your on-premises network. You can download the Self-hosted IR from the Azure portal.
2. Create a linked service in Azure Data Factory that points to your on-premises SQL Server. You will need to provide the server's name, database name, and authentication information.
3. Create a dataset in Azure Data Factory that references the SQL view you want to fetch data from. You will need to provide the view name and the linked service you created in step 2.
4. Create a pipeline in Azure Data Factory that uses the dataset you created in step 3 as the source. You can then use other activities in the pipeline to transform the data or write it to a destination. Here is a high-level overview of the steps involved:
   1. Install and configure the Self-hosted IR on a machine in your on-premises network.
   2. Create a linked service in Azure Data Factory that points to your on-premises SQL Server.
   3. Create a dataset in Azure Data Factory that references the SQL view you want to fetch data from.
   4. Create a pipeline in Azure Data Factory that uses the dataset you created in step 3 as the source. For more detailed steps and information, you can refer to the following Microsoft documentation:

• Use a self-hosted integration runtime to copy data from on-premises SQL Server to Azure
• Create a self-hosted integration runtime

I hope this helps! Let me know if you have any further questions.