Power BI and SQL Server - Managing an Enterprise Data Source Through the Gateway

Here is a scenario that plays out constantly. A team builds a genuinely useful Power BI report, everyone loves it, and then someone asks the obvious question: "why is it still showing last Tuesday's numbers?" The report was built on a one-off import of data from a SQL Server database sitting in the company's own data centre, and nothing has refreshed it since. The report is a snapshot, not a living thing, and the moment people notice, trust in it starts to wobble.

The fix is the on-premises data gateway, and setting it up properly is one of those unglamorous jobs that separates a Power BI toy from a Power BI capability the business can actually rely on. Microsoft's reference is Manage your enterprise data source - SQL, and it walks through the mechanics. This post is about what it means in practice and where teams trip up.

The problem the gateway solves

Power BI, when you publish a report to the service, lives in the cloud. Your SQL Server database, in a lot of Australian businesses, does not. It sits on a server in your office, your data centre, or a colocation facility, behind a firewall, with no direct path from the internet to it. And that is exactly how you want it. Nobody sane wants their production database exposed to the open internet.

So there is a gap. The cloud report needs fresh data. The database that holds that data is locked away on-premises. The gateway bridges that gap. It is a piece of software you install on a machine inside your network, and it acts as a secure, controlled relay. When Power BI in the cloud needs to refresh a report, it sends the request to the gateway, the gateway runs the query against your SQL Server from inside the network where it is allowed to, and it sends the results back up. Your database never gets exposed. The connection is always outbound from the gateway, which is far easier for a security team to sign off on than punching an inbound hole in the firewall.

This is not just for scheduled refresh, either. If you use DirectQuery, where Power BI queries the source live every time someone interacts with a report rather than importing a copy, the gateway is what makes that possible for an on-premises SQL Server. Same bridge, different traffic pattern.

What setting it up actually involves

There are a few moving parts, and getting them straight in your head saves a lot of confusion later.

First, you install the gateway. Microsoft offers it in two modes. Standard mode is the one you want for anything shared: multiple people, multiple reports, centrally managed. Personal mode exists for a single person refreshing their own data, and I would steer almost everyone away from it for anything that matters, because it lives on one person's machine and dies when they leave or turn their laptop off. If a report is important to the business, it should not depend on someone's laptop being awake.

Second, the gateway needs to live somewhere sensible. It should be on a machine that is on all the time, inside the network, with a reliable connection to the SQL Server. A dedicated always-on server is ideal. Someone's desktop that gets shut down every night is not. This sounds obvious and yet "the reports stopped refreshing because Dave went on leave and his PC was off" is a real support ticket we have seen more than once.

Third, you register a data source on the gateway. This is where you tell the gateway how to reach your specific SQL Server: the server name, the database, and the credentials it should use to connect. Those credentials matter. The gateway uses a stored account to run queries, so that account needs the right read permissions on the database and nothing more. Give it a dedicated service account with least privilege, not a personal login and definitely not an admin account. When someone leaves and their login gets disabled, you do not want the reports to fall over with them.

Fourth, back in the Power BI service, you map your dataset to the gateway and the data source you registered. Once that mapping is in place, you can set up scheduled refresh, pick how often it runs, and Power BI will use the gateway to pull fresh data on that schedule.

The traps we see

Most gateway problems are not the gateway's fault. They are setup and governance issues. A few we run into repeatedly.

The name-matching trap. The data source you register on the gateway has to match the server and database names in your Power BI dataset exactly. If your report was built pointing at the server by one name and the gateway is registered with a slightly different name, the mapping silently fails to appear and people burn an hour wondering why. Consistency in how you name and reference the server pays off here.

The single-point-of-failure trap. One gateway on one machine means one thing that can take down every refresh in the business. For anything important, run the gateway in a cluster, which is just a couple of gateway installs grouped together so that if one goes down the other keeps serving. It is not much extra work and it turns a fragile setup into a resilient one.

The credential-rot trap. The service account the gateway uses has a password, and in a lot of organisations passwords expire on a policy. When that account's password changes and nobody updates it in the gateway, every refresh that depends on it stops. This is one of the most common "everything broke overnight" causes we see. Use an account with a managed or non-expiring credential where your policy allows, and document which account is doing what so the next person can find it.

The permissions creep trap. It is tempting to give the gateway account broad access so you never have to think about it again. Resist. A read-only account scoped to exactly the databases and tables the reports need is safer, and when a security review comes around you can answer "what can this thing see" without wincing.

Why this is worth doing properly

A gateway that is set up well disappears into the background. Reports refresh on schedule, the data is current, and nobody thinks about it. A gateway that is set up carelessly becomes a recurring source of "the report is broken again" tickets that slowly erode people's faith in the whole Power BI programme. The difference between those two outcomes is almost entirely in the setup and the governance around it, not in the software.

This is exactly the kind of unglamorous plumbing that determines whether a data platform is trustworthy, and it is a big part of what we handle in our managed data and AI services. Getting the gateway, the service accounts, the refresh schedules, and the failover right is not hard, but it needs someone to actually own it rather than assume it will look after itself. Data capability that lives entirely in one person's head, or one person's laptop, is a risk to the business, and spreading that ownership properly is worth the effort.

If your organisation is connecting Power BI to SQL Server and you want it done in a way that is secure, resilient, and does not fall over the next time someone changes a password, our Power BI consulting team does this regularly. The Microsoft and Azure data work we do sits right alongside it, since the same gateway and governance thinking applies as businesses move towards Fabric and broader Azure data platforms.

The short version

The gateway is the bridge between your cloud Power BI reports and your on-premises SQL Server. Install it in standard mode on an always-on machine, register your SQL data source with a dedicated least-privilege service account, map your dataset to it, and set up scheduled refresh. Run it as a cluster if the reports matter, and keep an eye on the credentials so they do not silently expire.

Do that, and your reports stay current without anyone thinking about it. Skip it, and you are back to explaining why the dashboard still shows last Tuesday. If you want a hand getting it right, get in touch and we will sort it out with you.