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

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.

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

In this article, we will learn how to connect on-premise SQL Server from Microsoft Flow using an 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 of 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 an 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.

SQL server table

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

On-premise gateway

Step 3. Open and log in to the Microsoft Flow portal

Step 4. Create connections in Microsoft Flow.

Click on the connections option present under the data category.

Microsoft Flow

Step 5. Click on the ‘Add connection’ option.

Add connection

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

SQL server

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

Note. The system automatically populates a list of gateways that are present for your account.

System automatically populates

Choose Gateway

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

Microsoft flow connection list

Step 9. Till now, our SQL server, gateway, and connection to the database from Microsoft Flow are 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 the ‘Create from blank’ option.

Connected database

Step 10. Search for trigger ‘When HTTP request is received’.

Note. You can select a trigger as per your need.

HTTP request

Step 11. Click on a new step.

New step

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

SQL server action

Insert Row

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

Select the appropriate table to insert a row.

Select the appropriate table

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

Enter data

Step 15. Click on the save button to save the flow.

Click on save button

Step 16. 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’,

HTTP request URL

Step 17. I am using the Postman tool to send a request to flow to run flow.

Postman tool

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

Flow again

Insert

Step 19. Now, verify whether a record is created or not in the on-premise SQL server table.

SQL table

That's it. Our entire setup 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 servers from Azure cloud.