This chapter covers the Power BI Gateway for on-premises data, a critical component for hybrid data scenarios. For the DP-900 exam, understanding the gateway's role, types, and configuration is essential because it enables Power BI to connect to data sources that are not directly accessible from the cloud. Approximately 15-20% of the questions in the analytics domain (Objective 3.4) relate to gateways, data source connections, and refresh scheduling. You will be expected to differentiate between on-premises data gateway and on-premises data gateway (personal mode), know the supported data sources, and understand how the gateway handles security and data refresh.
Jump to a section
Imagine a multinational company with a headquarters in a secure embassy compound. The embassy has strict rules: no external networks can connect directly to internal systems. However, employees need to receive packages from outside. The embassy establishes a mailroom with a single, guarded entrance. A mail clerk is stationed there with a ledger. When a courier arrives with a package for an employee, the clerk records the package details, signs for it, and then carries it inside to the employee's desk. The employee never meets the courier, and the courier never enters the secure area. For outgoing packages, the employee brings the package to the mailroom, the clerk logs it, and hands it to the courier. The mailroom is the only authorized point of contact between the outside world and internal desks. If the mailroom is down, no packages get in or out. The clerk must be trustworthy and always available. This mirrors the Power BI Gateway: it sits in the boundary between on-premises data sources and the cloud, securely relaying queries and data without exposing the internal network directly. The gateway service runs as a Windows service, always listening for requests from the Power BI cloud service. It authenticates, encrypts, and forwards queries to on-premises databases, returning results. Without the gateway, Power BI cannot reach on-premises data because the data sources are not directly accessible from the cloud. The gateway is the only bridge, and it must be installed, configured, and running continuously.
What is the Power BI Gateway for On-Premises Data?
The Power BI Gateway is a software component that acts as a secure bridge between Power BI cloud services (Power BI Service, Power BI Report Server, Power Automate, Power Apps, Azure Logic Apps) and on-premises data sources that are not directly accessible from the cloud. It enables data refresh, live connections (DirectQuery), and scheduled data refreshes for datasets that rely on on-premises data. The gateway is installed on a Windows server within the on-premises network and communicates outbound to Azure Service Bus, eliminating the need to open inbound firewall ports.
Why It Exists
On-premises data sources (e.g., SQL Server, Oracle, SAP HANA, file shares) are often behind firewalls and not exposed to the internet. Power BI, being a cloud service, cannot directly connect to these sources. The gateway solves this by establishing a secure, outbound connection from the on-premises network to the Azure cloud, allowing Power BI to send queries and receive data without compromising security.
Types of Gateways
There are two types of gateways:
On-premises data gateway (standard mode): Used by multiple users, supports multiple data sources, and can be used by multiple Power BI tenants. It runs as a Windows service and is designed for enterprise scenarios. It supports scheduled refresh, DirectQuery, and live connections.
On-premises data gateway (personal mode): Designed for a single user, only works with Power BI, and cannot be shared. It runs as a Windows service under the user's account. It is simpler but less scalable. It supports only scheduled refresh (not DirectQuery).
How It Works Internally
Installation and Registration: The gateway software is installed on a Windows server. During installation, the user signs in with a Power BI account (or Azure AD account) to register the gateway with the Power BI service. This creates a gateway identity in the cloud.
Outbound Connection: The gateway opens a persistent outbound connection to the Azure Service Bus. This connection uses HTTPS (port 443) and is initiated from the gateway to the cloud. No inbound ports are opened on the on-premises firewall.
Data Source Registration: In the Power BI Service, the administrator adds data sources (e.g., SQL Server) and maps them to the gateway. The gateway stores encrypted credentials for each data source.
Query Execution: When a user triggers a refresh or a DirectQuery report runs, the Power BI service sends a request to the gateway via the Service Bus connection. The gateway receives the request, decrypts the credentials, connects to the on-premises data source, executes the query, retrieves the results, and sends them back to the cloud over the same outbound connection.
Security: All communication between the gateway and the cloud is encrypted using TLS. Data source credentials are encrypted using the gateway's encryption key, which is stored in the cloud but never accessible in plaintext. The gateway uses Azure AD for authentication.
Key Components, Values, Defaults, and Timers
Gateway Service: Runs as PBIEgwService (standard) or Power BI Gateway Service (personal). Default startup type is Automatic.
Ports: Outbound HTTPS (443) to Azure Service Bus. Optionally, port 5671 for AMQP (if enabled).
Connection Timeout: Default is 10 minutes for data source queries. Configurable per data source.
Refresh Interval: Scheduled refresh can be set as frequently as every 30 minutes (Power BI Pro) or more (Premium).
Gateway Clusters: Multiple gateways can be grouped into a cluster for high availability. Up to 10 gateways per cluster (standard mode only).
Supported Data Sources: SQL Server, Oracle, SAP HANA, Teradata, Azure SQL Database (though Azure sources typically don't need gateway), file shares, OData feeds, etc. Full list: https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-data-sources
Configuration and Verification Commands
Installation: - Download the gateway from the Power BI service (Download gateway button). - Run the installer with administrative privileges. - Sign in with a Power BI account that has gateway management permissions.
Verification of Gateway Status:
- On the gateway server: Open Services.msc, look for On-premises data gateway service (standard) or Power BI Gateway Service (personal). Ensure status is Running.
- In Power BI Service: Navigate to Settings > Manage gateways. The gateway should appear with a green checkmark.
- Test connection: In the data source settings, click 'Test connection'. A successful test confirms the gateway can reach the data source.
PowerShell Commands (for automation):
# Check gateway service status
Get-Service -Name "PBIEgwService"
# Restart gateway service
Restart-Service -Name "PBIEgwService"
# Get gateway details (not directly available via PowerShell, use REST API)Interaction with Related Technologies
Power BI Service: The gateway is managed from the Power BI Service settings. Data sources are added and credentials are stored there.
Power Automate / Logic Apps: These services can also use the same gateway to connect to on-premises data sources via connectors.
Azure Active Directory: Used for authentication of the gateway administrator and for mapping users to data sources.
Azure Service Bus: The underlying transport mechanism for the gateway's cloud connection.
Performance Considerations
Network Latency: The gateway adds latency because data must travel from on-premises to the cloud. For DirectQuery, this can impact report performance.
Gateway Hardware: The gateway server should have adequate CPU and memory, especially if multiple data sources are queried concurrently.
Data Volume: Scheduled refresh of large datasets can take time. The gateway must remain online during the entire refresh.
Gateway Clustering: For high availability and load balancing, multiple gateways can be clustered. If one gateway fails, another takes over.
Common Issues
Gateway Offline: If the gateway service stops, or the server loses internet connectivity, the gateway shows as offline in the Power BI Service.
Credential Expiry: Data source credentials stored in the gateway may expire. The gateway will fail to connect until credentials are updated.
Firewall Blocks: Outbound connectivity on port 443 must be allowed. Some corporate proxies may require additional configuration.
Timeouts: Large queries may exceed the default 10-minute timeout. This can be increased in the data source settings.
Exam-Relevant Details
The standard gateway supports multiple users and data sources; personal mode supports only one user and one Power BI account.
The gateway does NOT support inbound connections; it only initiates outbound connections.
The gateway is required for on-premises data sources only. Azure data sources (e.g., Azure SQL Database) do not need a gateway.
The gateway can be used for scheduled refresh, DirectQuery, and live connections (tabular models).
Gateway clustering is available only in standard mode.
The gateway must be installed on a machine that can access both the internet and the on-premises data source.
For personal mode, the user must be the same account that installed the gateway.
Summary of Key Technical Points
The gateway uses Azure Service Bus for communication.
It runs as a Windows service.
It requires outbound HTTPS (443).
Data source credentials are encrypted and stored in the cloud.
Multiple gateways can form a cluster for high availability.
The personal mode gateway cannot be shared and does not support DirectQuery.
Example Scenario
A company has an on-premises SQL Server database that contains sales data. The Power BI team wants to create a dashboard that refreshes every hour. They install the standard gateway on a server that can reach the SQL Server and has internet access. In the Power BI Service, they add the SQL Server as a data source under the gateway, providing credentials. They then create a dataset that points to this data source and set a scheduled refresh every 60 minutes. The gateway handles the refresh by querying the SQL Server and sending the data to Power BI.
Download and Install Gateway
Navigate to the Power BI Service settings, click 'Manage gateways', then 'Download gateway'. Choose the appropriate version: standard for enterprise, personal for single-user. Run the installer as administrator on a Windows server that has network access to both the on-premises data source and the internet (outbound HTTPS). During installation, you will sign in with a Power BI account (or Azure AD) to register the gateway. The installer configures the Windows service (PBIEgwService for standard, 'Power BI Gateway Service' for personal) and sets it to start automatically. After installation, the gateway appears in the Power BI Service under 'Manage gateways'.
Configure Firewall and Network
Ensure the gateway server can make outbound HTTPS connections to *.powerbi.com and *.servicebus.windows.net on port 443. No inbound ports are required. If the corporate network uses a proxy, configure the gateway to use the proxy by editing the `Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config` file (for standard gateway) or the corresponding config file. The gateway also uses port 5671 for AMQP if enabled, but this is optional. Verify connectivity by running a network connectivity test from the gateway machine using a tool like Test-NetConnection.
Register Gateway in Power BI Service
After installation, the gateway registers itself with the Power BI service using the credentials provided during setup. In the Power BI Service, navigate to Settings > Manage gateways. You should see the gateway listed with a status of 'Online'. If it shows 'Offline', check the service status on the gateway machine and network connectivity. The gateway's name and recovery key (if set) are displayed. The recovery key is used to recover the gateway if the configuration is lost. Store it securely.
Add Data Sources to Gateway
In the Power BI Service, under the gateway's settings, click 'Add data source'. Select the data source type (e.g., SQL Server, Oracle, File). Provide the server name, database name, and authentication method (Windows, Basic, OAuth). Enter credentials that have access to the data source. These credentials are encrypted and stored in the cloud. The gateway uses them to connect to the on-premises source. For Windows authentication, the gateway service account must have access to the data source, or you can use a stored credential. Test the connection to ensure the gateway can reach the data source.
Create Dataset and Set Refresh Schedule
In Power BI Desktop, create a report using the on-premises data source. When publishing to the Power BI Service, you will be prompted to map the data source to the gateway. After publishing, go to the dataset settings in the Power BI Service, expand 'Gateway connection', and select the gateway and data source. Then set a scheduled refresh under 'Scheduled refresh'. The minimum refresh interval depends on your license: 30 minutes for Pro, more frequent for Premium. The gateway will execute the refresh at the scheduled times, querying the on-premises data source and uploading the data to Power BI.
Scenario 1: Corporate Sales Dashboard with On-Premises SQL Server
A multinational retail company uses an on-premises SQL Server for its transactional sales data. The BI team needs to provide near-real-time dashboards to executives via Power BI. They deploy a standard gateway on a dedicated Windows server in the same subnet as the SQL Server. The gateway is configured with a cluster of two gateways for high availability. The data source is added using SQL Server authentication. The dataset uses DirectQuery to allow live reporting without data duplication. However, DirectQuery queries can be slow because each report interaction triggers a query through the gateway. To mitigate, the team optimizes SQL Server indexes and uses aggregations. They also set a scheduled refresh for a separate import dataset that powers historical analytics. The gateway handles both workloads without issues. When one gateway fails, the second takes over automatically. Key lesson: For DirectQuery, ensure the gateway server has low latency to the data source and sufficient CPU to handle concurrent queries.
Scenario 2: Small Business Financial Reports with Personal Gateway
A small accounting firm uses Power BI to create monthly financial reports from an on-premises Excel file on a shared drive. The accountant installs the personal gateway on their own Windows laptop. The gateway runs under their user account. They add the Excel file as a data source using Windows authentication. The dataset is set to refresh daily at 2 AM. However, the laptop must be on and connected to the internet at that time. If the accountant leaves the office, the refresh fails. This is a common pitfall: personal gateways are tied to the user's machine and account. The firm later upgrades to a standard gateway installed on a server that stays on 24/7, solving the reliability issue.
Scenario 3: Hybrid Architecture with Multiple Data Sources
A healthcare organization has data spread across on-premises Oracle, SQL Server, and file shares. They also use Azure SQL Database for some cloud data. They install a single standard gateway on a server that can reach all on-premises sources. They add multiple data sources under the same gateway. Power BI datasets can combine cloud and on-premises data, but the gateway is only used for the on-premises part. They configure a scheduled refresh every hour. During peak hours, the gateway server CPU spikes to 80%. They add a second gateway to the cluster to distribute the load. They also notice that the gateway's default query timeout is 10 minutes, which is insufficient for some complex queries. They increase it to 20 minutes per data source. The gateway cluster provides resilience: if one gateway goes down for maintenance, the other continues serving requests.
DP-900 Exam Focus on Power BI Gateway
The DP-900 exam tests your understanding of the gateway's purpose, types, and configuration. Objective 3.4 specifically covers 'Analytics' and includes questions about connecting to on-premises data. You should be able to:
Identify scenarios where a gateway is required (on-premises data sources) vs. not required (Azure data sources).
Differentiate between standard and personal mode gateways.
Understand that the gateway uses outbound connections only.
Know that the gateway supports scheduled refresh, DirectQuery, and live connections.
Recognize that the gateway is installed on a Windows server.
Common Wrong Answers and Why Candidates Choose Them
'The gateway requires inbound firewall ports to be opened.' Many candidates think the gateway needs inbound access because it 'bridges' on-premises and cloud. In reality, the gateway only initiates outbound connections to Azure Service Bus. No inbound ports are needed.
'The personal mode gateway can be shared with multiple users.' Candidates often assume the personal mode is just a simpler version of the standard gateway. However, personal mode is tied to a single user account and cannot be shared. The standard gateway supports multiple users and data sources.
'The gateway can connect to Azure SQL Database without additional configuration.' While the gateway can technically connect to Azure SQL Database, it is unnecessary because Azure SQL Database is already in the cloud and accessible directly. The exam tests that you know the gateway is for on-premises sources only.
'The gateway supports both inbound and outbound connections.' This is false; the gateway only uses outbound connections to ensure security.
Specific Numbers and Terms That Appear on the Exam
Port 443: The only port required for outbound communication.
Azure Service Bus: The underlying technology for cloud connectivity.
Windows service: The gateway runs as a Windows service (PBIEgwService).
Scheduled refresh: Minimum 30 minutes for Pro, but the exam may ask about refresh capabilities.
Gateway cluster: Up to 10 gateways in a cluster (standard mode).
Edge Cases and Exceptions
The personal gateway does NOT support DirectQuery. Only scheduled refresh is supported.
The standard gateway supports both scheduled refresh and DirectQuery.
The gateway cannot be used to connect to cloud-only data sources like Azure Blob Storage (unless they are behind a firewall).
The gateway can be used by other services like Power Automate and Azure Logic Apps.
How to Eliminate Wrong Answers
If the question mentions sharing the gateway with a team, the answer must be the standard gateway.
If the question mentions DirectQuery, the personal gateway is not an option.
If the question mentions inbound ports, that answer is incorrect.
If the question involves connecting to an on-premises SQL Server, a gateway is required.
If the question mentions a single user and personal computer, the personal gateway might be appropriate, but remember its limitations.
The Power BI Gateway is required for connecting to on-premises data sources that are not directly accessible from the cloud.
The standard gateway supports multiple users, DirectQuery, and clustering; the personal mode supports only one user and scheduled refresh.
The gateway uses outbound HTTPS (port 443) to connect to Azure Service Bus; no inbound ports are opened.
Data source credentials are encrypted and stored in the cloud; the gateway decrypts them when needed.
The gateway runs as a Windows service (PBIEgwService for standard).
The personal mode gateway cannot be shared and does not support DirectQuery.
Gateway clustering provides high availability and load balancing for enterprise scenarios.
The gateway is not needed for Azure data sources like Azure SQL Database.
Scheduled refresh can be as frequent as every 30 minutes for Power BI Pro.
The gateway can also be used by Power Automate and Azure Logic Apps for on-premises connectivity.
These come up on the exam all the time. Here's how to tell them apart.
On-premises data gateway (standard)
Supports multiple users and data sources
Supports scheduled refresh, DirectQuery, and live connections
Can be used by multiple Power BI tenants
Supports clustering for high availability (up to 10 gateways)
Runs as a Windows service under a dedicated service account
On-premises data gateway (personal mode)
Supports only one user (the installer)
Supports only scheduled refresh (no DirectQuery or live connections)
Works only with Power BI (not other services like Power Automate)
No clustering support
Runs as a Windows service under the user's account
Mistake
The gateway requires inbound firewall ports to be opened.
Correct
The gateway only initiates outbound connections to Azure Service Bus on port 443. No inbound ports are required, making it secure and easy to deploy in locked-down networks.
Mistake
The personal mode gateway can be shared by multiple users.
Correct
The personal mode gateway is tied to a single user account and can only be used by that user. It cannot be shared. The standard gateway supports multiple users and data sources.
Mistake
The gateway is necessary for all Power BI data sources.
Correct
The gateway is only needed for on-premises data sources that are not directly accessible from the cloud. Azure data sources like Azure SQL Database do not require a gateway.
Mistake
The gateway supports both inbound and outbound connections.
Correct
The gateway only uses outbound connections. It never listens for inbound connections. This ensures that the on-premises network remains secure.
Mistake
The personal gateway supports DirectQuery.
Correct
The personal gateway does not support DirectQuery. It only supports scheduled refresh. DirectQuery requires the standard gateway.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
No, you do not need a gateway for Azure SQL Database because it is a cloud service that is directly accessible from Power BI. The gateway is only required for on-premises data sources that are behind a firewall and not directly accessible from the cloud.
The standard gateway is designed for enterprise use: it supports multiple users, multiple data sources, DirectQuery, and clustering for high availability. The personal mode gateway is for a single user, supports only scheduled refresh (not DirectQuery), and cannot be shared. The personal mode runs under the user's account, while the standard runs as a dedicated Windows service.
Yes, the standard gateway can be used by Power Automate (and Azure Logic Apps) to connect to on-premises data sources. You need to add the data source under the gateway in Power Automate's admin settings. The personal mode gateway does not support this.
For Power BI Pro, the minimum refresh interval is 30 minutes. For Power BI Premium, you can schedule up to 48 refreshes per day (every 30 minutes) or use XMLA endpoints for more frequent refreshes. The gateway itself does not impose additional limits.
If the gateway server is offline, the gateway appears as 'Offline' in the Power BI Service. Scheduled refreshes will fail, and DirectQuery reports will not load. To avoid this, use a gateway cluster (standard mode) for high availability, or ensure the server is always on and connected.
It is not recommended to install the gateway on a domain controller due to security best practices and potential performance issues. The gateway should be installed on a dedicated Windows server or a member server that has access to both the data source and the internet.
The gateway requires outbound HTTPS on port 443 to *.powerbi.com and *.servicebus.windows.net. Optionally, it can use port 5671 for AMQP if enabled. No inbound ports are required.
You've just covered Power BI Gateway for On-Premises Data — now see how well it sticks with free DP-900 practice questions. Full explanations included, no account needed.
Done with this chapter?