Microsoft Entra SSO for Power BI - Getting Row-Level Security Right Through the Gateway

Most Power BI security problems we get called in on come down to one question that nobody answered properly at the start. When a person opens a report and it queries a database behind the company firewall, whose identity is doing the querying? If the answer is "a single service account that sees everything," then every clever bit of row-level security you built in the model is sitting on sand, because the database has no idea who is actually looking.

Microsoft Entra single sign-on, working through the on-premises data gateway, is the piece that fixes this. It carries the real user's identity all the way from the report down to the source system, so the database itself can enforce who sees what. For any Australian organisation with sensitive data and regulatory exposure, finance, health, government, this is not a nice to have. It is the difference between row-level security that is real and row-level security that is theatre.

We do a lot of this work, and the setup is fiddly in ways the documentation glosses over. So let me walk through what it does, why it is worth the effort, and the parts that reliably go wrong.

The problem SSO solves

Picture the normal setup. You have a Power BI report in the service, in the cloud. The underlying data lives in a SQL Server or an Analysis Services instance on a server in your own datacentre. To bridge that, you install the on-premises data gateway, which sits inside your network and relays queries from the cloud down to the database.

Here is the catch. When the gateway connects to that database, it has to authenticate somehow. The default and easiest way is to store one set of credentials on the gateway, a single account, and every query from every user flows through that one account. The database sees one identity no matter who opened the report. So if your security model depends on the database knowing the real user, it cannot work. You are forced to push all the filtering up into Power BI itself, which is fine for some scenarios and completely inadequate for others.

Entra SSO changes the deal. When it is configured, the gateway can pass the actual user's Entra identity through to the data source. The database receives a query that says, in effect, "this is Priya from the Brisbane finance team asking," and it can apply its own permissions, its own row-level security, its own auditing against that real identity. The filtering happens at the source, where it is hardest to bypass.

This is the foundation a lot of good governance sits on, and it is a regular part of our Power BI consulting work. When clients tell us their reports are secure, the first thing we check is whether identity actually reaches the source or stops at the gateway.

How it works under the hood

There are two main ways the gateway can do SSO, and which one you use depends on your source.

The first is Kerberos constrained delegation. This is the older, well trodden path for sources like SQL Server and SAP. The gateway is configured so that it is allowed to impersonate the calling user against the data source. When a query comes in, the gateway uses Kerberos to delegate the user's identity to the database. It works well, but the setup involves Active Directory, service principal names, and delegation settings, and it is exactly the sort of thing where one wrong SPN means nothing works and the error messages tell you very little.

The second, newer path is Entra ID based SSO, where the user's Entra token is used more directly to authenticate to cloud aware sources. This is cleaner where it is supported because it sidesteps a lot of the on-premises Kerberos plumbing. The catch is that not every source supports it, so you do not always get to choose the easy path.

In both cases the principle is the same. The gateway stops being the identity and starts being a courier for the real user's identity. That shift is the whole point.

Why it is worth the trouble

I will be blunt. SSO setup is one of the more annoying things in the Power BI stack. So why do it.

Because the alternative is brittle. If you enforce all your security inside Power BI using row-level security roles in the model, you are maintaining a second copy of permissions that already exist in the source. Two copies means two places to get out of sync, and security that drifts out of sync is worse than no security because it gives false confidence. With source level enforcement through SSO, there is one set of rules, in the database, and Power BI inherits it.

It also matters for audit. When a regulator or an internal review asks "who accessed this data and what could they see," an answer of "everything went through one gateway account" is not an answer anyone wants to give. With SSO, the source system's own audit logs show the real users. For regulated industries that is not optional, and we spend a good deal of time on exactly this with clients in our AI for financial services and AI for healthcare work, where the question of who saw which record is the entire ballgame.

What trips people up

A few patterns come up again and again.

The first is assuming SSO is on when it is not. You configure the data source in the gateway, tick the box that says use SSO via Kerberos or via Entra, and you assume it took. But if the underlying delegation is not set up correctly, the gateway silently falls back to the stored credentials, and everything appears to work because reports still load. You only discover the problem when you realise everyone is seeing data they should not. The fix is to test with a genuinely restricted user account, not an admin, and confirm they see only their slice. Never trust that SSO is working just because the report renders.

The second is the Kerberos delegation maze. Service principal names have to be registered correctly, the gateway service account needs the right delegation permissions, and the data source has to be reachable in the way the delegation expects. Get one piece wrong and you get a generic credentials error that points you in entirely the wrong direction. Budget real time for this. It is rarely a five minute job, and it usually involves someone with Active Directory access who is not on the BI team, so coordination is half the battle.

The third is mixing up identity types. Entra identities and on-premises Active Directory identities are related but not the same thing, and the mapping between them has to be sound for delegation to work. In organisations with a messy hybrid identity setup, and there are many, this is where things fall over. If your Entra Connect sync is not clean, your Power BI SSO will not be clean either, because it is built on top of that.

The fourth is gateway clustering and load. As usage grows you will want more than one gateway node for resilience, and every node has to have the delegation configured identically. We have seen reports that work for half the users and fail for the other half purely because one node in the cluster was set up differently. Treat the whole cluster as one thing and configure it consistently.

How we approach it

When we set this up for a client, we work backwards from the audit requirement. We ask what the source system needs to know about the user, then we make sure that identity survives the whole journey from the report down to the database. We test with real restricted accounts at every stage, not admin accounts, because admins see everything and hide exactly the failures you are trying to catch.

We also push hard on keeping security at the source wherever the source supports it, and only using Power BI row-level security for the cases where it genuinely cannot. One set of rules in one place beats two sets that drift. And we document the delegation setup properly, because the person who has to debug it in eighteen months will not remember the SPN they registered today, and neither will you.

If your Power BI estate has grown organically and you are not actually sure whether user identity reaches your data sources, that is worth checking before someone else checks it for you. Our Microsoft Fabric and Power BI specialists do exactly this kind of review, and you can get in touch if you want a hand pulling it apart.

The bottom line

Microsoft Entra SSO through the data gateway is not glamorous and it is not fun to set up. But it is the thing that makes Power BI security honest. It moves enforcement to the source, gives you real audit trails, and stops you maintaining two copies of the same permissions. The setup will cost you a day or two of careful work and some coordination with whoever owns your identity platform. For anything touching sensitive data, that is a small price for security that actually holds up when someone tests it.

Reference: Power BI gateway and Microsoft Entra single sign-on documentation