If you need to connect your Azure Data Factory to an on-premise SQL server you will need help Setting up Azure Data Factory Integration Runtime for On-Prem Connections
Set up On-Prem Integration Run Time
- Click on Author & Monitor from Azure Data Factory
- Create two new linked services
- Linked Service to Database. Since we are using a local on-prem SQL server we will need to create a integrated run time. Click on Manage -> Integrated Runtimes -> New
- Select Integration runtime setup followed by Self-Hosted.
- Name the Runtime Setup and select “Next”
- On the next step, Option 2: Manual Setup and download and install the integration runtime. Or download from here.
- Download and install the runtime. The IR should be on the same machine that your data is located.
- One the installation is done you will be prompted with the Configuration Manager (Self-Hosted). Enter the keys from your Azure Setup and hit register.
- Leave the name as is and check the box to “Enable Remote Access from Intranet” if you need it, but it is not necessary.
- If all is setup correctly you will get a confirmation message.
- If you navigate back to your Azure environment, you will now see your new integrated self-hosted run time available.
Connecting to On-Prem SQL Server
- From Azure Data Factory, Select Linked Services -> New, and you will now see an option for a SQL Server.
- Give the connection a name and fill in the rest of the details to line up to your local SQL Server and set to the new integrated run time. If you have not created a SQL Authentication ID before, see the steps at the bottom of this tutorial for details.
- Be sure to test the connection and hit create once completed.
Setting up Security on Local Database for Integration Runtime
You will need to create a SQL Authenticated User or service account for the connection to this database by Azure Data Factory.
- Connect to your database and select Security -> New -> Login
- Create a new Login name, set to SQL Server Authenticaion, set a password and on the User Mappings Roles, Grant Access to AdventureWorksDB as Owner.
Setting up Azure Data Factory Integration Runtime for On-Prem Connections