Connecting to On-Premises SQL Server in Azure Data Factory

I have created a pipeline in Azure data factory. I want to copy data from SQL server hosted in my local machine. In Azure we can connect to any cloud services but we cannot connect on-premises sources. To achieve this functionality follow the steps.

1. Install and configure an on-premises data gateway – An on-premises data gateway is required to connect on-premises data sources in Azure Analysis services.
1. Download gateway using link Gateway
2. Install and Run Setup
3. Select on premises gateway (recommended mode)



4. Select a folder to install, accept terms and click on install
5. Sign in to Azure. Use the same account as Azure Active Directory
6. Register gateway – To create gateway resource in Azure, we need to register the local instance installed
7. Select “Register a new gateway on this computer”
8. Enter a name and recovery key for gateway. By default, gateway uses azure active services default region, if you need to select different region then click on Change Region



9. After installing and registering a gateway, we need to restart the gateway. Click on Restart now



10. After restarting, now gateway is ready to use in Azure services. Now we need to Create gateway resource in Azure
2. Create an Azure gateway resource
1. Log into azure portal, click on create resource and search on premises data gateway. Click on Create



2. In next window enter required details like name, subscription, select gateway installed.



3. Click on Create and On premises gateway is created in Azure.
3. Connecting SQL server in Azure data factory
1. Open existed or new azure data factory and click on Author and Monitor
2. On the left side we have three menu options. Click on Author
3. Here we have Connections option at the bottom. Click on that and It will be open list existed linked services details and integration runtimes
4. Click on Integration runtimes tab. This will be connect SQL server installed on premises
5. Click on New and Select “Perform data movement and dispatch activities to external computes.” Click on Continue
6. Select “Self Hosted” and click on Continue
7. Enter the name and click on Create. This open a new window with two options I.e Express Setup and Manual Setup and with two authentication keys. Copy the keys and store
8. Select option 2 manual setup. Click on Download and install integration runtime
9. Integration runtime application will be downloaded. Click on Install
10. Enter name and any one of authentication key which we copied in above steps. Click on OK
11. After installation successful it will show in tab with the status as Running
12. Click on Linked Services tab. Click on New Connection and it will open a list of available data stores. Select SQL server and click on Continue

Make data easy with Helical Insight.
Helical Insight is world’s best open source business intelligence tool.

Grab The Free Trail

13. Enter the name. To connect SQL server installed in local machine here we need to select connect via integration runtime. Here select the integration runtime which we installed in above steps
14. Enter the database connection details like host name, database name, user credentials



15. Click on Test Connection and click on Create
16. SQL server will be created and it will be appear in the linked services tab

In case if you have any queries please get us at support@helicaltech.com

Thank You
Ramu Vudugula
BI Developer
Helical IT Solutions Pvt Ltd

Best Open Source Business Intelligence Software Helical Insight Here

A Business Intelligence Framework

Free
Download

Best Open Source Business Intelligence Software Helical Insight is Here

A Business Intelligence Framework

Free
Download