January 12, 2026 at 5:15 pm
Hi everyone,
Below is a consolidated summary of what we validated
Architecture & data path
The on-premises data resides in SQL Server, accessed by Power BI Service via on-premises Analysis Services (SSAS Tabular).
Effective flow:
Power BI Service ? Power BI Gateway ? SSAS Tabular ? SQL Server
The issue is not SQL connectivity, but authentication and delegation at the Gateway ? SSAS layer.
Authentication behavior & EffectiveUserName
We confirmed:
When switching authentication to enable impersonation:
Key conclusion: If a fixed Windows user is stored on the SSAS data source, Kerberos delegation will never trigger, even if delegation is perfectly configured.
This explains why: Kerberos is not observed end-to-end, user impersonation never occurs and the technical user is always resolved
TLS certificate chain & transport trust (first blocking layer)
“The certificate chain was issued by an authority that is not trusted.”
Because SSO/OAuth-based connections are stricter than Windows auth, TLS trust must be fully validated before delegation can succeed.
We need to confirm:
Federation & identity (second blocking layer)
Once TLS is confirmed clean, EffectiveUserName depends on:
Apart from this long list, is there anything else where this could be failing?
January 13, 2026 at 6:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
January 14, 2026 at 4:30 pm
With what you confirmed through testing, I would personally confirm in logs. There should be a log on one of the systems (gateway, SSAS, SQL) that indicates what the problem is. Trying to fix a problem like that when there are 4 points that need to be investigated, the first step is to determine where the problem is. Is the problem from web to gateway, gateway to SSAS, or SSAS to SQL when doing the impersonation. You confirmed that one of those is broken, but which one? It may be something as trivial as making sure your SPN's are set up correctly, or it could be one of the issues you listed.
BUT the very very first step is to determine where things are breaking down. Throwing every solution you are aware of at the problem MAY resolve things but it may end up being overkill. Review the logs, find the specific source of the problem and resolve that point first then work on the next one until all of them are resolved.
It sounds like the SSL certs are not trusted and fixing that is a pretty easy thing to do - add the root CA and intermediate CA to the local computer trusted root certificate store. But that fix probably needs to happen on the gateway, SSAS, and the SQL instance. Possibly even on the cloud side if you can configure that. Alternately, since you are connecting internal systems to external, you may need to get a certificate from a CA trusted by the cloud provider.
As for other things that could be failing, I'm sure there are more. Permissions (for example) for the account that is connecting to the systems for example. If userA has no permissions on SSAS but full control on Power BI, things could fail. And I'm sure there are other things. Depending on how certificates are handled, if they expire too far in the future, some systems will consider the expiration date invalid and thus the certificate invalid. So that is another consideration.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 19, 2026 at 3:37 pm
It is a while since I retired but my memory is that ssas effective user has nothing to do with Kerberos (with SQL2019). The authentication system in ssas dates from before MS purchased ssas back in the 1990s.
SSAS only accepts domain user authentication when installed in-house, and does not accept kerberos tokens. If for example you connect via SQL Server/SSRS/PoweBI via an impersonated user, ssas has no mechanism for recognising the kerberos token, so the login fails.
The only potential way forward is to use http connection to ssas. Most of the stuff on the web about enabling http for ssas is old, and does not mention you also need a http reservation. Unfortunately I ran out of project time while trying to set this up, but even if it had worked I was doubtful that it would support effective user. Although MS use http to connect to ssas, I think they also use a proprietary front end to get everything working, which is not available outside Azure.
Best of luck in getting this working. My info may be out of date by now
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
January 28, 2026 at 4:01 pm
Hi everyone,
many thanks for your input. The issue is now solved and I want to share some details in case someone else need them:
Root cause summary
The issue was not SQL Server connectivity, but authentication and delegation between the Power BI Gateway and SSAS.
Two independent blocking factors were identified:
When a fixed domain user is stored on the SSAS data source, EffectiveUserName is bypassed by design. In this configuration, SSAS always resolves the technical account and Kerberos delegation never triggers, regardless of delegation or SPN configuration. As a result, user impersonation cannot work.
Connection errors showed that the TLS certificate chain used by SSAS was not fully trusted by the gateway. SSO / OAuth-based connections enforce stricter TLS validation than classic Windows authentication. Until the full certificate chain (root + intermediates) is trusted, authentication fails before delegation can even occur.
How it was fixed
Key takeaway
If any fixed Windows credential is configured on the SSAS data source, EffectiveUserName will never work. Additionally, TLS trust must be clean before delegation troubleshooting is meaningful. Both conditions must be satisfied for Power BI ? Gateway ? SSAS user impersonation to function correctly.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply