On-Premises Data Gateway
Power BI · Power BI Service
Theory
The on-premises data gateway is software installed on a computer inside your network that acts as a secure bridge between the Power BI Service in the cloud and data sources that the cloud cannot reach directly, such as a SQL Server in the office or files on a local network share. The gateway makes outbound connections to Azure, so you do not need to open inbound firewall ports.
There are two modes. Standard mode is shared and centrally managed: an admin installs it, adds data source connections, and decides which users can use them; it supports scheduled refresh, DirectQuery and live connections for many people. Personal mode is for a single user, supports only Import refresh, and runs under that user's account, so it is not suitable for team reports. For example, the Sales semantic model connecting to SQL Server 'sql-office-01' is mapped to the company gateway so its 7 AM refresh can reach the database.
Why we use it
Many companies still keep key data on-premises. Without a gateway, published reports on those sources cannot refresh or run DirectQuery from the cloud.
When to use it
Install a standard gateway on an always-on server (not a laptop) when any published model uses on-premises sources. Cloud sources such as Azure SQL Database or SharePoint Online usually do not need one. Use gateway clusters for high availability.
How it works
- Published model uses an on-premises source
- Install and register a standard gateway
- Create a data source connection on the gateway
- Grant users access to the connection
- Is the semantic model mapped to the connection? Yes: Refresh and DirectQuery work. No: Map it in semantic model settings.
- Cloud reports reach on-premises data
Syntax
Install: download 'On-premises data gateway' → sign in → register a new gateway (name + recovery key)
Service: Settings → Manage connections and gateways → New connection (server, database, credentials)
Semantic model → Settings → Gateway and cloud connections → map each sourceExample
1. IT installs the standard gateway 'Jaipur-GW' on an always-on server next to sql-office-01.
2. Admin: Manage connections and gateways → New → On-premises → Jaipur-GW → SQL Server sql-office-01 / SalesDB.
3. Add the BI-Developers group as connection users.
4. Semantic model 'Sales Overview' → Settings → Gateway and cloud connections → map to the new connection.
5. Scheduled refresh now succeeds through the gateway.Key points
- Bridges the cloud Service and on-premises sources
- Uses outbound connections; no inbound ports needed
- Standard mode: shared, managed, supports refresh and DirectQuery
- Personal mode: one user, Import refresh only
- Install on an always-on machine; consider a gateway cluster