PowerApps are hosted in Azure cloud, so it is not straight forward to access on-premise network but it’s easy. To connect on-premises, Microsoft introduced “On-premises data gateway”
What is On-premises Data Gateway
“The on-premises data gateway acts as a bridge to provide quick and secure data transfer between on-premises data and several Microsoft cloud services”.
Azure cloud services include Power Apps, Power BI, Power Automate, Azure Analysis Services, and Azure Logic Apps.
Types of Gateways
- On-premises data gateway allows multiple users to connect to multiple on-premises data sources. You can use an on-premises data gateway with all supported services, with a single gateway installation. This gateway is well-suited to complex scenarios with multiple people accessing multiple data sources.
- On-premises data gateway (personal mode) allows one user to connect to sources, and can’t be shared with others. An on-premises data gateway (personal mode) can be used only with Power BI. This gateway is well-suited to scenarios where you’re the only person who creates reports, and you don’t need to share any data sources with others.
How the gateway works
- Any one of the cloud services (for example PowerApps) send the request to a Gateway cloud service.
- The gateway cloud service creates a request along with the encrypted credentials for the data source, and sends the request to the queue for the gateway to process.
- The gateway cloud service pushes the request to the Azure Service Bus. (Azure Service bus is a queue based processing system)
- The on-premises data gateway polls the Azure Service Bus for any pending requests , decrypts the credentials, and connects to the data source with those credentials.
- The gateway sends the request to the data source.
- The results are sent from the data source, back to the gateway, and then to the gateway cloud service
- Final results are utilized in cloud service
Install the On-premises data gateway
- Download On-Premises data gateway from Microsoft download
- Install the downloaded gateway on the server or VM or (local computer for testing)
- Connect Azure work or school account
- Set up the recover key
- For high availability, make sure to configure gateway cluster
- We should see a new Gateway connection on PowerApps
- Go to PowerApps
- On the left navigation Expand “Data”
- New gateway should be listed with the name we installed (As per this demo “HP_Envy”)
Connect On-premises SQL Server
- Make New Connection to the SQL Server
- Create flow and access SQL server, which will create a connection
Test the Data Source
Test the on-premises gateway by creating a flow with SQL connection. We have flow, which has two steps
- Call the stored procedure “GetEmployee”
- Call the stored procedure with parameter “GetEmployeeByDepartment”
Supported Data Sources
This on-premises data gateway supports the below data sources
- BizTalk Server 2016
- File System
- IBM DB2
- IBM Informix
- IBM MQ
- Oracle Database
- SharePoint Server
- SQL Server
Other things to consider or remember
- For one server/machine, only one gateway is sufficient
- Make sure system is up and running when connecting data from the cloud
- There are no inbound connections on this pattern. Gateway uses outbound connections
- Gateway polls the request from Azure Service Bus
- Verify the firewall and proxy