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:
Before diving in, you should have:
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.
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:
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.
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.
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:
FinanceDept-GW-PROD is far more useful than MyGateway. You'll see this name when managing connections in 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.
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:
SalesDB-SQLServer-PROD. You'll reference this name when configuring dataset refresh later.DBSERVER01\SALESINSTANCE.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.
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.
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.
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.
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:
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.
Learning Path: Enterprise Power BI