Power BI Gateway SSO - When To Use It and What To Watch Out For

Single sign-on through the Power BI on-premises data gateway is one of those features that sounds simple in a sales deck and gets complicated about ten minutes into the implementation. The promise is great. A user opens a Power BI report, the report queries an on-prem SQL Server, and the SQL Server sees the request as coming from that specific user, not a shared service account. Row-level security in the database "just works". Audit logs make sense. Security teams stop sending you angry emails.

The reality is that getting there involves picking the right SSO method for your situation, configuring delegation correctly in Active Directory, and accepting that some data sources will work beautifully and others will fight you.

We've set this up enough times across Australian organisations - banks, insurers, a few state government departments - to know which parts are smooth and which parts will eat a week of your life. Here's the lay of the land if you're thinking about implementing gateway SSO.

Why you'd want SSO in the first place

Before getting into how, a quick sanity check on whether SSO is actually what you need.

The default behaviour of the gateway is to use a single service account for every query. Whatever credentials you configured when you set up the data source in the gateway, those are what hit the on-prem database. Every Power BI user gets the same database identity. It works, it's simple, but it has obvious problems if your database has row-level security or your auditors care about who ran which query.

SSO solves those problems by passing the actual end user's identity through to the data source. Now SQL Server (or whatever) sees the query as coming from [email protected] instead of [email protected]. Database-level security policies kick in. Audit logs show real users.

If you don't have row-level security needs and your auditors don't ask hard questions, you might not need SSO. The setup cost is real and the operational complexity is non-zero. We've talked clients out of SSO when their actual requirement was solved better with Power BI's built-in row-level security at the dataset level. Don't reach for the heavyweight solution if the lightweight one fits.

That said, when you need SSO, you really need it. There's no clean substitute for genuine end-user identity passthrough.

The three flavours of SSO

The gateway supports several SSO mechanisms and picking the right one depends on your environment and the data source you're connecting to.

Kerberos constrained delegation is the classic on-prem option. You configure the gateway service account to be able to delegate the user's Kerberos ticket to specific back-end services. The data source sees the user's Windows identity. This works well for SQL Server, Oracle, Teradata, SAP HANA, and a bunch of others. It's also the one that sets up Active Directory the most and the one where things go wrong if a domain admin gets the SPN configuration slightly off.

SAML-based SSO works for data sources that authenticate via SAML rather than Windows. SAP BW is the headline use case. Less common in Australian environments but it does come up, particularly in organisations that standardised on SAP a decade ago and still run BW.

Microsoft Entra ID SSO is the newer option. If your on-prem data source is Entra-aware (some cloud-hosted databases, some SaaS-connected services), you can pass the user's Entra ID token through. This is the cleaner option going forward but the support matrix is narrower than Kerberos.

The decision usually comes down to what your data source supports. SQL Server on-prem? Kerberos. SAP BW? SAML. A SaaS database that integrates with Entra ID? Entra ID SSO. There's not much overlap so you mostly don't get to choose.

What goes wrong with Kerberos setup

Kerberos delegation is where most teams get stuck. It's not Power BI's fault - Kerberos has always been finicky - but Power BI's documentation glosses over the Active Directory work that needs to happen.

A few things we see go wrong on real implementations:

SPNs in the wrong place. Every service the gateway needs to delegate to needs a Service Principal Name registered in AD. SQL Server needs one. The gateway service account needs to be configured to allow delegation to that SPN specifically. If the SPN is on the wrong account or missing, the Kerberos ticket exchange silently fails and you get a confusing "the gateway can't connect" error in Power BI Desktop.

The fix is methodical. Use setspn -L to verify the SPN is where you think it is. Use klist on the gateway server to confirm tickets are being issued. If you've got a Windows admin who knows Kerberos well, this is two hours of work. If you don't, it's a week.

Resource-based constrained delegation vs traditional. Microsoft has been pushing resource-based KCD as the modern approach but a lot of older AD environments are still on the traditional model. The gateway supports both but they're configured differently. Mixing them up causes problems. Pick one and stick with it across the environment.

UPN vs SAM account name mismatches. When the gateway passes the user's identity, it uses the UPN (user principal name like [email protected]). If your SQL Server is expecting Windows logins in the down-level format (DOMAIN\jane), there's a translation that needs to happen. Usually fine, occasionally not. Tests with a real user account before declaring victory.

Cross-forest scenarios. If your gateway is in one forest and your SQL Server is in another, Kerberos delegation gets harder. Trust relationships need to be in the right state. We had a project at a large insurer where the gateway and SQL Server were in different forests with one-way trust, and it took two weeks of working with their AD team to get delegation working. Doable but plan for it.

The configuration steps that actually matter

Setting up SSO on the gateway side is well documented in Microsoft's official article, so I won't rehash it. The bits where we see teams stumble:

Don't use a personal account for the gateway service account. Use a dedicated service account with a long random password (or even better, a managed service account if your environment supports them). Personal accounts get disabled when people leave, passwords change, and your reports break.

Give the gateway service account "log on as a service" rights. Sounds obvious but it's a common omission, especially when corporate group policy doesn't grant it by default.

Configure the data source in the gateway with the SSO option turned on. There's a checkbox in the data source configuration in the gateway management portal. It's easy to miss. Without it, the gateway falls back to using its own credentials regardless of how much Kerberos setup you did.

Test with a real user, not yourself as gateway admin. Gateway admins often have elevated permissions that mask SSO problems. Get a test user from outside your team to try a report end-to-end. If it works for them, it'll work for everyone.

For Power BI work across Australian organisations, we've made this part of our standard checklist. It's the difference between a deployment that quietly works and one that gets a support ticket every week.

Performance considerations nobody mentions

SSO adds latency. Every query the gateway runs now involves a Kerberos ticket exchange instead of reusing a cached service account connection. For reports that hammer the gateway with hundreds of queries on page load, this adds up.

The gateway maintains a connection pool, so the ticket exchange happens once per user per data source, not per query. But the first query a user runs in a session is noticeably slower than with shared-account auth. For reports that are loaded by many users sporadically, this hurts perceived performance.

A few mitigations:

Use import mode where you can. SSO is mostly relevant for DirectQuery and live connections. Import mode does the query once during refresh and then serves all users from the imported dataset, so SSO isn't part of the query path. If your data refresh frequency tolerates it, import is faster end-to-end.

Aggregations. For hybrid scenarios where some queries need DirectQuery and some can be cached, Power BI's aggregation features let you keep the high-volume queries fast while preserving SSO for the detail-level drill-throughs.

Right-size the gateway VM. SSO is more CPU-intensive than shared auth because of the encryption work. If your gateway is on a small VM and you turn on SSO for a heavily-used dataset, you'll see CPU spike. Monitor it. We typically recommend monitoring gateway performance as part of any production Power BI deployment.

When to invest in SSO and when to skip it

If you're a small-to-mid Australian business with one Power BI workspace, a handful of reports, and no row-level security requirements, gateway SSO is probably overkill. The shared service account model is simpler, faster to set up, and easier to maintain. The audit trail concerns are real but often addressable through other means.

If you're an enterprise with compliance requirements, multi-tenant data, or a security team that demands per-user accountability for database queries, SSO is the right call. Budget for the AD work, get your Kerberos done properly the first time, and document the configuration so the next person to touch it doesn't have to reverse-engineer the SPN setup from scratch.

We help clients make this call as part of our broader Microsoft Fabric and Power BI consulting work. The answer depends on more than the technology - it's about who owns the gateway, how often the data sources change, and what your audit posture needs to be.

Reference: Use SSO for DirectQuery in the Power BI service from the Power BI documentation.