How To Connect On-Premise SQL Server From Microsoft Flow Using Azure On-Premise Data Gateway

In this article, we will learn how to connect on-premise SQL Server from Microsoft Flow using on-premise data gateway. This article will help you to understand how to use Azure on-premise gateway with the help example - connect on-premise SQL server from Microsoft flow with step by step.

Introduction

If you are a novice to the Azure Cloud and want to learn what a logic app is, then go through my previous articles.

  1. An Overview of Cloud And Azure Cloud
  2. Pillars of Cloud And Azure Services
  3. How To Store File In SFTP Using Azure Logic App

Before you go through this article, it is recommended to go through below parts of this section,

  1. Overview of Azure on-premise data gateway
  2. Install and configure Azure on-premise data gateway

In this article, we will learn how to connect on-premise SQL Server from Microsoft Flow using on-premise data gateway.

Scenario

This article will help you to understand how to use Azure on-premise gateway with the help example - connect on-premise SQL server from Microsoft Flow, step by step.

Pre-requisite

  • Basic knowledge Azure services
  • Basic knowledge of on-premise data gateway
  • Install and configure on-premise data gateway
  • Office 365 account with flow subscription enabled

Background

We have got an overview of Azure on-premise data gateway and how to install/configure the same in the last two parts.

In this article, we will be exploring how to use this gateway to insert a row in on-premise SQL server table from Microsoft flow.

Overview

Let’s ready and gear up to execute the steps.

Step 1

Ready with SQL server table with few columns like Id, Name, Flag.

How To Connect On-Premise SQL Server From Microsoft Flow Using Azure On-Premise Data Gateway 

Step 2

Check on-premise gateway is in online and ready to use.

How To Connect On-Premise SQL Server From Microsoft Flow Using Azure On-Premise Data Gateway 

Step 3

Open and login to Microsoft flow portal

Step 4

Create connections in Microsoft flow.

Click on connections option present under data category.

How To Connect On-Premise SQL Server From Microsoft Flow Using Azure On-Premise Data Gateway 

Step 5

Click on ‘Add connection’ option

How To Connect On-Premise SQL Server From Microsoft Flow Using Azure On-Premise Data Gateway 

Step 6

Search for ‘SQL server’ and click on Create Link.

How To Connect On-Premise SQL Server From Microsoft Flow Using Azure On-Premise Data Gateway 

Step 8

Select option connect using on-premise data gateway. Also enter, remaining parameters like SQL server name, database name, its authentication type, select gateway. Then, click on the Create button.

Note
System automatically populates a list of gateways which are present for your account.

How To Connect On-Premise SQL Server From Microsoft Flow Using Azure On-Premise Data Gateway 
 
How To Connect On-Premise SQL Server From Microsoft Flow Using Azure On-Premise Data Gateway

Step 9

Once your connection is ready then it shows as ready in Microsoft flow connection list.

How To Connect On-Premise SQL Server From Microsoft Flow Using Azure On-Premise Data Gateway 

Step 10

Till now, our SQL server, gateway and connection to the database from Microsoft Flow is ready. Now, we will focus on creating a flow to insert a row to this connected database.

Click on ‘My Flows’ and then click on ‘Create from blank’ option.

How To Connect On-Premise SQL Server From Microsoft Flow Using Azure On-Premise Data Gateway 

Step 11

Search for trigger ‘When HTTP request is received’.

Note
You can select trigger as per your need.
 
How To Connect On-Premise SQL Server From Microsoft Flow Using Azure On-Premise Data Gateway 

Step 12

Click on a new step.

How To Connect On-Premise SQL Server From Microsoft Flow Using Azure On-Premise Data Gateway 

Step 13

Search for SQL server action and then select action ‘Insert a row’.

How To Connect On-Premise SQL Server From Microsoft Flow Using Azure On-Premise Data Gateway 
 
How To Connect On-Premise SQL Server From Microsoft Flow Using Azure On-Premise Data Gateway

Step 14

Now, the system will automatically populate the list of tables. It means we have connected on-premise SQL server from Microsoft flow.

Select the appropriate table to insert a row.

How To Connect On-Premise SQL Server From Microsoft Flow Using Azure On-Premise Data Gateway 

Step 15

Once you select table then system populates all columns as a field in the table. Enter data in the text box to add it as a row.

How To Connect On-Premise SQL Server From Microsoft Flow Using Azure On-Premise Data Gateway 

Step 16

Click on save button to save flow

How To Connect On-Premise SQL Server From Microsoft Flow Using Azure On-Premise Data Gateway

 

Step 17

Flow is ready to use now. To test this flow, send an HTTP post request to flow.

How to get it HTTP request URL?

Go design of flow and copy ‘HTTP POST URL’,

How To Connect On-Premise SQL Server From Microsoft Flow Using Azure On-Premise Data Gateway 

Step 18

I am using postman tool to send a request to flow to run flow.

How To Connect On-Premise SQL Server From Microsoft Flow Using Azure On-Premise Data Gateway 

Step 19

Go to flow again and check history to verify flow successfully ran or not.

How To Connect On-Premise SQL Server From Microsoft Flow Using Azure On-Premise Data Gateway 
 
How To Connect On-Premise SQL Server From Microsoft Flow Using Azure On-Premise Data Gateway

Step 20

Now, verify record is created or not in on-premise SQL server table.

How To Connect On-Premise SQL Server From Microsoft Flow Using Azure On-Premise Data Gateway 

That's it. Our entire set-up is ready to use now.

Summary

In this article, we have learned how to use Azure on-premise gateway, how to create a connection in Microsoft flow and how to create Microsoft flow to connect to on-premise resources like SQL server from Azure cloud.