Connect to On-premises Data in Azure Data Factory with the Self-hosted Integration Runtime - Part 1

By:   |   Updated: 2018-12-10   |   Comments (9)   |   Related: More > Azure


I have created a pipeline in Azure Data Factory. I want to copy data from my SQL Server instance hosted on my local server. I can connect easily to cloud sources, but not to my on-premises sources. How can I achieve this?


Azure Data Factory is a scalable data integration service in the Azure cloud. In Azure Data Factory, you can create pipelines (which on a high-level can be compared with SSIS control flows). In a pipeline, you can put several activities, such as copy data to blob storage, executing a web task, executing a SSIS package and so on. A common scenario is to copy data from local sources to the cloud. In this case, Azure Data Factory needs to be able to connect to those sources. This can be achieved by using the self-hosted integration runtime.

The self-hosted integration runtime is a service running in Azure Data Factory, but you can add local compute nodes on local servers in your on-premises network. A connection is created between the nodes and the integration runtime within your Azure Data Factory (ADF) in Azure. Through this connection, ADF can reach your local data and copy it securely to the cloud. This set-up is very similar to the Power BI on-premises gateway. In fact, the self-hosted integration runtime used to be called the "data management gateway".

An alternative to using the self-hosted integration runtime (IR), is to use an on-premises ETL tool - like SSIS - to push the data to the cloud. However, this means you need a SQL Server license and a server. The self-hosted IR service is free to use and can be run on any local server. If you want to migrate your data infrastructure to the cloud, Azure Data Factory (and other Azure tools like Logic Apps) are a better choice.

It's important to note that the self-hosted IR will grant ADF access to your on-premises sources, but not the Azure-SSIS IR. If you want SSIS packages to connect to your local environment, you need to join the Azure-SSIS IR to a virtual network.

Setting Up the Self-Hosted Integration Runtime

The first step is setting up the self-hosted IR in ADF. Then we install a node (or multiple nodes) on a local server and connect the node with the integration runtime.

In ADF, go to the edit pane, then to connections (at the bottom) and finally to the Integration Runtimes. There, click on the New button to create a new IR.

create new IR

In the wizard, you get two options for the integration runtimes: one for external computes and the Azure-SSIS IR. We're going to pick the first one. For setting up an Azure-SSIS IR, check out the tip Configure an Azure SQL Server Integration Services Integration Runtime.

pick the correct IR type

You can either create an IR in Azure or a self-hosted IR. We're going to choose the second option.

choose self-hosted IR

Next, we're going to provide a name and a description.

provide a name and description

To configure the self-hosted IR node on your local machine, you can choose between an express setup or a manual install.

express or manual setup

However, I got the following error when I tried to download the express setup:

download error

Manual installation and configuration it is then. You can download the ADF IR from the Microsoft site:

download manual installs

After downloading, run the setup on the server that will host your self-hosted IR node. Ideally, this server is close to your data. There's an easy to follow setup wizard:

When the setup is finished, you can configure the node. First, we need to register it to the IR we created in ADF. Copy one of the authentication keys which you can find in the ADF IR setup:

copy authentication key

Paste the key into the local IR config menu and click Register:

register IR

Next you can specify a name for your local node of the self-hosted IR.

specify IR node name

If necessary, you can configure remote access as well:

configure remote access

When everything went successfully, you'll get a message stating the local node has been registered successfully to the self-hosted IR.

After the setup, the configuration manager of the node will be shown. Here you can consult the status of the service and see if it is connected to the cloud service.

node configuration manager

When you go back to the connections in ADF, you can see the self-hosted IR among the other IRs. It's possible the status is unavailable, while the node configuration manager says the connection is successful. In most cases, you simply need to refresh the screen.

IR statuses

You can click on the monitor icon to get more detail about your self-hosted IR:

monitoring the IR

Creating a connection to a local database

Before we can pull data from our on-premises server, we need to create a linked service to the database. In the connection pane, go to Linked Services and click on New.

create new linked service

In the wizard, choose SQL Server as the data store type.

Click Continue to go to the configuration screen of the linked service. There you'll need to specify a name, server name, database name and connection credentials. Don't forget to select the self-hosted IR from the dropdown.

configure linked service

At the bottom, you can test your connection. Click Finish to create the linked service.

test connection


In the first part of this tip, we've configured a self-hosted integration runtime in Azure Data Factory. Then we added a note to this IR on our local server. We created a linked service in ADF to our local instance of SQL Server and the connection was successful. In the next part of this tip, we'll see how we can use this setup to copy data from the SQL Server database to Azure Blob Storage.

Next Steps

Last Updated: 2018-12-10

get scripts

next tip button

About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips
Related Resources

Comments For This Article

Thursday, July 09, 2020 - 3:39:52 PM - Koen Verbeeck Back To Top (86114)


I haven't tried it out myself, so I don't know the answer. So I would say: just try it :) (in a dev env of course :)


Wednesday, July 01, 2020 - 12:39:39 PM - FIIS Back To Top (86077)

First of all thank you for all your posts, i do check your posts and thank you so much for it.

What i want to know is lets say for any reason the SHIR is in failure state or Running Limited state, can we then delete the existing one and create a new SHIR with the same name and link all the linked services to this IR, will it work ? or will it have issues?

Thursday, October 31, 2019 - 8:14:54 AM - Koen Verbeeck Back To Top (82942)

Hi Mohak,

currently there are no alternatives for the self-hosted IR: it's only availabe on Windows. I have no idea if there are any plans to change this.


Wednesday, October 30, 2019 - 8:29:29 AM - Mohak Saxena Back To Top (82933)

We have created self hosted IR using a Windows based on-prem server. My questions is that in today's era of open source and doing away dependencies is there no alternate of using a Linux based server for IR for Azure SQL Warehouse and ADF?

If yes what are the alternatives?

If no, does Microsoft has plans to do this?

Tuesday, October 22, 2019 - 7:33:43 AM - Koen Verbeeck Back To Top (82856)

Hi Richard,

the IR provides a compute environment for your SSIS packages. Think of it as a virtual machine where your packages will run. All other stuff, e.g. designing and configuring your packages is just like before. You create your SSIS packages in Visual Studio, you deploy them to the SSISDB catalog where you also configure them with environments. You can access the catalog with SSMS.


Monday, October 21, 2019 - 1:15:40 PM - Richard Back To Top (82848)

Hi Koen,

I would like to use the Self Hosted IR in conjunction with SSIS in Azure but am stuck after setting up the IR. 

The IR was setup succesfully with help of your article. How, if possible, can I configure a connection manager, in my SSIS package?

All tips and comments are greatly appreciated.

Regards, Richard

Monday, October 21, 2019 - 10:51:12 AM - Sid Back To Top (82845)

Can we install self-hosted integration runtime on a Windows VM sitting inside a AWS or Azure itself?

Thank you

Friday, March 08, 2019 - 9:25:50 AM - Koen Verbeeck Back To Top (79217)

Hi Anne,

you'll probably want to check your firewall settings. It seems the node cannot connec to the endpoint in the cloud.


Thursday, March 07, 2019 - 5:37:01 PM - anne Back To Top (79206)

Thanks for the article!

I tried to register the integration runtime on my local computer, it gives an error:

The integration runtime (self-hosted) node has encoutered an error during registration.

The integration runtime node failed to connect to the cloud service dure to network connectivty issues. failed to connect to

What shoudl I do then?



Recommended Reading

Adding Users to Azure SQL Databases

Transfer Files from SharePoint To Blob Storage with Azure Logic Apps

Continuous database deployments with Azure DevOps

Process Blob Files Automatically using an Azure Function with Blob Trigger

Using Azure Blueprints to deploy Azure SQL Server and Database with Key Vault Secrets

get free sql tips
agree to terms

Learn more about SQL Server tools