Power BI Gateway Explained: Connecting On-Prem Data

Power BI reports live in Microsoft's cloud, but much of the data they need lives on company networks: SQL Server databases, file shares, older systems. The on-premises data gateway connects the two. Microsoft describes it as a locally installed Windows application that acts as a bridge between on-premises data sources and services in the Microsoft cloud (Microsoft Learn: on-premises data gateway). Without one, a report built on local data works in Power BI Desktop and then fails to refresh once published.

How it works

The gateway runs on a machine inside your network. It opens outbound connections to Azure and polls for work; no inbound ports need opening in the firewall, and all communication from the cloud arrives as responses to that polling. When a scheduled refresh or a DirectQuery visual needs data, the request travels to the gateway over its outbound connection, the gateway queries the source with stored credentials, and returns the results. The same gateway also serves other Microsoft services, including Microsoft Fabric, Power Automate, Power Apps and Azure Analysis Services.

When you need one

• Needed: sources on a private network, such as SQL Server in your data centre, files on a network share, or on-premises Analysis Services, for both scheduled refresh and DirectQuery.
• Usually not needed: cloud sources that the Power BI service can reach directly.
• The combined case: a semantic model can use only one gateway connection, and if a single query combines an on-premises source with a cloud source, Power BI uses the gateway for the cloud source as well (Microsoft Learn: data refresh).

Three kinds of gateway

For anything used by a team, choose standard mode. Personal mode suits an individual who builds reports only for themselves. If the analyst who owns a personal gateway leaves or switches off their computer, every report depending on it stops refreshing.

Where to install it

Microsoft's installation guidance is specific (Microsoft Learn: install a gateway):

• Recommended: an 8-core CPU, 8 GB of memory, a 64-bit version of Windows Server 2019 or later, and solid-state storage for spooling.
• Not on a domain controller.
• Not on a laptop or any computer that might be turned off, asleep or disconnected, because refreshes fail whenever the gateway is unavailable.
• On a wired network, since wireless connections can hurt performance, and with care on virtual machines, where performance can be inconsistent.
• For Windows authentication, on a computer in the same Microsoft Entra environment as the data sources.

Put it close to the data sources on the network, since every refresh moves data from source to gateway to cloud.

Setting it up

1. Download and install the gateway on the chosen machine, and register it with your organisation account.
2. Configure it for your firewall and proxy if needed.
3. Add gateway administrators, so more than one person can manage it.
4. In the Power BI service, add connections for each data source with the credentials the gateway should use.
5. In each semantic model's settings, map its data sources to the gateway connections, then schedule refresh.

Connecting Power BI to SQL Server walks through the most common source, and data refresh covers scheduling and failure alerts.

Clusters for high availability

A single gateway machine is a single point of failure: when it restarts for updates, every dependent refresh fails. A cluster of two or more gateways avoids that and can load balance traffic across its members (Microsoft Learn: gateway clusters). For production reporting, a cluster of at least two machines is the safer design. Microsoft's refresh guidance also suggests separate gateways for import refreshes and DirectQuery, so heavy nightly refreshes do not slow interactive reports.

Limits and quirks to know

• DirectQuery responses through the gateway are limited to 16 MB of uncompressed data.
• 1,000 data sources is the maximum per gateway cluster.
• Credential changes are not instant: credentials are cached on the gateway, and Microsoft notes that changed credentials may not be used for around five hours, so refreshes can fail in the meantime.
• Updates matter: Microsoft releases a gateway update every month and supports only the last six releases.

Keeping it healthy

• Update regularly, within the supported window, ideally one cluster member at a time.
• Monitor refresh failures and the gateway's performance logs, and watch CPU and memory during peak refresh times.
• Document which reports depend on which gateway, so maintenance can be planned.
• Use service accounts with least-privilege, read-only access to sources, not a person's own login.

For how gateways fit into promoting content between workspaces, see deployment pipelines, and for the wider service, publishing, sharing and workspaces.

Scheduled refresh, publishing and connecting to databases are part of the Power BI service and data connectivity modules of the Power BI course.

Sources

• What is an on-premises data gateway? (Microsoft Learn) — Microsoft Learn
• Install an on-premises data gateway (Microsoft Learn) — Microsoft Learn
• Manage gateway high-availability clusters and load balancing (Microsoft Learn) — Microsoft Learn
• Data refresh in Power BI (Microsoft Learn) — Microsoft Learn