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.

• An Overview of Cloud And Azure Cloud
• Pillars of Cloud And Azure Services
• How To Store Files in SFTP Using Azure Logic App

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

• Overview of Azure on-premise data gateway
• Install and configure Azure on-premises data gateway

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.

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

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.

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

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

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.

Step 8. Once your connection is ready then it shows as ready in the 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.

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

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

Step 11. Click on a new step.

Step 12. Search for SQL server action and then select action ‘Insert a 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.

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.

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

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’,

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

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

Step 19. Now, verify whether a record is created or not in the on-premise SQL server 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.