Adding and Removing Power BI Gateway Data Sources - A Practical Guide

Most Power BI refresh failures I get called in to look at have nothing to do with the report. The DAX is fine, the model is fine, and somebody's dashboard has been showing last Tuesday's numbers for a week because a gateway data source is pointing at a SQL Server that got renamed during a migration, or it's using the credentials of someone who left the company in March.

If your organisation still has data sitting on-premises (and plenty of Australian businesses do, especially in manufacturing, logistics, local government and anyone running an ERP that predates the cloud), the on-premises data gateway is the bridge between that data and the Power BI service. Data sources on that gateway are the individual connections that bridge carries. Getting them right is boring work, and it's also the thing that decides whether your reports refresh at 6am or throw an error into someone's inbox.

This post walks through adding and removing gateway data sources, based on Microsoft's documentation and a fair amount of time spent cleaning up gateways that grew without any plan.

What a gateway data source actually is

The gateway itself is a piece of software installed on a Windows machine inside your network. It sits there and relays queries between the Power BI service and your local systems.

A gateway data source (Microsoft now calls these "connections" in most of the UI) is a stored definition on that gateway: the type of source, the server, the database, and the credentials used to connect. When a semantic model refreshes, Power BI matches the model's connection details to a data source on the gateway and uses those stored credentials.

That matching step is where most of the pain comes from. The server name and database name in your Power BI Desktop file have to match the gateway data source exactly. SQLPROD01 and sqlprod01.corp.local are different as far as the matching logic is concerned. So are localhost and the actual machine name. I've watched people spend an afternoon on this one.

Adding a data source

The process in the Power BI service is straightforward:

1. Go to Settings (the gear icon) and choose Manage connections and gateways.
2. On the Connections tab, select New.
3. Choose On-premises and pick the gateway cluster you want to use.
4. Give the connection a name, pick the connection type (SQL Server, Oracle, file, folder, SAP HANA, ODBC and so on), and fill in the server and database details.
5. Choose the authentication method and enter credentials.
6. Set the privacy level, and decide whether to allow the source to be used with cloud connections or with DirectQuery through the gateway.
7. Select Create.

Power BI tests the connection when you save it. If the test fails, you'll get an error and the connection won't be created, which is good. Better to find out now than at 2am.

After it's created, you can add users to the connection so they can bind their semantic models to it. People often miss this. Creating the connection doesn't mean anyone else can use it.

Credentials - the decision that matters most

For SQL Server you'll typically choose between Windows authentication and Basic (SQL) authentication. There's also the option to use single sign-on via Kerberos or Microsoft Entra ID for DirectQuery, which passes the report viewer's identity through to the source.

My strong opinion here: use a dedicated service account. Not your own account. Not the account of the BI developer who set it up. A named service account with read-only access to exactly what the reports need, a password managed by whoever manages your other service accounts, and an owner written down somewhere.

The number of organisations I've seen where every gateway data source is bound to one person's domain credentials is honestly a bit alarming. That person goes on leave, their password expires under your 90-day rotation policy, and suddenly forty reports stop refreshing. Or they leave the business and IT disables the account on their last day, which is exactly what IT should do.

SSO via Kerberos is great when you need row-level security enforced at the database rather than in the model, but it's fiddly to set up. You need constrained delegation configured in Active Directory, SPNs registered correctly, and the gateway service account set up properly. Budget time for it and test it with more than one user. If you don't have a real need for per-user identity at the source, stick with a service account and do security in the model.

Privacy levels

The privacy level setting (None, Private, Organisational, Public) controls how Power Query is allowed to combine data from different sources. If you set everything to Private, you'll get errors when a query tries to merge two sources. If you set everything to None, you've turned off a protection that exists for a reason.

For most internal line-of-business databases, Organisational is the right choice. It lets your internal sources combine with each other without letting that data get folded into a query sent to a public web source.

Removing a data source

Removing a connection is simple mechanically. From Manage connections and gateways, find the connection, open its menu and choose Remove.

The bit that isn't simple is knowing what depends on it. When you remove a data source, any semantic model bound to it will fail its next refresh. There's no cascading warning that says "these 12 models will break". You need to work that out beforehand.

What we do on cleanup jobs:

• Pull the list of semantic models and their gateway bindings, either through the admin APIs or the scanner API, so you know what is actually using each connection.
• Check whether any of those models are still refreshed or viewed. Usage metrics and the activity log help here. Half the time, the models bound to an old connection haven't been opened in a year.
• For models that are still live, rebind them to the replacement connection first, run a manual refresh, confirm it works, then remove the old connection.
• Tell the model owners before you do it. A short email saves a long argument.

If you're decommissioning an entire gateway rather than a single data source, the same logic applies at a larger scale. Move everything to the new gateway cluster, confirm refreshes over a full cycle (including the weekly and monthly ones people forget about), then take the old one down.

Things that go wrong

A few problems come up again and again.

Mismatched server names. Already mentioned, but it's the single most common cause of "you don't have a gateway configured for this data source" errors. Standardise on fully qualified names in both Desktop and the gateway, and put that in your report development standards.

Duplicate connections. Without some governance, you end up with "Sales DB", "SalesDB", "Sales DB (new)" and "Sales DB - Mike test" all pointing at the same server. Each has different credentials and different users. Then nobody knows which one is safe to remove. Pick a naming convention (we like Environment - System - Database, e.g. PROD - Pronto - Reporting) and stick to it.

Single-machine gateways. If your gateway runs on one server with no cluster, that server's patching schedule is now your reporting outage schedule. Adding a second member to the cluster is cheap insurance. Data sources are defined at the cluster level, so they apply across all members, but the drivers and network access have to be present on every machine. An Oracle client installed on node one and not node two will give you intermittent failures that are maddening to track down.

Gateway admins versus connection users. These are separate roles. A gateway admin can manage the gateway and all its connections. A connection user can only bind models to specific connections. Too many organisations make everyone a gateway admin because it's the quickest way to stop the support tickets. Don't.

Where this fits in the bigger picture

I'll be honest: the on-premises gateway is one of the less loved parts of the Power BI platform. It works, and it's far better than it was five years ago, but it's still a Windows service on a box someone has to patch, monitor and keep the drivers current on. For organisations moving towards Microsoft Fabric, there's a growing case for landing on-premises data into OneLake through pipelines or mirroring and pointing reports at that instead, rather than having every report query the source system directly through the gateway.

That's not a reason to rip out your gateway tomorrow. For a lot of mid-sized Australian businesses, a well-run gateway with a sensible set of data sources is the right answer for years to come. It just has to be run on purpose rather than left to grow.

If you want a hand auditing an existing gateway setup, or planning a move from direct gateway queries to a Fabric-based approach, our Power BI consultants do this kind of work regularly. We also help teams connect this reporting layer to AI-driven analysis through our business intelligence solutions.

Quick checklist

Before you add a data source:

• Server and database names match what's in Power BI Desktop, character for character
• A dedicated service account with least-privilege access
• Correct privacy level (usually Organisational for internal sources)
• Drivers installed on every gateway cluster member
• Users added to the connection so they can actually use it

Before you remove one:

• You know every semantic model bound to it
• Live models have been rebound and tested
• Owners have been told
• You've waited through a full refresh cycle

Reference: Add or remove a gateway data source - Microsoft Learn