One of the challenges many organizations face when beginning to work with Azure applications (PowerBI, PowerApps, Flow, etc.) is that their data is on-premise and the applications are hosted in the cloud. Moving the data to the cloud is often cost-prohibitive and there can be operational requirements that prevent the data, or the systems hosting it, from being relocated to the cloud.
So, how can on-prem data be used with Azure apps?
One solution is to use an On-Premise Data Gateway.
The Azure On-premises Data Gateway is an application that is installed on a server (or desktop) in the local/on-premises environment.
The Data Gateway uses the Azure Service Bus and acts as a message broker for data requests between Azure-based applications and on-prem data sources. Unlike VPNs (where the cloud app would communicate directly with the on-prem data source) the Azure Service Bus uses secure message queues to communicate between the application and data source (using TCP or HTTPS). The general process is:
- The Azure application sends a data query to the Gateway Cloud Service.
- The cloud service packages the query, along with the encrypted credentials for the on-premises data source, into a message. The message is then sent to the Azure Service Bus.
- The Azure Service Bus places the message into a queue for the application (on-premises) data gateway.
- The application gateway gets the message, decrypts the query & credentials, and then connects to the on-prem data sources with those credentials.
- The results are sent from the data source to the on-prem application gateway, through the Azure Service Bus, and back to the Azure application.
To create a new gateway in Azure, the account that is used to register the gateway will need access to an Azure Subscription and Resource Group. Permissions can be managed through the Azure portal: https://portal.azure.com.
Azure Subscription – Each Azure account can contain multiple subscriptions for management/billing purposes, and each subscription can contain various Azure resources. Subscriptions can be nested/grouped using Azure Management Groups. Organizations with Enterprise Agreements may need to create new subscriptions through the enterprise portal: https://ea.azure.com.
Azure Resource Group – A logical container that holds Azure resources (services, databases, storage, etc). Resource groups can include all related resources for a project/solution, or any resources that you want to manage as a group.
Once the Azure Subscription and Resource Group have been created, the on-prem gateway application can be installed & registered. The on-prem application can be run in 2 modes, Personal or Standard.
- Personal mode – For use with Power BI only. Allows one user to connect to on-prem data sources. Cannot be shared with others.
- Standard mode – For use with all supported Azure apps/services. Allows multiple users to connect to multiple on-prem data sources.
During the installation process, the name entered in the on-premises data gateway name field is how the gateway will appear in the Azure portal and applications. Multiple gateways can be clustered to allow for load-balancing and high-availability configurations.
Once the application installation has completed, the gateway must be registered in Azure. The Azure account that is used to sign-in and register the gateway will be the account that is used to share the gateway service.
After the gateway is installed & registered, it will be running on the local machine as NT Service\PBIEgwService. This setting can be modified to allow the service to run as a domain user or domain Managed Service Account (recommended).
Now that the On-Prem Data Gateway is set up, it can be used & shared in the various Azure applications (Power BI, PowerApps, Flow, etc). In Power BI this is accomplished by selecting a Dataset and adding it to a gateway.
Similarly, sharing the gateway in Power BI is accomplished by assigning permission to each user or group.
Troubleshooting
Subscription Error – When creating a gateway in the Azure portal, the selected subscription may return an error. The subscription <name> doesn’t have permissions to register the resource provider(s): Microsoft.Web, Microsoft.Logic.
This error can be resolved by navigating to the Resource providers setting for the subscription. Select the required provider(s) from the list, and click on the Register button.
