Wicked Smart Data
LearnInsightsAboutContact
Sign InLet's Build
LearnInsightsAboutContact
Sign InLet's Build
Wicked Smart Data

Intelligence, automation, and expert execution — plus an elite library of free knowledge. We turn complexity into competitive advantage.

Start a conversation

Platform

  • Learning Paths
  • Insights
  • RSS Feed

Company

  • About
  • Contact
  • Work With Us

Legal

  • Privacy Policy
  • Terms of Service

© 2026 Wicked Smart Data. All rights reserved.

Intelligence · Automation · Advantage

All Insights
Power BI

Connecting Power BI to On-Premises Data Sources Using the On-Premises Data Gateway

Your company's data lives on-premises. Your Power BI reports live in the cloud. The On-Premises Data Gateway is the bridge between them — and this lesson teaches you exactly how to build it, configure it, and keep it running reliably in a real enterprise environment.

🌱 Foundation17 min readJul 1, 2026Updated Sep 23, 2026
Hero image for Connecting Power BI to On-Premises Data Sources Using the On-Premises Data Gateway
On this page
  • Introduction
  • Prerequisites
  • What the Gateway Actually Is (and Why It Exists)
  • Choosing the Right Machine for Your Gateway
  • Downloading and Installing the Gateway
  • Adding a Data Source to the Gateway
  • Building and Publishing a Report That Uses the Gateway
  • Hands-On Exercise
  • Common Mistakes and Troubleshooting
  • Summary and Next Steps

Connecting Power BI to On-Premises Data Sources Using the On-Premises Data Gateway

Introduction

Picture this: your company's sales data lives in a SQL Server database sitting in a server room down the hall. Your finance team's expense records are in an Oracle database on a machine that hasn't seen the outside internet since 2017. You've been asked to build a Power BI dashboard that pulls from both, publishes to the Power BI Service so leadership can view it from anywhere, and refreshes automatically every morning before the 8 AM standup.

Here's the problem nobody mentions until you're already stuck: Power BI Service runs in Microsoft's cloud. Your databases don't. The cloud has no idea your SQL Server exists, let alone how to talk to it. This is exactly the gap that the On-Premises Data Gateway is designed to bridge — and by the end of this lesson, you'll understand not just how to configure it, but why it works the way it does, so you can troubleshoot it when things go sideways.

We're going to walk through the full lifecycle: what the gateway actually is, how to install and configure it, how to connect a data source, and how to wire it all together so your Power BI reports refresh automatically from data that never leaves your network.

What you'll learn:

  • What the On-Premises Data Gateway is and how it creates a secure tunnel between the Power BI cloud and your local network
  • How to download, install, and register the gateway on a suitable machine
  • How to add on-premises data sources to the gateway in Power BI Service
  • How to configure a published dataset to use the gateway for scheduled refresh
  • How to diagnose and fix the most common gateway errors you'll encounter in real environments

Prerequisites

Before diving in, you should have:

  • A Power BI Desktop installation (free download from Microsoft)
  • A Power BI Pro or Premium license, or access to a Power BI Service workspace (the gateway feature requires the cloud service — the free Desktop-only experience won't cut it here)
  • Access to an on-premises data source — a SQL Server database is the most common scenario and what we'll use throughout this lesson
  • Local administrator rights on the machine where you'll install the gateway
  • A work or school Microsoft account (personal accounts like @outlook.com or @gmail.com won't work with the gateway)

If you've built reports in Power BI Desktop before but haven't published them to the cloud yet, this lesson will fill in a major piece of the puzzle.


What the Gateway Actually Is (and Why It Exists)

Before touching any settings, it's worth understanding the architecture. If you skip this part, gateway problems will feel like random black magic. If you understand it, they'll feel solvable.

The Power BI Service is a cloud application hosted by Microsoft. When you publish a report and set up a scheduled refresh, Microsoft's servers need to go fetch your latest data. But your SQL Server database sits behind your company's firewall, on a private network. Microsoft's cloud cannot reach in and knock on that door — and for good reason. Opening inbound firewall ports to let cloud services directly access your internal databases would be a serious security nightmare.

The On-Premises Data Gateway solves this with an elegant inversion: instead of the cloud reaching into your network, a piece of software you install inside your network reaches out to the cloud. Think of it like a secure phone call that your network initiates — your IT firewall allows outbound connections, so the gateway establishes and maintains an encrypted channel to Microsoft's Azure Service Bus. When Power BI Service needs data, it sends a request through that channel, the gateway receives it, queries your database locally, and sends the results back through the same encrypted tunnel.

This means:

  • No inbound firewall rules are needed. Your network's security posture doesn't change.
  • Your data never sits unprotected in transit. The connection uses industry-standard encryption.
  • The gateway machine must stay running. It needs to be on and connected for refresh to work.

There are two gateway modes you should know about:

Standard mode (also called Enterprise mode) is what most organizations use. It's installed on a dedicated machine and can be shared across multiple Power BI reports, datasets, Power Automate flows, and even Analysis Services. Multiple people in your organization can use the same gateway to connect to different data sources.

**Personal mode** is a simplified version that only supports Power BI and can only be used by the person who installed it. It's useful for individual analysts who just need their own reports to refresh, but it doesn't scale to team or enterprise scenarios. For this lesson, we'll focus on Standard mode.


Choosing the Right Machine for Your Gateway

Where you install the gateway matters more than most tutorials acknowledge. The gateway machine becomes the workhorse that runs every data refresh — sometimes several per day — so choosing poorly will cause headaches later.

Hardware considerations: For most teams, a dedicated server or a reliable workstation with at least 8 GB of RAM and a solid network connection to your database server will do the job. If your refreshes involve large datasets or frequent schedules, lean toward more RAM and a faster CPU.

Availability is the biggest factor: The gateway machine must be powered on and connected to the network whenever a refresh is scheduled. A developer's laptop that gets closed at 6 PM and goes to sleep is a terrible gateway host. A server or always-on desktop machine is far better. Many organizations use a dedicated Windows Server VM for this purpose.

Network proximity: Because the gateway queries your databases locally, it should have fast, low-latency access to your data sources. A machine on the same network segment as your SQL Server is ideal.

Operating system: The gateway requires Windows 8 / Windows Server 2012 R2 or later. Most modern environments will have no issue here.

Tip

Don't install the gateway on the same machine that hosts your SQL Server database if you can avoid it. Resource contention during heavy refresh jobs can degrade both the gateway and the database server. A dedicated gateway machine is the cleaner architecture.


Downloading and Installing the Gateway

Once you've identified your gateway machine, navigate to the Power BI Service by going to app.powerbi.com and signing in with your work account.

To find the gateway installer, click the Settings gear icon in the top-right corner of the Power BI Service. In the dropdown menu, select Manage connections and gateways. On that page, look for a Download button or a link to download the gateway. This ensures you're getting the latest version directly from Microsoft.

Alternatively, you can search for "Microsoft On-Premises Data Gateway" and download it directly from the Microsoft website. The installer file will be named something like GatewayInstall.exe.

Copy that installer to your chosen gateway machine and run it with a local administrator account. Here's what the installation process looks like:

  1. The installer will ask you to choose between Standard mode and Personal mode. Choose Standard mode.
  2. Accept the terms and choose your installation directory. The default is usually fine.
  3. The installer will ask for the email address of your work or school Microsoft account. Enter the same account you use to sign into Power BI Service.
  4. After validating your account, you'll be prompted to either Register a new gateway or Migrate, restore, or takeover an existing gateway. Since this is a fresh setup, choose Register a new gateway on this computer.
  5. Give your gateway a name. Choose something descriptive and unique within your organization — something like FinanceDept-GW-PROD is far more useful than MyGateway. You'll see this name when managing connections in Power BI Service.
  6. Set a Recovery Key. This is a passphrase that allows you to restore or migrate your gateway if the machine ever needs to be replaced. Write this down and store it somewhere safe. There is no way to recover it if you lose it, and without it, you cannot migrate your gateway configuration.
  7. Click Configure and the gateway will register itself with the Power BI Service.

When installation completes, you'll see a green status indicator confirming the gateway is online and connected. The gateway application sits in your system tray and starts automatically with Windows.

Warning

The gateway service runs as a Windows service account. If your SQL Server uses Windows Authentication, the service account running the gateway needs to have permission to log into that database. This is one of the most common setup issues — more on this in the troubleshooting section.


Adding a Data Source to the Gateway

Installing the gateway is only half the work. The gateway itself doesn't know which databases to connect to yet — you have to configure data sources explicitly. Think of the gateway as a secure post office: installing it builds the post office, but you still have to register the addresses it's allowed to receive mail from.

Head back to the Power BI Service (app.powerbi.com) and navigate to Settings → Manage connections and gateways. You should see your newly registered gateway listed under the On-premises data gateways section.

To add a data source, click New in the top-left area of the Connections page. A panel will open on the right side asking for connection details:

  • Connection name: Give this a clear, descriptive name like SalesDB-SQLServer-PROD. You'll reference this name when configuring dataset refresh later.
  • Connection type: Choose SQL Server (or whatever matches your on-premises source — Oracle, MySQL, SharePoint, File System, etc. are all options).
  • Server: Enter the server name or IP address of your database server, exactly as you would in SQL Server Management Studio (SSMS). If your SQL Server uses a named instance, include it: DBSERVER01\SALESINSTANCE.
  • Database: Enter the specific database name.
  • Authentication method: This is where many people get tripped up. You have two choices:
    • Windows Authentication uses the service account running the gateway to connect to SQL Server. This is common in enterprise environments where SQL Server is configured for Integrated Security. Make sure the gateway service account has been granted login permissions in SQL Server.
    • Basic (username/password) uses SQL Server Authentication, where you provide a dedicated SQL login. This is simpler to configure but requires that your SQL Server has mixed-mode authentication enabled.

Fill in your credentials and click Create. Power BI will test the connection immediately. If everything is configured correctly, you'll see a success message and the data source will appear in your connections list.

Tip

If the connection test fails with an error like "Login failed for user," your SQL Server credentials are wrong or the account doesn't have access to that database. If it fails with a timeout or "Unable to connect to server," the network path between the gateway machine and the database server may be blocked — check firewall rules between those two machines.


Building and Publishing a Report That Uses the Gateway

Now that your gateway is installed and your data source is registered, let's tie it to an actual report.

Open Power BI Desktop and connect to your SQL Server database the usual way: click Home → Get Data → SQL Server. Enter your server and database name, choose your authentication method, and load your tables. For this example, imagine you're connecting to a SalesDB database and loading a dbo.Orders table.

Build a simple report — a bar chart showing total sales by region, for instance. Now publish it to Power BI Service by clicking Home → Publish and selecting your workspace.

Once published, open the Power BI Service and navigate to your workspace. Find the dataset that was created when you published (it's separate from the report itself). Click the three-dot menu next to the dataset and choose Settings.

In the dataset settings, expand the Gateway and cloud connections section. Power BI will attempt to match the connection information in your report to the gateway data sources you've already configured. If your server name and database name match exactly, it should auto-detect the right connection. If it doesn't auto-match, you can manually map each data source in your report to the corresponding gateway connection using the dropdown menus.

Once your gateway connection is mapped, scroll down to the Scheduled refresh section. Toggle refresh on, set the frequency (daily, weekly, etc.) and the specific times. You can schedule up to 8 refreshes per day on Power BI Pro, and up to 48 refreshes per day on Premium.

Click Apply and your dataset is now configured to pull fresh data from your on-premises source automatically.

Tip

After setting up scheduled refresh, trigger a manual refresh immediately using the Refresh now option on your dataset. This confirms everything works before you walk away and discover it silently failed overnight.


Hands-On Exercise

This exercise assumes you have access to a SQL Server database on your local machine or network and a Power BI Pro account.

Step 1 — Install the gateway. Download the Standard mode gateway installer from the Power BI Service (Settings → Manage connections and gateways → Download). Install it on your machine or a test server. Register a new gateway named Learning-Gateway-[YourInitials] and set a recovery key. Confirm it shows as online in the Power BI Service.

Step 2 — Add a data source. In the Power BI Service under Manage connections and gateways, create a new connection pointing to a SQL Server database you have access to. Use either Windows or Basic authentication as appropriate for your environment. Confirm the connection test succeeds.

Step 3 — Build and publish a report. In Power BI Desktop, connect to the same SQL Server database using Get Data → SQL Server. Load at least one table and build a simple visualization (a table visual or a bar chart showing row counts by category works fine). Publish the report to your Power BI workspace.

Step 4 — Configure refresh. In Power BI Service, go to your published dataset's settings. Map the data source to your gateway connection. Enable scheduled refresh for once daily at a time of your choosing.

Step 5 — Validate. Click Refresh now on your dataset. Watch the refresh history (available under dataset settings → Refresh history) to confirm it completes successfully. Check that the timestamp on your dataset updates.


Common Mistakes and Troubleshooting

The gateway shows as offline in Power BI Service. The gateway service on your host machine has stopped or the machine is off. Log into the gateway machine, open the On-Premises Data Gateway application from the system tray or Start menu, and check its status. If the service is stopped, you can restart it from within the app or through Windows Services (services.msc, find "On-premises data gateway service"). Also verify the machine has internet access — the gateway needs to reach Microsoft's Azure endpoints.

"Unable to connect to the server" when adding a data source. This almost always means the gateway machine can't reach the database server on the network. Common causes: the database server's firewall blocks the connection (SQL Server uses TCP port 1433 by default), the SQL Server Browser service is not running (needed for named instances), or you've entered the server name incorrectly. Test connectivity from the gateway machine using SSMS directly — if SSMS can connect, the gateway can too.

"Login failed for user" errors with Windows Authentication. When you configure Windows Authentication on a gateway data source, the gateway connects to SQL Server as the Windows service account that runs the gateway service. By default this is NT SERVICE\PBIEgwService. This account is not a domain account and almost certainly doesn't have SQL Server access. Either grant this account a SQL Server login, or switch the gateway service to run as a domain service account that already has database access. You can change the service account in the gateway app under Service Settings.

Data source mapping fails — Power BI can't find a matching gateway connection. Power BI matches report connections to gateway data sources by comparing the server name and database name exactly. If you connected in Desktop using localhost and your gateway data source uses the actual server name DBSERVER01, they won't match. Republish after updating the connection in Desktop, or rename your gateway data source to match exactly what Desktop reports.

Refresh completes but the data looks stale. The refresh succeeded, but Power BI may be showing you a cached version. Hard refresh your browser or close and reopen the report. Also double-check your report isn't using DirectQuery incorrectly — if it's set to Import mode, it only reflects the data as of the last refresh, not real-time.

Warning

Gateway recovery keys cannot be recovered or reset if lost. If your gateway machine fails and you don't have the recovery key, you'll need to reinstall the gateway entirely and reconfigure all data sources and dataset mappings from scratch. Store that key in your organization's password manager the moment you create it.


Summary and Next Steps

Let's recap what you've built and why each piece matters.

The On-Premises Data Gateway solves a fundamental architectural problem: it lets Microsoft's cloud service talk to databases that live behind your firewall by inverting the connection model. Your network reaches out to the cloud; the cloud doesn't reach in to you. This means no inbound firewall holes and no compromise of your security posture.

You installed the gateway in Standard mode on a reliable, always-on machine, registered it with your Power BI tenant, and gave it a recovery key (which you saved somewhere safe). You added a data source to the gateway by providing connection details and credentials — essentially telling the gateway "here's one of the addresses you're allowed to deliver mail to." Then you published a Power BI report, mapped its dataset to your gateway connection, and configured scheduled refresh so the report stays current automatically.

This is the foundation of enterprise Power BI. Most production reporting environments you'll encounter have a gateway at their core.

Where to go from here:

  • High availability gateway clusters: You can add multiple gateway machines to the same logical gateway, creating redundancy. If one machine goes down, the other handles refresh jobs automatically. This is critical for production deployments.
  • Gateway performance monitoring: The gateway application exposes logs and performance metrics. Learning to read them helps you optimize slow refreshes and catch problems before users report them.
  • DirectQuery mode: Instead of importing data on a schedule, DirectQuery sends live queries to your database every time someone opens the report. The gateway supports this too — it means always-fresh data, but puts more load on your database server.
  • Power Automate and Power Apps integration: The same gateway you configured for Power BI can also be used by Power Automate flows and Power Apps connections, giving you a single, managed integration point for your whole Microsoft Power Platform.

You've just become the person in your team who can answer "why isn't the report refreshing?" — and more importantly, the person who can fix it.

Work With Us

From insight to implementation

Reading is the start. When you're ready to build the data, automation, or AI systems behind it, our team turns strategy into shipped results.

Let's Build

Enterprise Power BI

Previous

Implementing Object-Level Security in Power BI to Restrict Table and Column Access for Sensitive Enterprise Data

Next

Implementing Power BI Tenant Settings and Sensitivity Labels for Enterprise Data Protection and Compliance

Related Insights

Power BIFoundation

Implementing Power BI Dataflow Incremental Refresh with Change Data Capture to Optimize Enterprise ETL Pipeline Performance and Reduce Source System Load

18 min
Power BIPractitioner

DAX Table Expansion and Relationship Propagation: How RELATED, RELATEDTABLE, and Filter Context Cross Model Boundaries in Complex Star and Snowflake Schemas

23 min
Power BIFoundation

Adding Tooltips and Drill-Through Actions to Your First Power BI Report: A Beginner's Guide to Interactive Navigation

17 min

On this page

  • Introduction
  • Prerequisites
  • What the Gateway Actually Is (and Why It Exists)
  • Choosing the Right Machine for Your Gateway
  • Downloading and Installing the Gateway
  • Adding a Data Source to the Gateway
  • Building and Publishing a Report That Uses the Gateway
  • Hands-On Exercise
  • Common Mistakes and Troubleshooting
  • Summary and Next Steps