Connecting to On-Premises SQL Server Using Azure Integration Runtime


In the world of cloud computing, seamless integration between on-premises systems and cloud services is crucial for modern businesses. Azure Integration Runtime is a powerful tool offered by Microsoft Azure that facilitates secure and efficient connectivity between on-premises data sources, like SQL Server, and Azure services. In this blog, we will explore how to set up and use Azure Integration Runtime to establish a connection with an on-premises SQL Server, complete with step-by-step instructions and screenshots.


– Azure subscription

– On-premises SQL Server instance

– Azure Data Factory instance

Step 1: Set Up Azure Integration Runtime:

1. Log in to your Azure portal and navigate to your Azure Data Factory instance.

2. In the left-hand menu, select “Author & Monitor” to access the Azure Data Factory user interface.

3. Click on “Author & Monitor” to launch the Data Factory UI.

4. In the Data Factory UI, navigate to the “Connections” tab and click on “+ New” to create a new Integration Runtime.

No alt text provided for this image

Step 2: Configure Integration Runtime:

1. Choose the integration runtime type. For connecting to on-premises systems, select “Self-hosted.”

2. Provide a name for the Integration Runtime and a description if desired.

3. Click on ‘Express Setup’ and open the setup on the machine where your SQL Server resides. This machine will act as a gateway between your on-premises resources and Azure services.

4. Once registered, test the connection to ensure the Integration Runtime is functioning correctly.

No alt text provided for this image

No alt text provided for this image

Step 3: Create Linked Service:

1. In the Data Factory UI, navigate to the “Author” section.

2. Click on “+ New” and select “Linked Service” to create a new connection to your on-premises SQL Server.

3. Choose the “On-premises SQL Server” option and configure the linked service settings. This includes specifying the server’s name, database name, authentication method, and credentials.

4. Test the connection to ensure it’s successful.

No alt text provided for this image
No alt text provided for this image

Step 4: Create Data Pipeline:

1. In the Data Factory UI, navigate to the “Author” section.

2. Click on “+ New” and select “Pipeline” to create a new data pipeline.

3. Add source and sink activities to the pipeline. Specify the linked service you created in the source and destination settings.

4. Configure data transformations, if necessary.

No alt text provided for this image

Step 5: Debug and Monitor:

1. After creating the data pipeline, you can debug and monitor its execution using the “Debug” and “Monitor” options in the Data Factory UI.

2. You can view detailed logs and monitor the data movement and transformations.

No alt text provided for this image


Azure Integration Runtime simplifies the process of connecting to on-premises data sources from Azure services. With its self-hosted capability, it enables secure and efficient data movement and transformation. By following the steps outlined in this blog, you can seamlessly integrate your on-premises SQL Server with Azure services, unlocking powerful data integration possibilities for your business.

Addend Analytics is a Microsoft Gold Partner based in Mumbai, India, and a branch office in the U.S.

Addend has successfully implemented 100+ Microsoft Power BI and Business Central projects for 100+ clients across sectors like Financial Services, Banking, Insurance, Retail, Sales, Manufacturing, Real estate, Logistics, and Healthcare in countries like the US, Europe, Switzerland, and Australia.

Get a free consultation now by emailing us or contacting us.