Power BI gateway for on-prem data

Power BI lives in the Microsoft cloud; many Dynamics 365 customers still have on-premise data sources — old SQL Server databases, legacy ERPs, file shares, internal services — that must be combined with cloud data for analytics. The on-premises data gateway is the bridge — a small Windows service running inside the customer's network that securely brings cloud Power BI queries to on-prem data.

The architecture.

The gateway sits in the customer's network as a Windows service:

• Outbound-only connection to Microsoft's Azure Service Bus relay. The gateway doesn't accept inbound from the internet; it polls Microsoft.
• When Power BI cloud needs to query an on-prem source, it sends the query through Azure Service Bus.
• The gateway picks up the query, runs it against the configured on-prem source, and returns results back through Service Bus to Power BI.
• The result lands in Power BI's data model; the user sees their report.

This pattern works without VPN setup, without opening firewall ports, without exposing on-prem data to the internet.

Two gateway types.

• Standard mode — for use by multiple users / Power BI reports. Production scenario. Multiple users share the gateway; admin centrally manages.
• Personal mode — for an individual maker's exploration; the gateway runs on the maker's PC. Less robust but quick for prototyping.

Production deployments use standard mode.

Installation.

1. Download the on-premises data gateway installer from Microsoft.
2. Install on a Windows server inside the network — typically a dedicated server, not a shared one.
3. Sign in with a Microsoft 365 / Entra ID account that has appropriate Power BI admin rights.
4. Register the gateway with a name and recovery key.
5. Configure data source connections — add each on-prem source the gateway will serve, with credentials.

Data source configuration.

For each on-prem data source the gateway should serve:

• Type — SQL Server, file share, OData feed, custom connector.
• Server / database / path — connection details.
• Credentials — typically a service account dedicated to gateway use, with read-only access to the source.
• Privacy levels — for combining data sources, declare each source's sensitivity to prevent cross-contamination in queries.

High availability. Single-gateway deployments are a single point of failure. Production:

• Gateway cluster — multiple gateway machines registered to the same logical cluster. Power BI load-balances across them.
• Failover — if one gateway fails, queries route to others.
• Maintenance windows — rolling restarts during low-usage hours.

For mission-critical analytics, gateway clusters are essential.

Refresh. Power BI datasets refresh from on-prem sources via the gateway:

• Scheduled refresh — Power BI cloud kicks off refresh on a schedule.
• On-demand refresh — user triggers refresh; same path through the gateway.
• Refresh rate limits — Pro datasets refresh up to 8 times per day; Premium up to 48 times per day.
• Real-time queries (DirectQuery) — every report interaction queries through the gateway; minimal latency required.

Performance.

• Latency — every query has gateway round-trip overhead; 100-500ms typical.
• Bandwidth — large result sets transit through Service Bus; can saturate the gateway's link to Azure.
• Concurrent queries — gateway has limits per CPU / memory; oversized concurrent load needs cluster expansion.

Security.

• Outbound-only — no inbound exposure.
• Service Bus relay — encrypted via TLS.
• Authentication — Power BI authentication and gateway-resident credentials are separate; query authorisation respects Power BI; data-source connectivity uses gateway-configured credentials.
• Audit — gateway logs every query; configurable retention.

Use cases for Dynamics 365 customers.

• Combining cloud BC / D365 with on-prem ERP data — for customers transitioning or running parallel systems.
• Joining D365 data with on-prem SQL Server — internal data marts, legacy applications.
• File-share document references — pulling document metadata from on-prem SharePoint or file servers.
• Custom internal APIs — accessing custom services that aren't internet-exposed.

Maintenance.

• Gateway version updates — Microsoft releases updates monthly. Stay current.
• Credentials rotation — periodically rotate data source credentials.
• Resource monitoring — gateway CPU, memory, disk I/O. Plan for growth.
• Connection refresh — connections occasionally need re-authentication.

Limits.

• Gateway file size — large datasets (gigabytes) through gateway are slow. Consider replicating to Azure SQL or Synapse for analytics.
• Some connectors aren't supported through gateway — primarily cloud-native ones don't need it.
• DirectQuery performance — gateway adds latency per query; not always suitable for high-interaction reports.

Common pitfalls.

• Single gateway as production — outage takes down all reports. Cluster.
• Gateway server under-resourced — slow queries, timeouts. Right-size CPU and memory.
• Stale credentials — gateway data sources fail mysteriously; rotate and refresh.
• Insecure credentials — gateway service account with admin rights to source. Use read-only.

Operational reality. For Dynamics 365 customers with on-prem data, the gateway is essential infrastructure. Plan for it, maintain it, monitor it like any other production service. Cloud-first analytics with cloud-first data sources doesn't need it; mixed environments do.