On-premises data gateway

What is an on-premises data gateway?

An on-premises data gateway is a Windows service that connects Microsoft cloud services to data inside your own network. Power BI, Microsoft Fabric, Power Automate, Power Apps, and Logic Apps can use it to reach sources such as SQL Server, file shares, local databases, or the database behind an ERP system.

The gateway solves a security problem. A cloud service should not be allowed to call straight into your internal network. The gateway reverses the direction: it runs inside your network and makes outbound connections to Microsoft cloud services. You do not need to open inbound firewall ports for the cloud to reach your data.

Think of it as a controlled courier. The cloud queues a request. The gateway checks in, picks it up, runs the query against the internal source, and returns the result through the outbound connection it created.

How the gateway works

The gateway communicates through outbound encrypted connections, using Azure Relay as part of the service architecture. The cloud service prepares a request, the gateway retrieves it, connects to the local data source, runs the query, and sends the result back.

Credentials for data sources are stored encrypted in the cloud service and are decrypted on the gateway machine when needed. That means gateway machine security matters: patching, access control, service-account management, and disk protection are all part of the setup.

For scheduled refresh, the gateway moves data during refresh windows. For DirectQuery, each report interaction can trigger live queries through the gateway. DirectQuery therefore needs more CPU, memory, network reliability, and source-database headroom than a nightly import refresh.

Gateway modes

Standard mode. The normal business setup. One gateway installation can serve multiple users, multiple data sources, and several Microsoft services. It can have multiple admins and can be clustered for high availability.

Personal mode. A single-user gateway for Power BI only. It is useful for personal work, but risky for team reporting because it is tied to one user and often ends up on a laptop or desktop that can go offline.

VNet data gateway. A Microsoft-managed gateway for data sources inside an Azure virtual network. You do not install software on your own server, but it is meant for Azure network scenarios, not for a SQL Server under a desk or an ERP database in your office network.

For company reporting, standard mode on a stable server is the default choice.

When you need a gateway

You need a gateway when the data source is inside a private network that the Microsoft cloud cannot reach directly. Common examples are an on-premises SQL Server, a local file share, an old SharePoint Server, an Access database on a network drive, or an ERP database hosted on your own server.

You usually do not need a gateway for cloud sources that are already reachable by the service, such as SharePoint Online, Dataverse, or an Azure SQL Database with the right network rules.

The exception is a cloud source that you deliberately locked behind a firewall, private endpoint, VPN, or virtual network. Then you may still need a standard gateway, VNet data gateway, or another approved network path.

Gateway or cloud copy?

A gateway is often the right answer while the source system remains on-premises. It keeps the source of truth where it already lives and lets Power BI or Fabric refresh from it. Combined with incremental refresh, it can avoid moving the full table every time.

A cloud copy becomes more attractive when several tools need the same data, refreshes become heavy, DirectQuery feels slow, or you need history and shared definitions. In that case, copy the data into a warehouse or lakehouse and let reports query that analytical layer instead of the operational source.

The trade-off is ownership. A gateway needs a server, updates, credentials, and monitoring. A cloud copy needs pipelines, storage, compute, security, and data modelling. Pick the simpler operating model for the problem you actually have.

What to watch out for with a gateway

Install it on a server that stays on. Do not place a business gateway on someone's laptop. Sleep mode, Wi-Fi, travel, and personal updates are a recipe for failed refreshes.

Keep it updated. Microsoft releases gateway updates regularly and supports only recent versions. Make gateway updates a normal admin task.

Plan for failure. One gateway server is a single point of failure. For important reporting, create a gateway cluster with at least two machines and keep them on compatible versions.

Store the recovery key. The recovery key is needed to restore, migrate, or take over the gateway. Microsoft cannot recover it for you. Put it in the organisation's password vault.

Use shared ownership. Assign multiple gateway admins or a security group. A gateway owned by one departed employee is painful to maintain.

Avoid personal-gateway sprawl. Many personal gateways across laptops make refreshes hard to understand and support. Centralise shared reporting on a standard gateway.