With many organizations managing their business applications in hybrid, on-premises and cloud environments, they need to bridge the gap between these resources.
Microsoft 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.
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=53127. Then run the installation.
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.
Login to the portal and search for Active Directory.
Click on Active Directory and then under quick links, select 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.
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:
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.
Now the gateway is ready to use. It can be used by Azure Logic App, Power BI, PowerApps, Flow and Azure Analysis Service.
Step 4: Register 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.
Click on create, and registration is done. Now it should be available to use in the supporting Microsoft Azure Cloud 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.
Type request and select request/response from the connectors list.
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.
Copy the URL as you will need it to invoke the Logic app later. Now click on next step and select 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.
Unlike the request/response connector, which had one trigger associated with it, the SQL connector has only associated actions. 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.
Once the connection is established, all the tables under DemoDb will be auto populated. For now, select 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.
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.
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.
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.