Configure On-premises Data Gateway for PowerApps And Other Azure Cloud Services

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
  1. 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.
  2.  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

  1. Any one of the cloud services (for example PowerApps) send the request to a Gateway cloud service.
  2. 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.
  3. The gateway cloud service pushes the request to the Azure Service Bus. (Azure Service bus is a queue based processing system)
  4. 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.
  5. The gateway sends the request to the data source.
  6. The results are sent from the data source, back to the gateway, and then to the gateway cloud service
  7. 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

Now, connect power automate to on-premises data source. We need to create a connection using that gateway. We can create in two ways
  1. Make New Connection to the SQL Server
  2. 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

  1. Call the stored procedure “GetEmployee”
  2. 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
  • MySQL
  • Oracle Database
  • PostgreSQL
  • SAP
  • SharePoint Server
  • SQL Server
  • Teradata
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 

3 comments

Leave a Reply

Your email address will not be published.