{"id":986,"date":"2021-01-12T14:34:40","date_gmt":"2021-01-12T14:34:40","guid":{"rendered":"http:\/\/jackofalltradesmasterofsome.com\/blog\/?p=986"},"modified":"2021-01-12T14:54:33","modified_gmt":"2021-01-12T14:54:33","slug":"setting-up-azure-data-factory-integration-runtime-for-on-prem-connections","status":"publish","type":"post","link":"https:\/\/jackofalltradesmasterofsome.com\/blog\/2021\/01\/12\/setting-up-azure-data-factory-integration-runtime-for-on-prem-connections\/","title":{"rendered":"Setting up Azure Data Factory Integration Runtime for On-Prem Connections"},"content":{"rendered":"\n<p>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<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Set up On-Prem Integration Run Time<\/h2>\n\n\n\n<ol class=\"wp-block-list\"><li>Click on Author &amp; Monitor from Azure Data Factory<\/li><\/ol>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"564\" height=\"202\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-1.png\" alt=\"\" class=\"wp-image-988\" srcset=\"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-1.png 564w, https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-1-300x107.png 300w\" sizes=\"auto, (max-width: 564px) 100vw, 564px\" \/><\/figure>\n\n\n\n<ul class=\"wp-block-list\"><li>Create two new linked services<ul><li>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 -&gt; Integrated Runtimes -&gt; New<\/li><\/ul><\/li><\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"459\" height=\"332\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-2.png\" alt=\"\" class=\"wp-image-989\" srcset=\"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-2.png 459w, https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-2-300x217.png 300w\" sizes=\"auto, (max-width: 459px) 100vw, 459px\" \/><\/figure>\n\n\n\n<ul class=\"wp-block-list\"><li>Select Integration runtime setup followed by\nSelf-Hosted.<\/li><\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"414\" height=\"151\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-3.png\" alt=\"\" class=\"wp-image-990\" srcset=\"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-3.png 414w, https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-3-300x109.png 300w\" sizes=\"auto, (max-width: 414px) 100vw, 414px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"424\" height=\"390\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-4.png\" alt=\"\" class=\"wp-image-991\" srcset=\"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-4.png 424w, https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-4-300x276.png 300w\" sizes=\"auto, (max-width: 424px) 100vw, 424px\" \/><\/figure>\n\n\n\n<ul class=\"wp-block-list\"><li>Name the Runtime Setup and select \u201cNext\u201d<\/li><\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"496\" height=\"261\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-5.png\" alt=\"\" class=\"wp-image-992\" srcset=\"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-5.png 496w, https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-5-300x158.png 300w\" sizes=\"auto, (max-width: 496px) 100vw, 496px\" \/><\/figure>\n\n\n\n<ul class=\"wp-block-list\"><li>On the next step, Option 2: Manual Setup and download and install the integration runtime. Or download from <a href=\"https:\/\/www.microsoft.com\/en-us\/download\/details.aspx?id=39717\">here<\/a>.<\/li><\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"461\" height=\"362\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-7.png\" alt=\"\" class=\"wp-image-994\" srcset=\"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-7.png 461w, https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-7-300x236.png 300w\" sizes=\"auto, (max-width: 461px) 100vw, 461px\" \/><\/figure>\n\n\n\n<ul class=\"wp-block-list\"><li>Download and install the runtime. The IR should be on the same machine that your data is located.<\/li><\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"443\" height=\"191\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-8.png\" alt=\"\" class=\"wp-image-995\" srcset=\"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-8.png 443w, https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-8-300x129.png 300w\" sizes=\"auto, (max-width: 443px) 100vw, 443px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"351\" height=\"276\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-11.png\" alt=\"\" class=\"wp-image-998\" srcset=\"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-11.png 351w, https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-11-300x236.png 300w\" sizes=\"auto, (max-width: 351px) 100vw, 351px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"351\" height=\"275\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-12.png\" alt=\"\" class=\"wp-image-999\" srcset=\"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-12.png 351w, https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-12-300x235.png 300w\" sizes=\"auto, (max-width: 351px) 100vw, 351px\" \/><\/figure>\n\n\n\n<ul class=\"wp-block-list\"><li>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. <\/li><\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"382\" height=\"300\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-13.png\" alt=\"\" class=\"wp-image-1000\" srcset=\"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-13.png 382w, https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-13-300x236.png 300w\" sizes=\"auto, (max-width: 382px) 100vw, 382px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"388\" height=\"270\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-14.png\" alt=\"\" class=\"wp-image-1001\" srcset=\"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-14.png 388w, https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-14-300x209.png 300w\" sizes=\"auto, (max-width: 388px) 100vw, 388px\" \/><\/figure>\n\n\n\n<ul class=\"wp-block-list\"><li>Leave the name as is and check the box to \u201cEnable Remote Access from Intranet\u201d if you need it, but it is not necessary.<\/li><\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"447\" height=\"311\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-15.png\" alt=\"\" class=\"wp-image-1002\" srcset=\"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-15.png 447w, https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-15-300x209.png 300w\" sizes=\"auto, (max-width: 447px) 100vw, 447px\" \/><\/figure>\n\n\n\n<ul class=\"wp-block-list\"><li>If all is setup correctly you will get a confirmation message.<\/li><\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"448\" height=\"308\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-16.png\" alt=\"\" class=\"wp-image-1003\" srcset=\"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-16.png 448w, https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-16-300x206.png 300w\" sizes=\"auto, (max-width: 448px) 100vw, 448px\" \/><\/figure>\n\n\n\n<ol class=\"wp-block-list\"><li>If you navigate back to your Azure environment,\nyou will now see your new integrated self-hosted run time available. <\/li><\/ol>\n\n\n\n<h2 class=\"wp-block-heading\">Connecting to On-Prem SQL Server<\/h2>\n\n\n\n<ol class=\"wp-block-list\"><li>From Azure Data Factory, Select Linked Services -&gt; New, and you will now see an option for a SQL Server.<\/li><\/ol>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"441\" height=\"320\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-17.png\" alt=\"\" class=\"wp-image-1004\" srcset=\"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-17.png 441w, https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-17-300x218.png 300w\" sizes=\"auto, (max-width: 441px) 100vw, 441px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"379\" height=\"460\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-18.png\" alt=\"\" class=\"wp-image-1005\" srcset=\"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-18.png 379w, https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-18-247x300.png 247w\" sizes=\"auto, (max-width: 379px) 100vw, 379px\" \/><\/figure>\n\n\n\n<ul class=\"wp-block-list\"><li>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.<\/li><\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"331\" height=\"219\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-19.png\" alt=\"\" class=\"wp-image-1006\" srcset=\"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-19.png 331w, https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-19-300x198.png 300w\" sizes=\"auto, (max-width: 331px) 100vw, 331px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"372\" height=\"445\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-20.png\" alt=\"\" class=\"wp-image-1007\" srcset=\"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-20.png 372w, https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-20-251x300.png 251w\" sizes=\"auto, (max-width: 372px) 100vw, 372px\" \/><\/figure>\n\n\n\n<ul class=\"wp-block-list\"><li>Be sure to test the connection and hit create once completed.<\/li><\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"250\" height=\"101\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-21.png\" alt=\"\" class=\"wp-image-1008\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Setting up Security on Local Database for Integration Runtime<\/h2>\n\n\n\n<p>You will need to create a SQL Authenticated\nUser or service account for the connection to this database by Azure Data\nFactory.<\/p>\n\n\n\n<ol class=\"wp-block-list\"><li>Connect to your database and select Security -&gt; New -&gt; Login<\/li><\/ol>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"409\" height=\"170\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-22.png\" alt=\"\" class=\"wp-image-1009\" srcset=\"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-22.png 409w, https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-22-300x125.png 300w\" sizes=\"auto, (max-width: 409px) 100vw, 409px\" \/><\/figure>\n\n\n\n<ul class=\"wp-block-list\"><li>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.<\/li><\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"417\" height=\"377\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-23.png\" alt=\"\" class=\"wp-image-1010\" srcset=\"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-23.png 417w, https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2021\/01\/image-23-300x271.png 300w\" sizes=\"auto, (max-width: 417px) 100vw, 417px\" \/><\/figure>\n\n\n\n<p> Setting up Azure Data Factory Integration Runtime for On-Prem Connections <\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 &amp; Monitor from Azure Data Factory Create two new linked services Linked Service to Database. Since we are [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":823,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28],"tags":[44,103,84],"class_list":["post-986","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-azure","tag-azure","tag-integrated-run-time","tag-sql-server"],"_links":{"self":[{"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts\/986","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/comments?post=986"}],"version-history":[{"count":2,"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts\/986\/revisions"}],"predecessor-version":[{"id":1012,"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts\/986\/revisions\/1012"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/media\/823"}],"wp:attachment":[{"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/media?parent=986"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/categories?post=986"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/tags?post=986"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}