Scenario
Power BI report server is widely used in Company environments. Usually in an OnPremise Active Directory domain, several Windows servers with different roles are deployed.
In this blog, I will describe how to configure user single sign on access to Power BI report server: the underlying protocol for authentication will be Kerberos and its constrained delegation which allows the Power BI report server to impersonate the connected user while accessing external data.
Let’s assume we have three different servers deployed in a “sample.local” Domain:
- SSRS Power BI report server (running under Group Managed service account SAMPLE\SSRSsvc$)
- SQL1 Microsoft SQL server (running under domain account SAMPLE\SQL1svc)
- SQL2 Microsoft Analysis service (running a named instance TABULAR under the domain account SAMPLE\SSASsvc)
Kerberos configuration for the SQL services
First part of the configuration requires the definition of the Service Principal Names (SPN) for all the SQL services.
An user with Domain Administration privileges is required.
From an administrative command prompt with Domain admin rights:
- Register 2 SPN names for the SQL1 services: (with and without the MS SQL port number)
SetSPN -s MSSQLSvc/SQL1.sample.local:1433 SAMPLE\SQL1svc
SetSPN -s MSSQLSvc/SQL1.sample.local SAMPLE\SQL1svc - Register 4 SPN names for the SQL2 services: (2 for SSAS and 2 for SQL Browser service)
SetSPN -s MSOLAPSvc.3/SQL2.sample.local:TABULAR SAMPLE\SSASsvc(where TABULAR is the instance name)SetSPN -s MSOLAPSvc.3/SQL2:TABULAR SAMPLE\SSASsvc
SetSPN -s MSOLAPDisco.3/SQL2.sample.local SQL2
SetSPN -s MSOLAPDisco.3/SQL2 SQL2
Kerberos configuration for BI Report server
Second part of the configuration requires the definition of the Service Principal Names (SPN) for the Power BI report server.
From an administrative command prompt with Domain admin rights:
- Register 2 SPN names for the SSRS server:
SetSPN -s HTTP/SSRS.sample.local SAMPLE\SSRSsvc$
SetSPN -s HTTP/SSRS SAMPLE\SSRSsvc$
The Power BI must be authorized for Kerberos constrained delegation: if SSRSsvc user is a standard one, this could be done also through the user properties in the Active Directory Users & Computers GUI. If this service user is a group managed service account, this is not possible!
Let’s delegate it using the PowerShell which always works in both cases.
From a PowerShell admin window:
Set-ADAccountControl -Identity SAMPLE\SSRSsvc$ -TrustedForDelegation $false -TrustedToAuthForDelegation $false
Set-ADComputer -Identity SAMPLE\SSRSsvc$ -Add @{'msDS-AllowedToDelegateTo'=@( 'MSSQLSvc/SQL1.sample.local:1433, 'MSSQLSvc/SQL1.sample.local','MSOLAPSvc.3/SQL2.sample.local:TABULAR', 'MSOLAPSvc.3/SQL2:TABULAR','MSOLAPDisco.3/SQL2.sample.local SQL2','MSOLAPDisco.3/SQL2 SQL2')}
The first command enables the Kerberos-constrained delegation / Trust this computer for delegation to specified services only (Use Kerberos only) while the second one lists all the 6 SPN names we have defined in the previous step for SQL1 and SQL2 allowing the report server to impersonate the connected user while accessing data on SQL1 and SQL2.
NOTICE
Be sure that the Power BI report server is configured to access SQL database with the full server name: in this example SQL1.sample.local.
If the configuration refers to the short NETBIOS name (SQL1), edit it in the configuration by repointing the database!
Change the Power BI report server authentication
Last step of the configuration requires the change of the Power BI report server configuration.
The file rsreportserver.config (the default location for this file is C:\Program Files\Microsoft Power BI Report Server\PBIRS\ReportServer) must be edited:
find the Authentication/AuthenticationTypes section and set the parameters this way:
<AuthenticationTypes>
<RSWindowsNegotiate/>
<RSWindowsNTLM/>
</AuthenticationTypes>
With this configuration, the Power BI report server will attempt a Negotiate auth first with Kerberos, with NTLM fallback, required for client devices which doesn’t support Kerberos (like the mobile ones!)
Last step
Restart all the three servers to load the new Kerberos configuration and test the user single sign on access.
Remember that all the user web browsers must be configured to trust the Power BI report server name and forward the user credentials: the configuration is different from browser to browser so specific configurations must be followed for MS Edge, Firefox, Chrome, Safari, ….
Alessandro Romboli
Author
