Course: Microsoft Power BI Training
Topic: Understanding On-Premises Data Gateway
Course Link: Microsoft Power BI Training – JustAcademy
Demo Link: Register for a Course Demo
JustAcademy Website: Visit JustAcademy
Power BI is a business intelligence platform used to connect data sources, transform information, create interactive reports, and share business insights. In many organizations, data is stored on local computers, internal servers, or databases located inside a private company network. The Power BI service runs in the cloud and may not be able to access these internal data sources directly.
The On-premises data gateway provides a secure communication bridge between supported cloud services, such as the Power BI service, and data sources that are located inside an organization's network or otherwise require gateway connectivity.
For example, a company may store sales information in a SQL Server database on its office network. A Power BI developer creates a report using that database and publishes it to the Power BI service. When the service needs to refresh the report's data or execute supported queries, the gateway enables communication with the internal database without requiring the database to be exposed directly to the public internet.
An on-premises data gateway is software installed on a supported Windows computer or server. It securely transfers requests and data between supported cloud services and data sources that the gateway can reach.
The gateway can support scenarios such as:
A cloud service cannot automatically access every database, file server, or application inside a private network. Network firewalls and authentication requirements often prevent direct external connections.
The gateway provides a controlled connection path so that authorized cloud services can access supported data without requiring organizations to make their internal data sources publicly accessible.
Before learning how a gateway works, it is important to understand the difference between on-premises data and cloud data.
On-premises data is information stored on infrastructure that an organization manages locally or within its private network. This may include physical servers, office computers, private-network virtual machines, and internal file servers.
Examples include:
Cloud data is hosted in a cloud service or environment that can be accessed through supported network connections and authentication methods.
Examples include:
Not every cloud source requires a gateway. A gateway may still be necessary for some cloud-hosted sources when network restrictions, private connectivity, connector requirements, or other architectural conditions make it necessary.
| Feature | On-Premises Data | Cloud Data |
|---|---|---|
| Storage location | Local infrastructure or a private network. | Cloud-hosted infrastructure or services. |
| Infrastructure management | Often managed by the organization or its IT provider. | Usually managed partly or fully by the cloud provider. |
| Accessibility | May require internal network access or a gateway. | May be directly accessible through a supported cloud connector. |
| Security controls | Internal firewalls, network controls, and authentication. | Cloud identity, permissions, network policies, and authentication. |
| Power BI connectivity | Often requires a gateway for cloud refresh or query scenarios. | May connect directly, depending on the source and configuration. |
The gateway establishes communication between Power BI and supported data sources. It runs on a machine that can access the data source and communicate with the required Microsoft cloud services.
Internal Data Source
(SQL Server / Network File / Other Supported Source)
|
v
On-Premises Data Gateway
(Windows Machine)
|
| Secure Outbound Communication
v
Power BI Service
|
v
Semantic Model and Reports
|
v
Report ConsumersThis diagram represents a simplified flow. Actual communication depends on the data source, connector, storage mode, authentication method, and gateway configuration.
The gateway normally initiates outbound connections to the required Microsoft cloud services. As a result, the standard communication pattern does not require opening inbound ports to the organization's internal network for the gateway to receive cloud requests.
However, network administrators must configure outbound connectivity according to Microsoft's current gateway network requirements. Proxy servers, firewalls, DNS settings, TLS inspection, and organizational security policies can affect connectivity.
Power BI supports three gateway options: the on-premises data gateway in standard mode, the on-premises data gateway in personal mode, and the virtual network data gateway.
The standard-mode gateway is designed for shared and centrally managed use. Multiple authorized users can use its configured connections, and gateway administrators can manage data source connections and permissions.
Standard mode is suitable for:
The personal-mode gateway is intended for an individual Power BI user who needs to refresh their own imported semantic models from supported on-premises sources. It is managed by the individual rather than through centrally shared gateway connections.
Personal mode is suitable for:
Personal mode does not support the same capabilities as standard mode. It does not support DirectQuery, live connections to Analysis Services, or gateway clustering and high availability.
A virtual network data gateway is a Microsoft-managed gateway service used to connect supported cloud analytics services to data sources protected within a virtual network. Unlike an on-premises gateway, it does not require the organization to install and maintain gateway software on a local Windows machine.
VNet gateways may be appropriate when:
| Feature | Standard Mode | Personal Mode | VNet Gateway |
|---|---|---|---|
| Installation | Installed on a supported Windows machine. | Installed on a supported Windows machine. | Microsoft-managed service. |
| Multiple users | Yes, through managed connections and permissions. | No, intended for an individual user. | Supports eligible service connections according to configuration. |
| Centralized administration | Yes. | Limited. | Managed through supported cloud configuration. |
| Scheduled import refresh | Yes. | Yes. | Supported scenarios depend on the source and service configuration. |
| DirectQuery | Yes, for supported sources and scenarios. | No. | Supported scenarios depend on the source and configuration. |
| High availability cluster | Yes. | No. | Uses its managed service architecture rather than a local gateway cluster. |
| Typical use | Shared departmental or enterprise BI. | Individual Power BI refresh. | Supported sources protected by a virtual network. |
Recommendation: For most shared business reporting solutions that require access to on-premises data, standard mode is generally the preferred option. Select the gateway type according to the architecture, data source, security needs, and supported features.
A gateway is generally required when Power BI or another supported cloud service needs to access a data source that is not directly accessible through a supported cloud connection.
Always evaluate the source type, network configuration, connector, and connection mode before deciding whether a gateway is necessary.
Installing the gateway is an important step in establishing connectivity between Power BI and internal data sources. For business environments, the installation should be planned with the IT or BI administration team.
Check Microsoft's current gateway installation documentation for supported Windows versions, software dependencies, and system requirements before installing the gateway.
Official download page: Download the Power BI Gateway.
Gateway names should make their purpose and environment clear.
Examples:
For clustered deployments, use a consistent naming convention that identifies the cluster and individual nodes.
Installing the gateway software does not automatically make every data source available to Power BI. The gateway must be registered, and the required data source connections must be configured and authorized.
Suppose the organization stores sales data in a SQL Server database named SalesDB on a server called SQLSERVER01.
| Setting | Example Value |
|---|---|
| Data source type | SQL Server |
| Server | SQLSERVER01 |
| Database | SalesDB |
| Authentication | An approved authentication method supported by the connector |
| Gateway | PowerBI-Gateway-Production |
These values are illustrative. Use the actual server name, database name, and authentication settings provided by the database administrator.
Power BI Desktop is commonly used to connect to data sources, transform data, create semantic models, and design reports. When a report is published, the corresponding service-side model must be configured to access its source through the appropriate gateway when required.
After publishing, the semantic model in the Power BI service must be associated with the correct gateway connection when the source requires one. The source information used by the model must match the configured connection details.
For example, a model that connects to SQLSERVER01 and SalesDB may not map to a gateway connection configured with a different server name or database. Consistent source names and connection settings help avoid mapping and refresh errors.
Power BI supports different data connectivity modes. The selected mode affects how data is retrieved, stored, and updated, as well as how the gateway is used.
In Import mode, Power BI loads data into the semantic model. Users interact with the imported data, and the model must be refreshed to retrieve subsequent changes from the source.
The gateway may be used to refresh imported data from on-premises sources.
Example: A company imports sales transactions from SQL Server every morning at 6:00 AM. The gateway allows the Power BI service to reach the database during the scheduled refresh.
In DirectQuery mode, supported report interactions can send queries to the underlying data source rather than relying entirely on a fully imported copy of the data.
When the data source requires a gateway, the gateway can relay supported queries between the Power BI service and the source.
Example: A sales manager opens a report and filters sales by region. Depending on the model and query, Power BI can send a query to the underlying database to retrieve the required results.
| Feature | Import Mode | DirectQuery Mode |
|---|---|---|
| Data storage | Data is loaded into the semantic model. | Data remains primarily in the source for supported queries. |
| Data freshness | Depends on the refresh schedule or manual refresh. | Depends on source data, query behavior, caching, and model configuration. |
| Gateway use | May be required for refresh from an internal source. | May be required for supported queries against an internal source. |
| Performance factors | Model size, refresh time, and report design. | Source performance, query complexity, network latency, and concurrency. |
| Typical use | Reporting that benefits from imported data and scheduled updates. | Reporting that needs supported source-query behavior or source-level data access. |
Scheduled refresh updates an imported semantic model by retrieving current data from its source. If the source is on-premises and requires a gateway, the gateway must be online and correctly configured for the refresh to succeed.
| Report | Data Source | Refresh Schedule | Business Purpose |
|---|---|---|---|
| Daily Sales Report | SQL Server | Every business morning | Review recent sales performance. |
| Inventory Report | Internal inventory database | Several times per day, if required and supported | Monitor stock availability. |
| Monthly Finance Report | Finance database | After the monthly close process | Review finalized financial results. |
Refresh frequency should be based on data requirements, available capacity, source-system workload, and applicable Power BI limits. Avoid unnecessary refreshes that increase database load without improving business decisions.
A standard-mode gateway can be managed centrally. Gateway roles determine who can administer the gateway or use its configured connections.
Gateway administrators can manage and update the gateway and perform supported administrative tasks. They should be trusted technical personnel who understand the organization's data security and network requirements.
Standard-mode gateway connections can have their own access permissions. Depending on the connection and interface, users may be able to manage a connection or use it to connect to a data source.
A person who only needs to use an approved gateway connection does not necessarily need to be a gateway administrator.
Gateways connect cloud services to internal data sources, so their configuration must be managed carefully. Poorly managed gateway access can expose sensitive information or interrupt business-critical reporting.
Grant gateway administration rights only to people who need to manage the gateway. Grant connection usage rights to users who need to access the approved source. Do not assume that every report consumer requires gateway administration access.
Follow Microsoft's documented network requirements and the organization's firewall policies. Configure outbound connectivity as required and avoid exposing internal databases directly to the internet merely to make Power BI connectivity easier.
A gateway cluster consists of multiple standard-mode gateway installations configured to work together. Clustering can improve availability and help distribute workloads, reducing dependence on a single gateway machine.
Power BI Service
|
v
Gateway Cluster
/ \
v v
Gateway Node 1 Gateway Node 2
\ /
\ /
v v
Internal Data SourceThis is a simplified conceptual diagram. Cluster behavior, workload distribution, failover, and configuration depend on the gateway setup and current Microsoft guidance.
Clustering is useful when a gateway supports important reports or operational processes that cannot tolerate a single machine becoming unavailable. A small personal reporting project may not need a cluster, but a large organization with business-critical refreshes may benefit from one.
Gateway performance affects refresh duration, DirectQuery response times, and the overall reliability of reports that depend on the source.
| Problem | Possible Cause | Suggested Solution |
|---|---|---|
| Gateway appears offline. | The gateway service is stopped, the machine is shut down, or network connectivity is blocked. | Check machine availability, gateway service status, and outbound network access. |
| Scheduled refresh fails. | Credentials are invalid, the source is unreachable, or the gateway mapping is incorrect. | Verify credentials, connection settings, source availability, and model-to-gateway mapping. |
| Power BI cannot find the gateway. | The gateway is registered to a different account or tenant, or the user lacks access. | Confirm gateway registration, tenant, and permissions. |
| Data source connection test fails. | Incorrect server name, database name, authentication, firewall settings, or missing driver. | Test connectivity from the gateway machine and correct the source configuration. |
| Refresh is slow. | Large data volume, complex transformations, limited resources, or a slow source database. | Optimize queries, reduce unnecessary data, review resource usage, and investigate source performance. |
| Gateway service cannot start. | Operating system, service account, software, or machine configuration issue. | Check service status, system logs, installation health, and supported configuration requirements. |
| DirectQuery requests fail. | The gateway or source may be unreachable, the model mapping may be incorrect, or the source may reject queries. | Check the gateway connection, source permissions, network path, and database logs. |
| Credentials are repeatedly requested. | Credentials changed, authentication failed, or the saved connection settings are invalid. | Update credentials in the appropriate gateway connection or semantic model settings. |
| Gateway performance is inconsistent. | Resource contention, concurrent workloads, network instability, or outdated software. | Monitor resource utilization, update the gateway, and evaluate workload distribution. |
| Network file cannot be accessed. | The gateway service identity lacks access to the file or share. | Verify the path and permissions using the identity under which the gateway accesses the source. |
A gateway requires ongoing maintenance to remain secure, compatible, and reliable. Standard-mode gateway software is updated periodically, and administrators should plan for updates instead of leaving installations outdated.
Updates can provide fixes, compatibility improvements, and security enhancements. An outdated gateway may also cause inconsistencies between the local transformation environment and the cloud service.
Before updating a business-critical gateway, follow the organization's change-management process and confirm that the appropriate support and recovery arrangements are in place.
Gateway recovery procedures help administrators restore gateway functionality after machine failure, replacement, or other operational incidents.
When registering a standard-mode gateway, administrators create a recovery key. The key is used in supported gateway recovery or migration operations and helps protect the gateway's configuration and credentials.
The recovery key should be stored securely in an approved password vault or another protected location accessible to authorized backup administrators.
A retail company stores its sales transactions in a SQL Server database hosted on its internal network. Management wants a Power BI report that displays daily sales, monthly revenue, product performance, and regional comparisons.
The Power BI service can access the internal SQL Server data through the configured gateway for supported refresh or query operations. Authorized users can view the published report, and imported data can be updated according to the configured refresh schedule.
An organization may store operational data in an Excel workbook on an internal file server. If the workbook must remain on that internal network and the Power BI service needs to refresh data from it, a gateway may be required.
If the workbook can be moved to SharePoint Online or OneDrive for work or school, a supported cloud connection may remove the need for a gateway. The correct approach depends on the organization's storage, security, and governance requirements.
Organizations should plan gateway deployment before using it for important reporting solutions. Planning helps avoid unnecessary installations, performance problems, unclear ownership, and access-control risks.
The gateway should normally be installed on a machine that is close enough to the data source to provide reliable connectivity and low network latency. It should also meet the organization's security, availability, and data residency requirements.
For business-critical solutions, a dedicated and always-on virtual machine or server is often more appropriate than a personal employee computer.
| Misconception | Correct Explanation |
|---|---|
| The gateway stores all the business data. | The gateway provides connectivity and data transfer. In Import mode, the semantic model stores imported data in the Power BI service. |
| Installing the gateway automatically configures every data source. | Each required connection and its authentication must be configured and authorized. |
| Every Power BI report requires a gateway. | Only scenarios that require gateway connectivity need one. Many supported cloud sources connect directly. |
| Personal mode is best for enterprise reporting. | Standard mode is generally more suitable for shared and centrally managed business reporting. |
| Sharing a report requires giving everyone gateway administrator access. | Report access and gateway administration are separate. Consumers do not normally need gateway administration rights. |
| The gateway always provides real-time data. | Data freshness depends on the connectivity mode, source behavior, refresh configuration, and query execution. |
| Opening inbound ports is always required. | The gateway normally uses outbound communication, although required network endpoints and firewall rules must still be configured. |
| A successful desktop connection guarantees service refresh will work. | The service also requires correct gateway mapping, credentials, source accessibility, and applicable permissions. |
A company has one analyst who refreshes a personal report and another team that manages reports for 100 employees. Decide which gateway type is appropriate for each scenario and explain why.
Prepare a gateway configuration checklist for a Power BI semantic model that connects to an internal SQL Server database. Include server details, authentication, gateway mapping, and testing.
A report refresh works in Power BI Desktop but fails in the Power BI service. Create a troubleshooting checklist covering gateway status, credentials, source accessibility, and connection mapping.
Design a gateway security policy that defines gateway administrators, connection users, recovery-key storage, and access-review responsibilities.
A company depends on several important reports that refresh throughout the business day. Explain how a standard-mode gateway cluster may improve availability and what should be considered before implementation.
An Excel workbook is stored on an internal network drive. Compare keeping the file on the network and using a gateway with moving it to an approved SharePoint Online location and using a supported cloud connection.
Answer: It is software installed on a supported Windows machine that provides a secure communication bridge between the Power BI service and supported data sources that are located on an internal network or otherwise require gateway connectivity.
Answer: A gateway allows supported cloud services to access internal data sources for refresh or query operations without requiring those sources to be directly exposed to the public internet.
Answer: On-premises data gateway in standard mode, on-premises data gateway in personal mode, and virtual network data gateway.
Answer: Standard mode supports shared connections, centralized management, DirectQuery, supported live connections, and clustering. Personal mode is intended for an individual user refreshing their own imported semantic models and has more limited functionality.
Answer: No. The gateway provides connectivity and transfers data or query requests. Imported data is stored in the Power BI semantic model, while DirectQuery uses supported queries against the source.
Answer: The recovery key is used for supported recovery or migration operations involving a standard-mode gateway. It must be stored securely and made available to authorized administrators.
Answer: It is a configured connection that specifies how the gateway accesses a particular data source, including source details, authentication, and relevant settings.
Answer: When an imported semantic model uses an on-premises source that requires a gateway, the gateway provides the service with a path to retrieve updated data during scheduled or manual refresh.
Answer: Yes. Standard mode supports DirectQuery for supported data sources and scenarios that require gateway connectivity. Personal mode does not support DirectQuery.
Answer: A gateway cluster is a group of standard-mode gateway installations configured together to improve availability and support workload distribution in supported scenarios.
Answer: Check the gateway machine, gateway service, network connectivity, required outbound access, and relevant gateway status information.
Answer: The service may have incorrect gateway mapping, credentials, permissions, source paths, or network access. Desktop connectivity alone does not guarantee service-side refresh will succeed.
Answer: No. Many supported cloud data sources connect directly. A gateway is needed only when the source or architecture requires it.
Answer: Use sufficient machine resources, keep the gateway updated, optimize source queries and transformations, reduce unnecessary data, minimize network latency, and monitor concurrent workloads.
Answer: The gateway connects cloud services to internal sources. Restricting administrative access, protecting credentials, securing recovery keys, and monitoring the gateway help protect organizational data.
The On-premises data gateway is an important component of Power BI architecture when cloud-based reporting solutions need to connect to supported data sources inside private networks. It provides a controlled communication path that helps organizations refresh imported semantic models, execute supported DirectQuery operations, and maintain connectivity to internal business data.
Understanding gateway types, installation, source configuration, scheduled refresh, access permissions, security, performance, and troubleshooting is essential for building reliable Power BI solutions. For shared organizational reporting, standard mode is generally the preferred starting point, while personal mode and VNet gateways serve different requirements.
A well-managed gateway deployment should include secure installation, limited administration, protected credentials and recovery keys, regular updates, performance monitoring, and a documented recovery plan. These practices help ensure that Power BI reports remain reliable, secure, and available to authorized users.
