Recently, while trying to work on Microsoft Flow to perform some actions from Dynamics CRM on-premise, we discovered that to access on-premise data we needed to create connection with Microsoft flow.
So, in this blog we will show how you can connect your on-premise organization with Microsoft flow.
It is possible to connect Microsoft flow with CRM on-premise with the help of “On-premises data gateway” to perform CRUD operations. By using the data gateway we can create connection with Microsoft Flow via SQL server. Let’s see what are steps needed to be followed for the same.
1. First you need to install the “On-premises data gateway.” Click here to download the same and complete the installation process.
Note: You need to install a gateway on a machine that is in the same network as the SQL server. This machine must be always on and be always connected to the internet.
2. Post installation, you need to sign in to register the data gateway. See below screenshot:
Note: For signing in you need to use the same credentials which you are using to sign in to the Microsoft flow.
3. Once you sign-in you are ready to register the gateway as shown in screenshot below:
In the above screenshot you will see that there are 3 fields:
- New on-premises data gateway name: Used to define the name. You can give any name of your choice.
- Recovery Key: The recovery key will be needed if you ever need to migrate, restore or take over a gateway. You can enter any value for key.
- Confirm Recovery: Re-enter the Recovery key.
4. Once you register the gateway, you need to sign in into the Microsoft flow using https://us.flow.microsoft.com
5. Then, click on settings icon on top right corner and select the “Connections” to create the new connection as shown in the below screenshot.
6. Click on “New Connection” button on top left corner and select “SQL Server” as shown in below screenshot.
7. On clicking the “SQL server” connections a window will pop up as shown in the below screenshot.
8. To connect to on-premises data choose the option “Connect using on-premises data gateway” as shown in the above screenshot.
9. Then, fill all the details of SQL server and choose the gateway which we installed as shown in below screenshot and click on “Create” button to create the connection.
10. Post successful creation of connection you can use the Microsoft flow for the on-premise organization using the SQL server connectors in the same way we use it for any online CRM.
11. We have created one sample flow by using SQL server connectors to show how it works. In the example below, we have created a flow to send an Email whenever a new “Account” record gets added in the integrated SQL database. To accomplish the same we have selected the “Create from blank” option as shown in the below screenshot:
12. Now select the connectors as “SQL server” as shown below:
13. Here, we want to trigger this flow when new record gets created into the SQL database. So, we are selecting the 1st option i.e. “When an item is created” as can be seen in the above screenshot.
14. Here, we want to trigger this flow when new record gets created into the SQL database. So, we are selecting the 1st option i.e. “When an item is created” as can be seen in the above screenshot.
15. Next, select the action as “Send Email“ on Gmail account. Here, we need to sign in to Gmail account.
16. Then, fill the necessary fields to be used for sending an email. We can also access the fields from the SQL database and map them in the attributes needed to send an email as shown below:
Once we are ready by creating the flow then, just save it and we are good to go.
The On-premises data gateway enables one to use Microsoft Flow with their on premise dynamics CRM organization and thus perform CRUD operations on it.