Posted on by Nate Chamberlain

On-premises data gateway for Power BI, Power Automate (Flow), and Power Apps

On-premises data gateways allow users to connect online services like Power BI service, Power Automate (formerly Microsoft Flow), and Power Apps to their on-premises data sources such as SharePoint Server, SQL databases, and network file shares.

The gateway has allowed me to use modern services like Power Automate to bring advanced functionality to my SharePoint 2013 and 2016 lists and libraries, such as copying items across site collections (not possible in SharePoint Designer). I’ve also been able to share Power BI data visualizations and reports of limited on-prem data sources such as SQL databases with off-prem consumers who wouldn’t otherwise have access to that data.

One data gateway will cover all three apps – you don’t need separate gateways for each app/service. You can, however, have two gateways per machine but a max of one per mode:

  • Regular mode (share access to data)
    • Multiple users
    • All services (BI, Automate, Apps)
    • Supports Power BI scheduled refresh and live query
  • Personal mode (others don’t need access to data)
    • One user
    • Only works with Power BI
    • Only supports Power BI scheduled refresh

Install a gateway

The following steps are adapted from documentation available on Microsoft Docs. Please read all available information (see “More info” section at bottom of post) prior to installing a gateway to make sure you’re configuring it correctly for your organization’s needs.

  1. Download and install the gateway
    • Install on a server/computer that is always on to make sure Flows and refreshes aren’t disrupted due to the gateway being unavailable on a powered down machine. You might consider installing multiple gateways and creating a gateway cluster for high availability or load balancing in case a server goes down.
    • Install on a personal/work computer if it will only be used by one person or machine manually (perhaps for those building Power BI reports without automatic refreshes). This is not ideal for scheduled flows or data refreshes as the machine may be powered down.
    • Note: You can restrict who is allowed to install on-premises data gateways for your organization.
  2. Configure the gateway.
  3. Add gateway admins
    • By default, the individual/account that installs a gateway is a gateway admin. You can manage admins, adding other security groups or individuals as needed.
  4. Use the gateway to refresh an on-premises data source such as a SQL database (specific to Power BI).
  5. Troubleshoot gateways

Share a data gateway

To manage gateways from your apps, follow these paths;

  • Power Automate –> Settings wheel –> Gateways
  • Power Apps –> Data –> Gateways
  • Power BI –> Settings wheel –> Manage gateways

In Power Automate and Power Apps, your options are identical from here. You can view additional information on existing gateways, download another gateway, and share or delete existing gateways.

If you share gateways with others, you get a dialog with the option to include everyone in the organization. This way anyone could have access to the gateway to use in their own Flows, apps, and reports. And unless a user is an admin, they can’t reconfigure the gateway :

When you share a gateway with others, you can provide these levels of access within Power Automate and Power Apps (not Power BI):

  • Can use (for those just using apps)
  • Can use + share (for those sharing apps, which will automatically share the gateway)

More info on these levels available here.

When assigning permissions, you can specifically allow these permissions:

In all three apps, you can make someone else (or a security group) an admin. If you make another user an admin of a gateway, they have the same rights as you to manage the gateway and its configuration in its entirety.

More information from Microsoft Docs

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.