Using Gateways — Use gateways for on-prem data refresh (conceptual) - Microsoft Power Bi

SECTION 1

Historical Context & Motivation

The rise of cloud-based business intelligence platforms introduced a fundamental architectural tension: organizations had invested decades in building robust on-premises data infrastructure — SQL Server instances, Oracle databases, file shares, and proprietary data warehouses — yet the analytics layer was migrating to the cloud. Microsoft recognized early that a wholesale "lift and shift" of enterprise data was neither practical nor desirable for most organizations. Security policies, regulatory compliance (HIPAA, GDPR, SOX), and sheer data volume made full cloud migration a multi-year endeavor. The on-premises data gateway emerged as the bridge that would let organizations keep their data where it was while still leveraging Power BI's cloud-hosted reporting and collaboration features.

2013 • Power BI Preview & Early Data Management Gateway

Microsoft launched Power BI as an Office 365 add-on with a rudimentary Data Management Gateway to support hybrid data access for Power Query. This first iteration required manual configuration per data source and lacked centralized management.

2016 • On-Premises Data Gateway (Standard Mode)

With Power BI's general availability, Microsoft released the on-premises data gateway in standard (enterprise) mode, supporting centralized management, multiple data sources per gateway, and gateway clustering for high availability.

2018 • Personal Mode & VNet Gateways Introduced

Microsoft added a lightweight personal mode gateway for individual users, along with early support for Azure Virtual Network (VNet) data gateways that eliminated the need for a physical machine on premises.

2021 • VNet Data Gateway GA & Unified Gateway Management

VNet data gateways reached general availability, enabling organizations with Azure-hosted databases to bypass on-prem gateway installations entirely. The Power Platform admin center became the unified management surface for all gateway types.

2023–Present • Fabric Integration & Managed Connections

Microsoft Fabric's emergence brought tighter integration with gateway infrastructure, including managed private endpoints and streamlined credential management through the Fabric admin portal.

The core question the gateway addresses is deceptively simple: How does a cloud service securely query a database that is not exposed to the public internet? The answer involves outbound-only encrypted connections, relay-based communication channels, and a carefully orchestrated credential management scheme — concepts we will unpack throughout this lesson.

SECTION 2

Core Principles & Definitions

Before diving into architecture details, it is essential to establish the foundational concepts that govern gateway behavior. A data gateway in the Power BI ecosystem is a software agent installed on a machine within your network that acts as a secure conduit between on-premises data sources and the Power BI service in the cloud. It does not move data permanently into the cloud; rather, it facilitates on-demand query execution and scheduled data refresh operations. Understanding gateways requires grasping several interrelated concepts.

Outbound-Only Connections

The gateway initiates all connections outbound to Azure Service Bus via HTTPS (port 443). No inbound firewall rules are required, which dramatically reduces the attack surface compared to opening database ports to the internet.

Encrypted Credential Store

Data source credentials are encrypted using an asymmetric key pair generated during gateway installation. The private key exists only on the gateway machine; the Power BI service holds the public key and the encrypted credentials. This means Microsoft never has access to your plaintext database passwords.

Standard vs. Personal Mode

Standard (enterprise) mode supports multiple users and centralized administration. Personal mode is restricted to a single user and cannot share data sources. Think of standard mode as a departmental server and personal mode as a developer workstation.

Gateway Cluster (High Availability)

Multiple standard-mode gateway installations can be grouped into a cluster. The Power BI service load-balances refresh requests across cluster members and automatically fails over if one node becomes unresponsive, providing enterprise-grade availability.

Scheduled Refresh vs. DirectQuery

Gateways support two data access patterns: scheduled refresh (import mode) pulls data into the Power BI dataset on a schedule, while DirectQuery forwards each user query through the gateway to the on-premises source in real time.

✦

KEY TAKEAWAY

Think of the on-premises data gateway as a reverse SSH tunnel for BI data. Just as an SSH tunnel allows you to securely expose a local service to a remote machine without opening inbound ports, the gateway establishes an outbound encrypted channel to Azure Service Bus. The cloud service drops query requests into this channel, and the gateway picks them up, executes them against local databases, and returns the results — all without your database ever being directly addressable from the internet.

SECTION 3

Visual Explanation — Gateway Architecture

The following diagram illustrates the end-to-end data flow when a Power BI dataset configured in import mode triggers a scheduled refresh through the on-premises data gateway. Pay close attention to the direction of arrow connections — all gateway-initiated traffic is outbound.

The numbered steps show the refresh lifecycle: ① Power BI triggers a refresh, ② the relay notifies the gateway via its persistent outbound connection, ③ the gateway decrypts stored credentials using its local private key, ④ queries execute against on-premises sources, and ⑤ results flow back through the relay to the Power BI dataset.

Notice that the on-premises network boundary (right side) never exposes any inbound ports. The gateway maintains a persistent outbound WebSocket connection to Azure Service Bus, which the Power BI service uses to relay commands. This is conceptually similar to how a WebSocket-based chat client connects outbound to a message broker, then receives messages pushed back over the same channel. The credential flow (step ③) is particularly important from a security standpoint: the encrypted credential blob travels from the cloud to the gateway, where the gateway's locally stored RSA private key decrypts it. At no point does a plaintext password traverse the network.

SECTION 4

How It Works — Communication & Security Internals

The gateway's communication model is built atop Azure Service Bus Relay, which acts as a cloud-hosted message broker. When the gateway Windows service starts, it authenticates with Azure Active Directory (now Entra ID) and establishes a persistent TLS 1.2+ WebSocket connection to a Service Bus relay namespace. This connection is always outbound from the gateway — the gateway polls the relay for pending work items. When the Power BI service needs to execute a refresh or DirectQuery operation, it places a message on the relay, which the gateway picks up within seconds.

Credential Encryption Scheme

During gateway installation, a 2048-bit RSA key pair is generated. The public key is uploaded to the Power BI service; the private key remains on the gateway machine, stored in the Windows DPAPI-protected credential store. When an administrator configures a data source in the Power BI service and provides a username/password, the service encrypts those credentials with the gateway's public key before persisting them. Only the gateway can decrypt them.

CREDENTIAL ENCRYPTION

C = E(PUB_gw, plaintext_credentials)

Where C is the ciphertext stored in the Power BI service, E is RSA-OAEP encryption, and PUB_gw is the gateway's public key. Decryption occurs only on the gateway: plaintext = D(PRIV_gw, C).

Transport Layer Security

All data in transit between the gateway and the Power BI service is encrypted via TLS 1.2 (minimum). The gateway also supports an optional secondary encryption layer using AES-256 symmetric keys negotiated per session between the gateway agent and the cloud endpoint. This defense-in-depth approach ensures that even if TLS termination occurs at a corporate proxy, the inner payload remains encrypted. The combination of asymmetric encryption for credentials and symmetric encryption for data transport mirrors the hybrid approach used in protocols like TLS itself — use slow asymmetric crypto for key exchange, fast symmetric crypto for bulk data.

Refresh Scheduling & Throttling

The Power BI service orchestrates refresh scheduling. A Pro license allows up to 8 refreshes per day per dataset; Premium workspaces allow 48. When a scheduled refresh triggers, the service enqueues a refresh job targeting the gateway cluster. The gateway picks up the job, retrieves and decrypts the relevant credentials, executes the data source queries (e.g., SQL, OData, ODBC), compresses the result set, and transmits it back to Power BI for ingestion into the in-memory Analysis Services (VertiPaq) engine. If the refresh fails — due to network issues, credential expiry, or gateway downtime — the Power BI service retries and eventually notifies the dataset owner via email.

SECTION 5

Detailed Breakdown — Gateway Modes & Deployment Options

Microsoft provides three distinct gateway deployment models, each targeting a different use case. Understanding when to use each is critical for both Power BI certification exams and real-world enterprise deployments. The following diagram and table break down the key differences.

Comparison of the three gateway deployment models. Standard mode is the enterprise workhorse. Personal mode is a quick solution for individual developers. VNet gateways eliminate hardware management entirely but require Azure-hosted data sources and a Premium or Fabric capacity.

Feature	Standard Mode	Personal Mode	VNet Gateway
Installation	Dedicated server on-prem	User's workstation	None — Azure-managed
Users	Multiple (shared)	Single user	Multiple (shared)
Data Access Modes	Import, DirectQuery, LiveConnect	Import only	Import, DirectQuery
Clustering / HA	Yes — up to N nodes	No	Managed by Azure
License Required	Pro or Premium per user	Pro	Premium or Fabric
Data Source Location	On-premises or cloud	On-premises or cloud	Azure VNet only

Gateway Feature Comparison

💡

When to Use Which?

Use standard mode for production workloads that serve teams or departments. Use personal mode during development or for individual prototyping — but plan to migrate to standard before going live. Use a VNet gateway when your data sources are already in Azure (e.g., Azure SQL, Azure Synapse) and you want zero hardware management.

SECTION 6

Worked Example — Configuring Scheduled Refresh via Gateway

Let us walk through a realistic scenario. Imagine you are a data engineer at a mid-size retail company. You have published a Power BI report to the Power BI service that imports sales data from an on-premises SQL Server 2019 instance. You need to configure the report's dataset to refresh automatically every 4 hours during business days.

Setting Up On-Premises Scheduled Refresh

1
Step 1 — Install and Register the Gateway

Download the on-premises data gateway installer from Microsoft's gateway download page. Run the installer on a dedicated Windows Server machine that has network access to the SQL Server instance. During installation, sign in with an organizational account (the gateway admin). The installer generates an RSA key pair, registers the gateway with the Power BI service, and establishes the outbound connection to Azure Service Bus.

Gateway appears in the Power Platform admin center under Settings → Manage gateways.
2
Step 2 — Add a Data Source to the Gateway

In the Power BI service, navigate to Settings → Manage connections and gateways. Select the registered gateway and click New connection. Choose the data source type SQL Server, enter the server name (e.g., retaildb01.corp.local), database name (SalesDB), and authentication credentials. The credentials are RSA-encrypted with the gateway's public key before being stored.

A green checkmark confirms the connection is valid.
3
Step 3 — Map the Dataset to the Gateway Data Source

Navigate to the published dataset's settings page (Workspace → Dataset → Settings → Gateway connection). Power BI displays detected data sources from the .pbix file. Map each source to a gateway data source. If the connection string in the .pbix matches the gateway data source server and database, Power BI auto-maps it; otherwise, perform a manual mapping.

Status shows Gateway connection: OK for each source.
4
Step 4 — Configure the Refresh Schedule

Under the dataset's Scheduled refresh section, toggle Keep your data up to date to On. Set the refresh frequency to Daily and add time slots: 08:00, 12:00, 16:00, 20:00. Optionally, configure failure notifications so the gateway admin receives an email if a refresh fails. Click Apply.

The dataset will refresh 4 times daily. The Refresh history page shows status of each run.
5
Step 5 — Verify & Monitor

Trigger a manual refresh from the dataset's context menu (Refresh now) to validate the end-to-end pipeline. Monitor the Refresh history for success. On the gateway machine, check Event Viewer → Applications and Services Logs → On-premises data gateway for detailed logs. In a production setting, integrate gateway health metrics with Azure Monitor or a SIEM for proactive alerting.

Refresh history shows Completed status with duration in seconds. Reports consuming this dataset now display fresh data.

SECTION 7

Strengths, Limitations, and Trade-offs

Like any middleware component, the on-premises data gateway introduces both capabilities and constraints. A clear-eyed assessment of these trade-offs is essential for making sound architectural decisions, especially in environments where alternatives such as cloud data migration, ETL pipelines to Azure Data Lake, or VNet gateways are also viable options.

Strengths	Limitations
No inbound firewall ports required — outbound-only HTTPS keeps security teams satisfied	Requires a dedicated always-on Windows machine; gateway downtime blocks all dependent refreshes
Supports 60+ data source types: SQL Server, Oracle, SAP HANA, file shares, ODBC, web APIs	Some data sources (e.g., custom REST APIs) require a custom connector, adding development overhead
Credentials never leave the on-premises boundary in plaintext — RSA encryption provides strong security	Recovering a lost gateway key pair requires re-entering all data source credentials — no automated migration
Clustering enables horizontal scaling and automatic failover for high availability	Cluster members must all be on the same LAN / VPN; cross-region clustering is not supported
Single gateway can serve Power BI, Power Apps, Power Automate, and Azure Logic Apps	Concurrent refresh load can saturate gateway machine CPU/memory, requiring careful capacity planning

Gateway Strengths vs. Limitations

✦

KEY TAKEAWAY

Think of the gateway as a load balancer for hybrid data access. Just as a reverse proxy like NGINX sits at the boundary of your web infrastructure, mediating between external requests and internal backend services, the gateway mediates between cloud-based analytics requests and on-premises databases. Both require capacity planning, health monitoring, and redundancy — and both become single points of failure if not properly clustered.

SECTION 8

Connection to Advanced Theory — Gateway in the Modern Data Stack

The on-premises data gateway is not the only pattern for bridging on-prem and cloud data. As organizations mature their data infrastructure, they often encounter a spectrum of approaches, from simple gateway-based refresh to full cloud-native architectures. Understanding where the gateway fits in this continuum helps you make informed architectural recommendations.

Aspect	On-Prem Gateway (Current)	Cloud-Native Pipeline (Advanced)
Data Location	Data stays on-premises; queried on demand or imported on schedule	Data is ingested into Azure Data Lake / Synapse / Lakehouse via ADF or Fabric pipelines
Latency	DirectQuery adds round-trip latency through relay; import mode has stale data between refreshes	Near-real-time with streaming or micro-batch pipelines
Scalability	Limited by gateway machine resources; cluster adds nodes but all need LAN access	Cloud-scale compute (Spark, Synapse Serverless); auto-scales to workload
Complexity	Low — install gateway, configure data sources, set schedule	High — requires ETL/ELT pipeline design, data modeling, orchestration (ADF, Airflow)
Cost	Low marginal cost — existing server + Pro/Premium license	Azure compute and storage costs, but eliminates on-prem hardware dependency
Best For	Organizations early in cloud migration; regulated industries with data residency requirements	Organizations committed to cloud-first architecture; large-scale data processing workloads

Gateway vs. Cloud-Native Pipeline Comparison

Looking forward, Microsoft's Fabric platform is blurring the lines between these approaches. Fabric's shortcuts and mirroring features allow data from on-premises sources (accessed via gateways) to be virtualized within a lakehouse without full ETL. Meanwhile, managed private endpoints are enabling secure connectivity to Azure-hosted databases without even a VNet gateway. The long-term trajectory suggests that gateways will remain essential for truly on-premises (non-Azure) sources, while Azure-hosted data will increasingly bypass gateways entirely through native Fabric integration.

SECTION 9

Practice Problems

Question 1: CONCEPTUAL

Explain why the on-premises data gateway uses outbound-only connections rather than requiring inbound firewall rules. What security advantage does this provide over directly exposing a database port to the internet?

Show answer

The gateway initiates all connections outbound to Azure Service Bus over HTTPS (port 443), which is typically already allowed by corporate firewalls. This means no inbound ports need to be opened, drastically reducing the attack surface. If the database port (e.g., SQL Server's 1433) were exposed to the internet, it would be vulnerable to port scanning, brute-force attacks, and exploitation of SQL Server vulnerabilities. The outbound-only model also means the database server's IP address is never publicly discoverable. The relay acts as an intermediary — the cloud service communicates with the relay, and the gateway polls the relay for work, creating an air gap between the public internet and the database.

Question 2: BASIC CALCULATION

A Power BI Pro user has configured a dataset with 6 scheduled refresh slots per day. Each refresh takes approximately 12 minutes. The dataset is mapped to a standard-mode gateway that also serves 4 other datasets, each with 4 refreshes per day taking 8 minutes each. Assuming refreshes do not overlap (sequential scheduling), how many total minutes per day is the gateway busy with refresh operations? Is the gateway approaching a utilization concern if we assume it should not exceed 75% busy time in a 24-hour period?

Show answer

The first dataset contributes 6 × 12 = 72 minutes of refresh time per day. The four other datasets collectively contribute 4 × (4 × 8) = 128 minutes per day. Total gateway busy time is 72 + 128 = 200 minutes per day. A 24-hour day has 1,440 minutes, so the gateway is busy for 200 / 1,440 ≈ 13.9% of the day. The 75% threshold would be 1,080 minutes, so the gateway is well within acceptable utilization. However, this calculation assumes sequential execution and uniform distribution; in practice, refreshes may cluster around business hours (e.g., 7–9 AM), creating peak contention that this average does not capture.

Question 3: INTERMEDIATE

Your organization has a standard-mode gateway cluster with 3 nodes. Node A experiences a hardware failure at 2:00 AM. At 2:30 AM, a scheduled refresh triggers for a dataset that was previously load-balanced across all three nodes. Describe the sequence of events that occurs, including how the Power BI service, Azure Service Bus, and the remaining gateway nodes interact. What would happen if nodes B and C were also offline?

Show answer

When the Power BI service triggers the 2:30 AM refresh, it sends a request to the Azure Service Bus relay associated with the gateway cluster. The relay attempts to route the request to an available cluster member. Since Node A is offline (its persistent WebSocket connection has dropped), the relay detects this and routes the request to either Node B or Node C, whichever responds first or has lower load (round-robin or load-based routing). The surviving node decrypts the credentials using its copy of the cluster's shared private key, queries the on-premises SQL Server, and returns results. If all three nodes were offline, the relay would have no active listeners. The refresh request would time out after the configured retry window (typically several minutes). Power BI would mark the refresh as failed and send an email notification to the dataset owner. The dataset would retain its last successfully imported data until a future refresh succeeds.

Question 4: APPLIED

You are consulting for a healthcare company subject to HIPAA regulations. They want to visualize patient outcome data in Power BI. The data resides in an on-premises PostgreSQL database. The CISO insists that no patient data may be stored in the public cloud, but executives want cloud-based dashboards for remote access. Design an architecture using gateways that satisfies both requirements. Specify the gateway mode, data access pattern (import vs. DirectQuery), and justify your choices with respect to HIPAA's data-at-rest and data-in-transit requirements.

Show answer

Deploy a standard-mode gateway cluster (minimum 2 nodes for HA) on dedicated, hardened Windows servers within the healthcare company's HIPAA-compliant data center. Configure the Power BI dataset to use DirectQuery rather than import mode. With DirectQuery, patient data is never persisted in the Power BI service's cloud storage — queries are executed in real time against the on-premises PostgreSQL instance, and only aggregated result sets (the visual rendering data) are transmitted to the user's browser. This satisfies the CISO's requirement that no patient data at rest exists in the public cloud. For data-in-transit compliance, the gateway's TLS 1.2+ encrypted channel satisfies HIPAA's transmission security standard (45 CFR §164.312(e)(1)). Additionally, enable the gateway's secondary AES-256 encryption layer for defense in depth. Ensure that PostgreSQL credentials stored in the Power BI service are encrypted with the gateway's RSA key pair, meaning Microsoft has no access to database credentials. Finally, configure Row-Level Security (RLS) in the Power BI dataset to enforce role-based access to patient data, aligning with HIPAA's minimum necessary standard.

Question 5: CRITICAL THINKING

Critically evaluate the following claim: "The on-premises data gateway will become obsolete within five years as all enterprise data moves to the cloud." Consider technical, organizational, regulatory, and economic factors. Under what conditions might this claim hold true, and under what conditions is it likely false? Reference specific gateway features and cloud-native alternatives in your analysis.

Show answer

The claim oversimplifies a complex migration landscape. Technically, full cloud migration is feasible for many workloads, and Microsoft's Fabric platform with VNet gateways, managed private endpoints, and lakehouse mirroring is reducing dependency on traditional gateways. However, several factors challenge the five-year obsolescence timeline. Organizationally, many enterprises operate legacy systems (mainframes, on-prem ERP, SCADA/OT systems) that cannot be cloud-migrated without fundamental re-architecture — a multi-year, multi-million-dollar effort. Regulatory factors like GDPR's data sovereignty requirements, HIPAA, and sector-specific regulations (financial services, defense) may mandate on-premises storage for certain data classes indefinitely. Economically, some organizations find that cloud storage and compute costs at scale exceed on-prem total cost of ownership, especially for large, relatively static datasets. The gateway is likely to remain essential for organizations with genuinely on-premises infrastructure (factory floors, air-gapped networks, legacy databases). The claim may hold for cloud-native organizations or those with modest data volumes. A more nuanced prediction is that gateway usage will decline gradually as a percentage of total connections, but the absolute number of gateway installations will plateau rather than disappear, serving the long tail of on-prem data sources for a decade or more.

SUMMARY

Lesson Summary

Summary

The on-premises data gateway is a software agent that enables secure, outbound-only connectivity between on-premises data sources and the Power BI cloud service. It communicates via Azure Service Bus Relay using TLS 1.2+ encrypted WebSocket connections, ensuring no inbound firewall ports are required. Credentials are protected by an RSA-2048 asymmetric key pair generated at installation time, with the private key stored only on the gateway machine. The gateway supports both scheduled refresh (import mode) and DirectQuery data access patterns, allowing organizations to choose between data freshness and performance.

Three deployment models serve different needs: standard mode for enterprise-grade shared access with clustering and high availability; personal mode for single-user development scenarios; and VNet gateways for Azure-managed, hardware-free connectivity to Azure-hosted data sources. While cloud-native pipelines (Azure Data Factory, Fabric) represent the advanced end of the hybrid data continuum, the on-premises gateway remains indispensable for organizations with data residency requirements, legacy on-prem databases, or early-stage cloud migration strategies.