Use of Microsoft Flow with Dynamics 365 CRM On-premise

By | January 21, 2019

Introduction

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”. 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.

Microsoft Flow with Dynamics 365 CRM On-premise

3. Once you sign-in you are ready to register the gateway as shown in screenshot below:

Microsoft Flow with Dynamics 365 CRM On-premise

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.

Microsoft Flow with Dynamics 365 CRM On-premise

6. Click on “New Connection” button on top left corner and select “SQL Server” as shown in below screenshot.

Microsoft Flow with Dynamics 365 CRM On-premise

7. On clicking the “SQL server” connections a window will pop up as shown in the below screenshot.

Microsoft Flow with Dynamics 365 CRM On-premise

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.

Microsoft Flow with Dynamics 365 CRM On-premise

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:

Microsoft Flow with Dynamics 365 CRM On-premise

12. Now select the connectors as “SQL server” as shown below:

Microsoft Flow with Dynamics 365 CRM On-premise13. 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.

Microsoft Flow with Dynamics 365 CRM On-premise

15. Next, select the action as “Send Email“ on Gmail account. Here, we need to sign in to Gmail account.

Microsoft Flow with Dynamics 365 CRM On-premise

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:

Microsoft Flow with Dynamics 365 CRM On-premise

Once we are ready by creating the flow then, just save it and we are good to go.

Conclusion

The On-premises data gateway enables one to use Microsoft Flow with their on premise dynamics CRM organization.

70% of global 2000 companies apply gamification to improve productivity and returns!

Gamifics365 – Spin the magic of games within Microsoft Dynamics 365 CRM to improve user adoption, enhance productivity, and achieve company goals!

8 thoughts on “Use of Microsoft Flow with Dynamics 365 CRM On-premise

  1. Manish

    Thanks for this information, can you please more elaborate on the registration with on -premise gateway. If someone is using only there intranet credentials to log in into on-premise crm, what they have to do to get Flow credentials?

    Regards
    Manish Sharma

    1. inogic

      Hi Manish,

      The credentials for sign in Microsoft Flow and on-premise CRM are both different. You need to subscribe for the Microsoft Flow and use the credentials for the sign in.

      Thanks!

  2. Eran Yaron

    Hey guys,

    Great article – thanks for sharing. One thing we have to be careful with the connector – we need to use it for READ ONLY – you shouldn’t write or update data using this. It will create insert and update statements for SQL which are strictly not-supported with MSCRM\Dyn365CE.

    Great for reading and referencing data, but there is still no supported way of creating\updating records in CRM OnPrem from MS Flow.

    Cheers,
    E

  3. AJ

    Hi , I was able to create the connection but the CRM tables are not getting displayed.

    Also, I have read , we can perform read operations only. i.e, we can check if anything in created in db and trigger but we can’t insert into db based on another trigger. i.e, we can’t really use the CRM db connection as a destination.
    Have you tried? Since I can’t see any of the CRM tables i’m unable to proceed further.

    1. inogic

      Yes, we cannot directly create/update record in the database. In this blog, we have shown a way to connect an on-premise CRM and trigger an event using Microsoft flow.

      Thanks!

  4. Gareth Harrison

    Hi,

    Thanks for the great article. For some reason, I can only see a small portion of the tables from my CRM database, do you have any idea why that might be?

    Thanks

    Gareth

    1. Inogic

      If you are trying to use the SQL connection as a destination then it means that you are trying to create or update record in the SQL database of CRM directly. If yes, then you should not directly create, update or delete record in the SQL database as it is not recommended.

Comments are closed.