Developer and End user experience to use PowerApps with on-premise data gateway (part 1)
Do you know what is on-premise data gateway?
You may refer to Microsoft article here for details or read my short summary.
Power Platform, include PowerApps and Power Automate (it was called Flow before), are cloud services provided by Microsoft. If you want to use their cloud services with your own data (it is called on-premise data sources), you need to setup on-premise data gateway.
On-premise data gateway is a software provided by Microsoft free of charge. After download, install, and configure it on Windows server, you can start use it to establish connections between MS cloud and your on-premise data sources. For example, SharePoint 2019 on-premise, MS SQL Server, your C drive, etc.
You can probably find some good reference online talk about how to install gateway and troubleshoot so I will not repeat here. (Give me a message below if I can help) Today I will talk about Developer and End user experience of using PowerApps with on-premise SharePoint 2019 as data source.
Developer
Assume you have configured the gateway correctly and the developer need to build an App in PowerApps studio. First thing developer need to perform is adding the on-premise connection and the data source.
In the browser, navigate to powerapps.com and sign in with your M365 account. Once signed in, go to “Connections” under the “Data” section in the left navigation menu.
Click on “New connection” on the top bar. Then choose your connector types. Here you can see some of connector types have “Premium” label. It means the end users need a higher plan to use them. As at this moment, only users with “PowerApps per app plan” or “PowerApps per user plan” can use Premium connectors. For the non-premium connectors, all users with M365 Enterprise plan can use them. However, Microsoft may change their price policy in future.
You may ask why there is a “Type” column with “Standard” as value at the right. Honestly I don’t know what is it. Please let me know if you have the answer.
Here I choose SharePoint. Please note that both SharePoint online and SharePoint on-premise use same connector. Only users with “PowerApps per app plan” or “PowerApps per user plan” can use on-premise data source.
Press the + button beside SharePoint. Below screen will be prompted. Choose the 2nd option and then type in your on-premise AD login & password.
Press “Create” to finish. Then the Connection you just created will be listed and named as “domain\username” with the green SharePoint logo. IT IS REALLY STUPID NAMING METHOD. Because when you need to create more SharePoint connections later all of them will have same naming and lead to a lot of confusion. Nice job Microsoft!
Then open your App with edit mode. Press on “Data” at left navigation menu -> Add data. Choose SharePoint data source.
Now, if you created several SharePoint source like me, you will see something like these below:
By some luck, pick the connection you need. The right panel will prompt up. Type in the URL of on-premise SharePoint site and press Connect button.
Here you may get 502 bad gateway error. It means your on-premise data gateway cannot reach the mentioned URL. Please note that:
- Gateway DO NOT recongize HOSTS file. Make sure the url type in can correctly resolve to the IP address on the gateway server.
- Make sure your SharePoint on-premise have correct AAM setup.
- Network is not blocking between gateway server and your SharePoint web front end. Normally it will be port 80 and 443.
- If your intranet enforce proxy server, make sure your gateway configuration have updated for proxy server. (reference)
If everything is alright, you can see the SharePoint lists under the site. Check the lists you need. Then each list will be added as a new data source and listed in your PowerApp studio.
After you finish your app, just save and publish it normally. According to MS article here, If you create and share an app that includes data from an on-premises source, the on-premises data gateway itself and certain types of connections to that gateway will be shared automatically.
Now you can share your app to specific users or Azure groups. Please remember share the on-premise data gateway to users. To do so, in https://make.powerapps.com/, click on Data -> Gateways. Select the gateway and then “Share”.
On part 2 I will talk about End user experience on this app.
