Connecting Power BI to SSAS and effective user not working

• Paul Hernández

  SSCarpal Tunnel

  Points: 4963

  More actions

  January 12, 2026 at 5:15 pm

  #4715437

  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 Windows authentication with a fixed domain user is configured on the SSAS data source, the dataset loads and reports are visible.
  • In this mode, EffectiveUserName is bypassed by design.
  • SSAS always resolves the technical account, regardless of the interactive user.

  When switching authentication to enable impersonation:

  • The dataset becomes unavailable in the report.

  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)

  • We reviewed certificate stores on both the gateway and data source servers.
  • Root and intermediate CAs appear to be present on both sides. However, earlier connection errors explicitly referenced:

    “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:

  • Confirm the exact certificate used by SQL Server / SSAS for TLS.
  • Ensure the full certificate chain (Root + Intermediate CAs) is trusted on the gateway server.
  • Validate EKUs (Server Authentication) and certificate expiry.
  • Retest connectivity after confirming full chain trust.
  • Until TLS trust is unequivocally clean, delegation troubleshooting cannot reliably progress.

  Federation & identity (second blocking layer)

  Once TLS is confirmed clean, EffectiveUserName depends on:

  • AD ? Entra ID (Azure AD) federation and synchronisation
  • Correct UPN suffix alignment

  Apart from this long list, is there anything else where this could be failing?

  Paul Hernández