Power BI Data Gateways - The Thing That Breaks at 6am and How to Get It Right

Here is a pattern I have seen more times than I can count. A company builds a beautiful set of Power BI reports off their on-premises database, everyone is thrilled, and it works perfectly in the demo. Three weeks later the reports are showing yesterday's numbers, then last week's, and eventually someone notices the dashboard has been quietly stale for a fortnight. The reports were never the problem. The data gateway was, and nobody was watching it.

The gateway is one of those pieces of infrastructure that is invisible when it works and infuriating when it does not. It is also one of the most misunderstood parts of a Power BI deployment, because it lives in the gap between the data team who build reports and the IT team who run servers, and things in that gap tend to fall down. Microsoft's gateway planning guidance is the thorough reference. This is the field version, from setting these up and, more often, being called in to fix them.

What a gateway is actually for

Power BI lives in the cloud. A lot of your data does not. It sits in a SQL Server in a server room, an ERP database behind the corporate firewall, or a file share that never leaves the building. The gateway is the bridge that lets the Power BI service reach that on-premises data securely without you having to open your firewall to the internet.

The mechanics matter for understanding why it fails. The gateway is a piece of software you install on a machine inside your network. It makes an outbound connection to the Power BI service and waits. When a report needs to refresh, the service sends the request down through that connection, the gateway runs the query against your local data, and sends the results back up. The firewall stays closed because nothing is coming in uninvited. That outbound-only design is genuinely well thought out and it is why security teams generally accept it.

The catch is right there in the description. The gateway is software, on a machine, that has to be running. If that machine is off, the query fails. If the machine is a spare desktop under someone's desk that gets rebooted for Windows updates, your refresh fails during the reboot. If the person who installed it has left and their account was the one it ran under, it stops when their account gets disabled. Every one of those has been a real client incident.

Personal versus standard, and why it matters

There are two flavours of gateway and choosing the wrong one is a common early mistake.

The personal mode gateway is designed for one person refreshing their own reports. It is quick to set up, it runs under your account, and it is fine for an individual analyst doing their own thing. It does not support DirectQuery, it cannot be shared or centrally managed, and it dies when that person's machine is off or their account changes. It is a personal tool and it should never be holding up reports the business depends on.

The standard mode gateway, previously called the on-premises data gateway, is the one for anything that matters. It can be shared across a team, multiple people can use the same gateway for their data sources, it supports DirectQuery and live connections, and critically it can be centrally managed and monitored. This is what you want for anything production.

The mistake we see is a keen analyst getting a report working on a personal gateway on their own laptop, the report becoming important, and the whole business quietly depending on a gateway that lives on one person's machine and stops the moment they close the lid or go on leave. When we audit a Power BI setup, "what is this refresh actually running on" is one of the first questions, and the answer is uncomfortable more often than it should be. Sorting this out is a standard part of the Power BI work we do.

Where it lives is the whole game

The single most important decision about a gateway is what machine it runs on, and it is the one people put the least thought into.

A gateway needs to run on a machine that is always on, always connected, and properly maintained. That means a server, ideally a dedicated one, not someone's workstation. It needs to survive reboots, so the gateway service should start automatically and run under a service account rather than a personal login. It needs enough grunt, because refreshing large datasets is memory and CPU hungry, and a gateway starved of resources will refresh slowly or time out.

The classic disaster is the gateway installed on the machine of whoever set up Power BI first, running under their personal account. They go on leave, IT reboots the machine for patching, or their account gets disabled when they change roles, and every refresh across the business fails at once with no obvious cause. The fix is dull and correct: put the gateway on a proper server, run it under a dedicated service account, and document what it is and who owns it. Boring infrastructure is reliable infrastructure.

For real resilience, standard gateways can be clustered, meaning you install several and group them so that if one goes down the others carry the load. For any business where stale reports actually cost something, this is worth doing, and it is remarkable how few organisations have it set up until after their first painful outage.

Why refreshes fail, in order of likelihood

When a client says "the refresh is broken", the cause is almost always one of a short list, and it is rarely the report.

The machine is off or asleep is number one. If the gateway lives on a workstation with power-saving settings, it sleeps overnight and your scheduled 5am refresh fails. Servers do not sleep. Workstations do.

Credentials have changed is a close second. The gateway stores credentials for each data source, and when a database password rotates, or a service account changes, the stored credential goes stale and the refresh fails with an authentication error until someone updates it in the gateway settings. This is why we push for service accounts with managed, documented rotation rather than a personal password nobody remembers setting.

The machine ran out of resources is the sneaky one. Refreshes that worked fine at launch start failing as the data grows and the gateway machine cannot cope, especially if it is also doing other jobs. The symptom is intermittent timeouts that are maddening to diagnose if you are not looking at the machine's resource use.

And the gateway software is out of date is the quiet one. Microsoft ships monthly updates, some contain fixes for exactly the failures people hit, and a gateway left un-updated for a year is a gateway accumulating bugs someone already fixed. Keeping it current should be part of routine maintenance, not something you do reactively after an incident.

Getting it right, and keeping it right

The setup that stays reliable is not complicated, it is just disciplined. Put standard gateways on proper always-on servers, not workstations. Run them under dedicated service accounts, not personal logins, and document those accounts. Size the machine for the load and keep an eye on it as data grows. Cluster gateways where downtime actually costs money. Keep the software current. And, the part everyone skips, monitor the thing, so you find out a refresh failed from an alert rather than from a manager asking why the numbers look wrong.

That last point deserves emphasis because it is where most of the real-world pain comes from. A gateway that fails loudly, with an alert to someone who will act on it, is a minor annoyance. A gateway that fails silently while everyone keeps making decisions on stale data is a genuine business risk. The technology to alert on gateway and refresh failures is right there in the platform. The gap is almost always that nobody set it up and nobody owns it.

There is a broader point here about treating Power BI as real infrastructure rather than a reporting toy. The reports are the visible part, and the gateway, the refresh schedules, the service accounts and the monitoring are the plumbing that decides whether those reports can be trusted. Getting that plumbing right is a lot of what separates a Power BI deployment that runs quietly for years from one that generates a steady drip of "why is this wrong" emails. It sits inside the wider business intelligence work we do with clients.

If you have Power BI in production and you are not entirely sure what your refreshes are running on, or you are building something new and want the infrastructure done properly from the start, that is exactly the kind of thing we sort out. Have a look at our services or get in touch and we will give you an honest read on how fragile or solid your setup really is.

For the full planning framework and current recommendations, Microsoft's data gateway planning guidance is worth reading properly.