On-Premises Data Gateway Configuration
Connect Power BI to on-premises and private network data sources with the data gateway. Installation, configuration, clustering, and security guide.
The On-Premises Data Gateway is the bridge between your corporate network and Power BI cloud services. Any time Power BI needs to access data that is not publicly available on the internet—SQL Server databases behind firewalls, Oracle instances on private networks, file shares on internal servers, or data sources within Azure Virtual Networks—a gateway is required. Without a properly configured gateway, scheduled refreshes fail, live connections timeout, and your cloud-based reports cannot reach the data they need. This guide covers installation, configuration, clustering for high availability, performance tuning, and the security architecture that makes gateways safe for enterprise deployment.
When You Need a Gateway
| Scenario | Gateway Required? | Gateway Type | |---|---|---| | On-premises SQL Server, Oracle, SAP, MySQL | Yes | Standard | | Azure SQL Database (public endpoint) | No | N/A—direct cloud connection | | Azure SQL in private VNet / private endpoint | Yes | Standard or VNet gateway | | File shares (CSV, Excel on network drive) | Yes | Standard | | SharePoint on-premises | Yes | Standard | | Dataverse / Dynamics 365 Online | No | N/A—direct cloud connection | | REST API on internal server (no public URL) | Yes | Standard | | SSAS Tabular on-premises (live connection) | Yes | Standard |
If your data source has a public internet endpoint and does not require VPN or firewall traversal, you do not need a gateway.
Gateway Architecture
How the Gateway Communicates
The gateway uses an outbound-only connection model. This is critical for security:
- The gateway service on your server initiates an outbound HTTPS (port 443) connection to Azure Service Bus
- Power BI Service sends refresh requests through Azure Service Bus to your gateway
- The gateway executes queries against local data sources and returns results through the same outbound connection
- No inbound firewall ports need to be opened—the gateway calls out, never receives inbound connections
This architecture means your corporate firewall only needs to allow outbound HTTPS to Azure Service Bus endpoints (*.servicebus.windows.net). No DMZ, no reverse proxy, no exposed ports.
Standard vs Personal Gateway
| Feature | Standard Gateway | Personal Gateway | |---|---|---| | Shared access | Multiple users, multiple datasets | Single user only | | Supported sources | All on-premises and VNet sources | Import mode only (no live connections) | | Clustering | Yes (HA with failover) | No | | Admin management | Centrally managed by IT | User-managed | | Runs as | Windows service (always on) | Desktop application (must be running) | | Recommended for | All production scenarios | Personal prototyping only |
Always use the Standard Gateway for production. Personal gateways cannot participate in clusters, cannot serve live connections, and stop working when the user's machine is off.
Installation and Configuration
Server Requirements
Choose a server that will host the gateway with these specifications:
- OS: Windows Server 2019 or later (Server 2022 recommended)
- CPU: 8+ cores (gateway is CPU-intensive during data compression)
- RAM: 16 GB minimum, 32 GB recommended for large datasets
- Network: Reliable, low-latency connection to both data sources and internet
- Availability: Always-on (not a developer workstation)—use a VM or dedicated server
- Location: Same network segment as your data sources for lowest latency
Do not install the gateway on a domain controller or a server running SQL Server with heavy load—the gateway needs dedicated resources during refresh windows.
Installation Steps
- Download the gateway installer from the Power BI Service > Settings > Manage gateways > Download gateway, or from the Microsoft download center
- Run the installer and select On-premises data gateway (recommended) for Standard
- Sign in with your organizational account (the account that administers Power BI)
- Name the gateway cluster and provide a recovery key (store this securely—it is needed to restore or add nodes to the cluster)
- The gateway registers with Power BI Service and appears in the admin portal
Adding Data Sources
After installation, configure each data source the gateway will serve:
- In Power BI Service, navigate to Settings > Manage connections and gateways
- Select your gateway cluster and click New connection
- Provide: connection type (SQL Server, Oracle, etc.), server name, database name, authentication method, and credentials
- Test the connection to verify connectivity
- Grant specific users or security groups permission to use this data source
Credential security: Data source credentials are encrypted with the gateway recovery key before being stored in Azure. Power BI Service never sees the plaintext credentials—only the gateway can decrypt them.
High Availability with Clustering
A single gateway is a single point of failure. If the server goes down, all scheduled refreshes fail until it recovers. For production environments, deploy a gateway cluster:
Cluster Architecture
- Install the Standard Gateway on two or more servers
- During installation of the second (and subsequent) nodes, select "Add to an existing gateway cluster" and provide the cluster name and recovery key
- Power BI automatically distributes refresh requests across cluster members
- If one member goes offline, requests route to remaining members
Load Balancing
By default, gateway clusters distribute requests randomly. For more control:
- Custom load balancing: In the Power BI Admin portal, configure routing preferences to distribute based on server capacity
- Affinity: Route specific data sources to specific gateway members (useful when different servers have network access to different data sources)
Update Strategy
Gateway updates are released monthly. To maintain zero-downtime updates:
- Update one cluster member at a time
- Verify the updated member is healthy before proceeding to the next
- Never update all cluster members simultaneously
- Schedule updates during low-activity windows
Performance Optimization
Refresh Performance Factors
Gateway refresh speed depends on several factors:
| Factor | Impact | Optimization | |---|---|---| | Network bandwidth | Determines data transfer rate | Ensure gigabit connection between gateway and data source | | Query complexity | Complex queries run longer on source | Optimize source queries, add indexes, use stored procedures | | Data volume | Larger datasets take longer to transfer | Implement incremental refresh to reduce data volume per refresh | | Concurrent refreshes | Multiple refreshes compete for resources | Stagger refresh schedules to avoid peak overlap | | Compression | Gateway compresses data before sending to Azure | Adequate CPU ensures fast compression |
Spooling Configuration
The gateway uses a spool directory for temporary data during refresh. By default, this is on the C: drive. For large dataset refreshes, move the spool directory to a fast SSD drive with sufficient space (2x the largest dataset size).
Configure the spool directory in the gateway configuration file: change the `SpooolFilesFolder` setting in the `Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config` file.
Monitoring Gateway Health
- Power BI Admin Portal: View gateway status, last contact time, and cluster health
- Gateway Performance Monitor: Enable performance logging in the gateway configuration to track query execution times, data transfer volumes, and queue depths
- Windows Event Log: The gateway logs errors and warnings to the Windows Application event log
- Gateway log files: Detailed diagnostic logs are stored in the gateway installation directory (default: C:\Program Files\On-premises data gateway\)
Security Best Practices
- Use dedicated service accounts: Run the gateway Windows service under a dedicated Active Directory service account with only the permissions needed to access data sources
- Encrypt all connections: Enable encryption for all data source connections (TLS for SQL Server, SSL for Oracle)
- Rotate credentials quarterly: Update data source credentials in the gateway configuration on a regular schedule
- Restrict admin access: Limit who can administer the gateway to a small team of trusted administrators
- Enable audit logging: Track who adds, modifies, or removes data source connections
- Network segmentation: Place the gateway server in a network segment that can access data sources but is isolated from general user traffic
Related Resources
Frequently Asked Questions
Can I cluster multiple gateways for high availability?
Yes, install multiple gateways in the same cluster for automatic failover and load balancing. This ensures no single point of failure and allows maintenance without downtime.
What network ports does the gateway use?
The gateway uses outbound HTTPS (443) for all communication. No inbound ports need to be opened, making firewall configuration straightforward.