Connecting Power BI to Cloud Data Sources Without the Usual Headaches

Most Power BI problems I get called in for are not really report problems. They are connection problems wearing a report costume. The visuals are fine, the DAX is fine, but the data is stale, or the refresh failed overnight, or it works on someone's laptop and dies in the service. Nine times out of ten the root cause sits in how the report was connected to its cloud data source in the first place.

Connecting Power BI in the service to cloud data, Azure SQL Database, Synapse, Snowflake, Dataverse, an Azure Data Lake, all of it, is one of those things that feels trivial in the demo and gets genuinely fiddly in production. The demo has one small table and one person. Production has row-level security, scheduled refresh, service principals, gateways you did not think you needed, and a finance team that notices the second a number looks wrong.

Microsoft's documentation on connecting to cloud data sources lays out the menus. What it does not tell you is which decisions come back to bite you. That is what I want to cover, based on the setups we build and, more often, the ones we get called in to fix.

Two ways to connect, and why the difference matters

Before anything else, you have to be clear about which of the two connection modes you are using, because almost every downstream headache traces back to this choice.

The first is import. Power BI pulls the data out of the source, compresses it, and stores it inside the dataset, or semantic model as it is now called. Your report queries that in-memory copy, which is why import reports are fast. The catch is the data is only as fresh as your last refresh. If you want current numbers, you schedule a refresh, and that refresh has to be able to reach the source on its own, without you sitting there logged in.

The second is DirectQuery. Power BI stores no data. Every time someone opens the report or clicks a slicer, it fires a live query at the source. The data is always current, but the source has to answer fast every single time, and if a hundred people open the report at 9am on Monday, your database wears a hundred people's worth of queries. DirectQuery is the right call when data must be real-time or the volume is too large to import, and it is the wrong call when your source is a modest Azure SQL Database that will fall over under interactive load.

My rule of thumb after years of this: default to import unless you have a specific, named reason to use DirectQuery. Freshness is the usual justification people reach for, but a well-scheduled import refreshing every hour is fine for the overwhelming majority of business reporting, and it will not hammer your source or make every slicer click feel sluggish. Reach for DirectQuery when you actually need it, not because real-time sounds nice in a meeting.

The credentials question nobody enjoys

Here is where the service part gets real. When you connect in Power BI Desktop on your own machine, you are authenticated as you. It works because you have access to everything. Then you publish to the service, and the refresh runs at 3am when you are asleep and nobody is logged in as anybody. So the service needs its own credentials to reach the source, and setting those up is where people get stuck.

For a proper cloud source like Azure SQL or Synapse, you have a few options, and they are not equal. You can use basic SQL authentication with a username and password stored against the semantic model. It works, it is the easiest thing to set up, and I would rather you did not, because now there is a shared database password sitting in Power BI that somebody has to remember to rotate and that outlives the person who created it.

The better path for Microsoft cloud sources is OAuth with a service principal, or Entra ID authentication. You give an application identity read access to the database, and the semantic model authenticates as that identity. No password in Power BI, access governed by role assignments you can see and audit, and no mystery credential that breaks the day someone changes their password. It is more setup up front. It is dramatically less pain later, especially when you have twenty reports instead of one. This is exactly the kind of foundation our Power BI consultants put in place so that reporting scales without turning into a credential-management nightmare.

Whichever you pick, you set it once against the semantic model in the service, under the settings for that model, and every scheduled refresh uses it from then on. The most common "my refresh suddenly broke" ticket is a password that changed or a token that expired on a connection nobody was watching.

Do you need a gateway or not?

This one confuses people constantly, so let me be blunt about it.

For a source that lives fully in the cloud and is reachable over the public internet, Azure SQL Database, Snowflake, most SaaS APIs, Dataverse, you generally do not need an on-premises data gateway. The Power BI service can reach those directly. The gateway is for when your data sits somewhere the service cannot get to on its own.

Where it gets murky is network isolation. The moment you do the sensible security thing and lock your Azure SQL Database behind a private endpoint or a firewall that blocks public access, the Power BI service can no longer reach it directly either, and now you need a path in. That path is either a virtual network data gateway or a standard gateway sitting inside the network. So the more mature and locked-down your Azure environment is, the more likely you need a gateway even for "cloud" sources, which feels backwards until you think about what the firewall is actually doing.

The trap I see: a report works fine in development against an open database, gets promoted to production where the database is properly firewalled, and refresh dies with a connection error that reads like a credential problem but is actually a networking one. If you are planning to secure your data sources, and you should be, plan the gateway or virtual network story at the same time, not after the fact when a dashboard has already gone dark.

Row-level security and the identity that runs the refresh

One more thing that catches teams out when connecting to cloud sources: whose permissions apply.

With import, the refresh runs as the service credential, so that credential needs to see all the data you want in the model. If you have got row-level security you want enforced, you enforce it in Power BI with RLS roles, not by hoping the source filters it, because the source sees one identity doing the refresh and hands over everything that identity can read.

With DirectQuery it gets more nuanced, because you can pass the user's identity through to the source in some configurations, so the database itself applies per-user security. That is powerful and it is also a much bigger setup, and if you get it subtly wrong people see data they should not. If per-user security at the database level is a hard requirement, treat it as its own design exercise rather than a checkbox, and test it with real user accounts, not just your admin login that can see everything anyway.

What good looks like

When we set this up properly for a client, the pattern is fairly consistent. Import mode unless there is a concrete reason not to. Service principal or Entra authentication rather than stored passwords. A refresh schedule that matches how fresh the data genuinely needs to be, not the most aggressive option available. Gateways planned in step with network security rather than bolted on when refresh breaks. And RLS handled deliberately in one clear place rather than assumed to happen somewhere in the stack.

None of that is exotic. It is just decisions made on purpose instead of by default, which is most of what separates a reporting setup that runs quietly for years from one that generates a support ticket every fortnight. The teams who struggle are almost never struggling with Power BI the tool. They are struggling with connections and credentials and networking, the plumbing that the demo skips over. Getting that plumbing right is a lot of what our broader Microsoft Fabric consulting work involves, because Fabric and Power BI share the same connection model underneath.

Where to start

If you have a report that fails refresh unpredictably, do not start by looking at the report. Start at the connection. Check whether it is import or DirectQuery, check what credential the semantic model uses and whether it still valid, and check whether the source has quietly been firewalled since the report was built. That covers the majority of refresh failures I have ever diagnosed.

And if you are building something new, spend the extra hour on service principal authentication and a sensible refresh schedule before you publish. It is the least glamorous part of the job and the part that determines whether anyone has to think about this report again after it ships.

If you would like a hand designing a Power BI setup that connects cleanly to your Azure or cloud data and keeps refreshing without drama, that is squarely what we do. Have a look at our data and analytics services or just get in touch and we will take a look at what you have got.