IT Insights

Connecting On-Premises Apps via Logic App with On-Premises Data Gateway

Posted by Maheshkumar Tiwari on July 12, 2017

On Premises Gateway Logic AppWith many organizations managing their business applications in hybrid, on-premises and cloud environments, they need to bridge the gap between these resources.

Microsoft Azure Cloud Services has introduced a way to connect them with its On-Premises Data Gateway technology through Azure Logic Apps. This blog will describe the steps involved to easily connect this system to existing on-premise apps.

Example scenario

An organization has an Azure Logic App which accepts requests over http and inserts the data in an on premise based SQL Table. The following is needed to connect it via Microsoft On-Premises Data Gateway:

  • An Azure Subscription
  • An On-Premises database
  • The On-Premises Data Gateway registered to Azure and installed on the on-premises database

Below are the steps to create the solution:

Step 1: Create a database and table 

To keep it simple, create a demo database with three columns. 

 Gateway database and table

 

Step 2: Install, configure and start the gateway

Install the On-Premises Data Gateway by downloading it from https://www.microsoft.com/en-us/download/details.aspx?id=53127Then run the installation.

Downloading the Gateway 

Once you have completed the installation, you need to use a work account to register it. You can also add a user to an Active Directory to do so. Below are steps to add a user to the Active Directory, if you have a work account or another Azure account then you can skip this step.

GatewayImage3.jpg 

Login to the portal and search for Active Directory.

 Search for active directory

 Click on Active Directory and then under quick links, select add a user.

 Gateway add a user

Before adding a user, the user must have an email extension of onmicrosoft.com. Next, you must make the user a Global Administrator. Do this by going to all users, selecting the new user, clicking on directory role and selecting global administrator.

Gateway global administrator 

Now reset the password for the newly created user. You can then use this password to log into the portal, but you will be prompted to change it at the first login. Note, you will need the original password later, so keep it handy.

Next, add the user as the owner so the user shares the same subscription. Go to our subscription, then access control, then add the role as owner and select the user created above. In the notification, you should see following:

Gateway adding user as owner                    

Now to set the password, log into the Azure portal with the new user and you will be prompted to change the password.      

Step 3: Configure the gateway on the local database

You will need to enter the user you just created or the work account. Then provide the name to the gateway to be setup and the recovery key.

Gateway recovery key

Now the gateway is ready to use. It can be used by Azure Logic App, Power BI, PowerApps, Flow and Azure Analysis Service.

GatewayImage9.jpg 

 GatewayImage9.jpg

Step 4Register the gateway on the Azure portal

Associate the gateway to the new account by logging into the Azure portal with the new account and search for On-Premises Data Gateway.

Click on add to register the gateway you installed and provide the name, subscription and resource group. The installed gateway is already available in the drop-down list.

 Gateway registering

Click on create, and registration is done. Now it should be available to use in the supporting Azure services.

Step 5: Create the logic app

Open the Azure portal, sign in with your account and click new, web + mobile, Logic App. Then provide the name, create or use the existing resource group and location, and click on create. Then on the designer blade select the blank logic app template.

Now add a request/response trigger. Azure Logic apps will always start with trigger.

 Gatewayimage11.jpgGatewayimage11.jpg

Type request and select request/response from the connectors list.

 Gateway request selection

The request expects you to define the JSON schema for the request message intended to be received and the method. Once you save the configuration, the URL of the logic app will be created automatically.

Gateway define JSON schema 

Copy the URL as you will need it to invoke the Logic app later.  Now click on next step and select add an action.

Gateway add an action 

After receiving the request message, the next action is to insert it in the SQL, so look out for the SQL connector.

 GatewayImage SQL connector

Unlike the request/response connector, which had one trigger associated with it, the SQL connector has only associated actions. Select insert row.

 Gateway select insert row

As this is first time the SQL connector will be used, you will need to create a connection to the SQL, so select connect via on-premise data gateway. Then provide a connection name and details of the SQL server, database and authentication details.  

Gateway connection naming 

Once the connection is established, all the tables under DemoDb will be auto populated. For now, select product table.

Gateway product table 

All the columns from the table will become available with a blank text box against it to contain the value to be inserted.

Gateway insert value 

You now want to have dedicated values for the columns, and for that you need to explicitly let the logic app know where to pick these values from. For that you must switch to code view.

Use @{triggerBody()?} to get access to the JSON created in the previous step, then navigate through your structure to find the properties (Description, ID and Price).  

Then, going back to the designer view, click designer and see that the workflow has identified these values as coming from the request. Now the logic app is ready to accept requests and insert rows in table.

Step 6: Test the solution

To test the logic app, use a Postman, ARC or any other client. This example uses ARC.

Gateway test 

You can check the status of the trigger and logic app on the portal, as well as if the entry was made in the database. You might get the below error while configuring the SQL connector if the on-premises data gateway service is stopped or the machine hosting it isn't available over the network or is shutdown.

Gateway test status 

Following these series of steps will make it easy for an organization to benefit from their on-premises investments made on apps via Microsoft Azure Cloud platform, as well as give them a single method to manage all their applications. If your organization needs support for your Microsoft Azure Cloud platform or on-premises, Hybrid Integration, Emtec can help. Contact us today. 

Topics: .IT, cloud hosting, on-premises data gateway, logic apps

Search the Blog

Get IT Insights in Your Inbox

More Emtec Insights