Tip #1267: Do PowerApps work with Dynamics 365 on premise?

The answer is “it depends.”

It depends on how you define “works with.”

There is no PowerApps connector for D365 on Premise. There is a SQL Server connector, and this connector can connect to on premises SQL databases. This includes the D365 on premise SQL database. So you can make an app that reads directly from your on premise CRM database, but this connector cannot update or create records in a supported manner. And as you long time tip of the day readers know, we don’t recommend doing unsupported stuffunless George says it’s ok.Another issue with this approach is it is likely to be slow–it’s very difficult to get good performance reading from on premises systems from the cloud, and you have to open up external access to your SQL server. Just don’t do this.

The second option is to integrate your on premise environment with the CDS–in effect setting up a hybrid environment where you have a copy of your configuration in the cloud as well as your on premise CRM, and create a bi-directional integration to synchronize data changes between the two environments. This is probably the best option as it would recognize your existing security and record ownership and provide full CRUD capabilities. But this option also carries some potential overhead–you will need to reflect configuration changes in both (at least for the entities that are included in your PowerApps), and there will be potential delay for record changes to synchronize between the two — If salesperson 1 updates a contact phone number in D365 on premise and contact 2 saves the contact on her PowerApp, this could lead to some interesting data conflicts. There will also be licensing implications — your users will need to have at least a P1 license in the cloud to be able to use PowerApps that use the Common Data Service

A third option is to synchronize your on premise CRM data with another type of cloud storage — could be an Azure data warehouse, SQL database, data lake, or any number of other places. These are viable connections for your PowerApp. The problem (like # 1) is with creates, edits, and deletes, as well as reads if security restrictions in D365 on premise need to be honored. If I want a Power BI dashboard or PowerApp that simply display any record from my CRM on premise system, I can replicate the on premise data to Azure and go to town. However, if users create or modify records from the PowerApp or need to be restricted in viewing records following the same logic that CRM on premise security roles dictate, this is not a great option, and you may inadvertently open the proverbial barn door.

My recommendation

Don’t do this. Go all the way to the cloud, not part way. Your Power Platform experience will always be more satisfying if you are using Common Data Service and Dynamics 365.

But wait, you say, we have a bunch of reports and system integrations that we can’t quickly upgrade, yet we want to give our users the value of the Power Platform now. I get that and totally understand that if you have been developing on CRM on premise for years, it takes a while to get some of those components cloud ready.

Maybe you should take option 2 and flip it on its head–instead of making the assumption that all of the users will keep using D365 on premise, what if all the users moved to the cloud, yet you kept D365 on Premise around short term for reporting purposes? That way the users could start benefiting from the cloud and directly connected PowerApps and Flows now, but keep on premise around as a read only replicated reporting environment (while you work to move those reports and integrations to a cloud ready approach (like PowerBI). This would minimize the overhead of the approach, as the majority of the users would simply access D365 online.

Summary

Avoid this if at all possible — go all the way to the cloud. If, for some reason, you need to do this short term, I recommend that you replicate your on premise data with the Common Data Service to enforce appropriate security and make your future cloud migration easier.

Cover photo by unsplash-logoElias Schupmann

10 thoughts on “Tip #1267: Do PowerApps work with Dynamics 365 on premise?

  1. Paul Kunze says:

    How about the OData Endpoint (if the App is readOnly)?

    • Joel Lindstrom says:

      Yes there are numerous approaches that work if you want a read only app. But I don’t think most users will want a read only app.

  2. Mohamed says:

    On-premises data gateway is not an option?

    • Joel Lindstrom says:

      Yes, it is an option, but it is a SQL Server connection–you cannot update or create records in a supported way using on premises data gateway. If you want read only data in your app, this is fine, but it won’t give you full functionality.

  3. What about Kingswaysoft SSIS to CDS, once you are in CDS you have full Powerapps power… 😉
    https://www.kingswaysoft.com/blog/2018/11/22/Leveraging-PowerApps-with-Microsoft-Dynamics-On-Premise

  4. John Davis says:

    Could we not use Power Automate to call OnPrem WebAPI to create/update? like we call organization service and then use retrievemultiples/create/update etc., in c# console application say for example? Could we mimic these console app CRUD operations using Power Automate once you have access to the CRM Org service?

    • Yes, you should be able to create a custom connector and use Power Automate with on-premises installation. Not sure if you’d need the gateway but, for example, if you are after reading only then you can talk directly to SQL Server.

  5. Daniele says:

    Hi, so if I’ve understood well it’s not possible to use dataverse with on premise environments,
    unless you create a copy of your on prem
    database in the cloud and keep the databases synchronized with very bad performance and
    The chances to create data conflicts.
    If I need to develop an app with power app to read only the data , I could use the sql server connector,
    But what if I want also to create and update data to my on premise env without to use dataverse?
    I read about custom connector, could please give some details about that ?
    Thanks in advance
    Daniele

    • Custom connector does not exist on its own, it’s a layer on top of existing API. The only approach in this scenario is to create a custom API layer and then create custom connector on top of that API. Use authentication will also be a challenge in this scenario if you want to keep the identity of the user.

      The other approach to consider is to build a set of virtual tables in Dataverse, each corresponding to on-premises table. Then you’ll be able to use standard Dataverse connector “as is”. But it’s a fair bit of development work.

Leave a Reply

Your email address will not be published. Required fields are marked *