Integrate Microsoft Flow with an onprem SQL Server

2017-04-25 | js

Microsoft Flow offers various connectors, also for integration with onprem systems. To be able to connect with an onprem SQL server, SharePoint server or file server, a gateway service must be installed at a machine in the LAN where the server is available. To setup the gateway navigate to the Microsoft Flow service in your Office 365 portal and click on the gear symbol and then on “Gateways” (see screenshot below). There you will find the option “+ Create Gateway” where you can download the Microsoft Gateway service.

image

After the download has finished, open the executable file and install the application. You will be prompted to enter your Office 365 credentials where you are creating the flow and a name for the gateway. After that has been done, the gateway service is running and is ready for use (on a server machine the gateway service should be added to the startup applications).

When the gateway is running, a connection to the onprem SQL server must be established, therefore click on the gear symbol again and then “Connections” –> “Create connection”.

image 

In the choice page for the new connection chose “SQL Server”. A form for configuration of the connection to the onprem SQL server will be displayed. Fill out the form with the server name in the local network, the database name, the authentication type (“Basic” for usual SQL authentication) and the credentials to connect to the server. The last option is a dropdown with the gateway to use, here you should see your former created gateway as an option. Click on “Create connection”, if no error is printed out, the connection has been successfully set up.

image

 

After the connection to the onprem server has been established, a new flow can be created using the connection. In the example below we use the SharePoint Flow connector when a new item is created in the SharePoint list “idtable”. When this connector gets triggered we add a connector “SQL server” –> “get rows” as the next action, which then lets you select the table from where you want to get the SQL data rows from. This connector is using the specified SQL onprem server connection. In our case the table name is “flowtesttable”. Additionally, we filter the rows we get from that table by column “id” in the database and query only for the rows having the "id" column equal to the “Titel” column of the SharePoint list element from the former connector. After that, you can do whatever you please with the elements in the next action. In this example all of them get sent via email to a user.

image

 

Microsoft Flow has now successfully been integrated with your onprem SQL server, you can query data from your SQL tables and also e.g. filter them with dynamic data from other connectors (e.g. the SharePoint connector).

Categories: Azure, Cloud, Flow, Microsoft, Office365, SQL Server

Source: https://blog.atwork.at/post/integrate_flow_onprem