While preparing my Kerberos for BI session for SQL Grillen, I decided to introduce the May edition of Power BI Report Server as a new element in the demos.
While configuring Kerberos Constrained Delegation for Power BI Report Server is not very different from other setups, there are a few things that you need to be aware of before moving forward:
- You need protocol transition
- You might need to register a SPN for the MSOLAPDisco.3 service if running a named instance.
This blog post is an explanation on how to setup Kerberos Constrained Delegation for:
- Getting data from SSAS within a Power BI report
- Getting data from SQL with a Report Server report
Please keep in mind that at the date of writing, the preview of Power BI Report Server only supports SSAS live connection. More will be added a later point in time.
Installing Power BI Report Server is quite trivial and explained in detail in other blog posts, so I invite you to read those if you need info about that part:
and this great YouTube video by Dustin Ryan: https://www.youtube.com/watch?v=jfTrFEsoOBs
Why Constrained Delegation
In many demo cases, you will have an all-in-one server where you have installed Power BI Report Server, SSAS (tabular or multidimensional) and SQL Server. In those cases you don’t need any form for credentials delegation since the Report Server is on the same box than the data source.
But there are scenarios where you have a distributed environment like the one I have on my VMs demo domain and for jumping around servers and passing credentials around, you need to setup Kerberos Constrained Delegation. Furthermore you will need protocol transition for it to work in Power BI Report Server. Let me explain….
Without Kerberos Constrained Delegation, you will get that error when trying to view a Power BI report with a SSAS data source
And when connecting a report to a SQL Server it would look like that using integrated security
Virtual Environment Servers
|Domain Controller (OFL.Local)
SSAS Tabular (named instance called “TAB”)
SQL Server browser running as Network Service
|Power BI Report Server
SQL Server (not used by Power BI Report Server)
|SQL Server used for Report Server databases|
- Analysis Services: OFL\SSASSvc
- Power BI Report Server: OFL\SSRSSvc
- SQL Server: OFL\SQLSvc
Configuring Constrained Delegation
The most crucial step when configuring Kerberos Constrained Delegation is gathering the information necessary to the setup.
The service accounts are listed above and the first step is to make sure that we have the SPNs we need. For that we use
is available on every server but requires domain admin permissions to make change to the Active Directory.
In a previous post, I have explained how to give non Domain Admins permission to configure Constrained Delegation via Group Policies: https://theblobfarm.wordpress.com/2016/12/05/allowing-non-domain-admins-to-configure-kerberos-constrained-delegation/
1 Registering SPNs for Power BI Report Server
There are 2 cases:
Report Server running under a domain account using the server names and user accounts listed above:
SetSPN -s HTTP/SQL1 OFL\SSRSSvc
SetSPN -s HTTP/SQL1.OFL.Local OFL\SSRSSvc
Report Server running under Virtual Service Account or Network Service:
The server name should already have a SPN listed for HTTP. Control that using
SetSPN -L SQL1
Yields that result
2 Registering SPNs for the SSAS data source
- For the named instance:
SetSPN -s MSOLAPSvc.3/DC:TAB OFL\SSASSvc
SetSPN -s MSOLAPSvc.3/DC.OFL.Local:TAB OFL\SSASSvc
Since we are running a named instance, we need to register a SPN for the SQL browser service. If the SQL Browser service is running under Local System, Local Service or Network Service, you need to register a SPN using the computer name, else the domain account
- For the SQL Browser – running as Network Service in our case:SetSPN -s MSOLAPDisco.3/DC DC
SetSPN -s MSOLAPDisco.3/DC.OFL.Local DC
3 Registering SPNs for the SQL Server
Since I am also connecting to a SQL Server from the Report Server, I also need to register SPNs for it.
It is running under a default instance, so we only need to specify the port number.
Since my data source is on a database located on the server SHP.OFL.Local I need to add the SPNs below
SetSPN -s MSSQLSvc/SQL1.OFL.Local:1433 OFL\SQLSvc
SetSPN -s MSSQLSvc/SQL1.OFL.Local OFL\SQLSvc
4 Setting the Constrained Delegation
Now that we have all the SPNs in place, I need to find my Report Server Service account in Active Directory Users and Computers and click on the delegation tab. Be aware that this tab is only available if you have an SPN registered for that user.
Please, keep in mind that the internals of Power BI Report Server requires protocol transition so we’ll need to pick the right kind of delegation
Remember that the service account we are looking at is our “middle-man” for going from A to B. So this man in the middle need to be able to pass credentials forward from A to B. In that case B is our Analysis Services instance, more specifically it’s service account and the SQL Browser.
Call out :
- Add Service for delegation
- Click Users or Computers
- Choose the SSAS service account
- Add the MSOLAPSvc.3 Service
- Click Users or Computers
- Choose the DC computer account
- Add the MSOLAPDisco.3 Service
The expanded result should look like that
Repeat the same steps but this time add the SQL Server Service account (SQLSvc). Once done it should look like the result below
5 Modify the report server config file
The file is located here: C:\Program Files\Microsoft Power BI Report Server\PBIRS\ReportServer
By default, Power BI Report Server (and reporting services) is using the NTLM protocol (RSWindowsNegotiate) as shown below
we need to modify the rsreportserver.config file to use RSWindowsNegotiate (trying Kerberos and falling back to NTLM) or RSWindowsKerberos (Kerberos only) so it looks like that
6 Stop and Start the Power BI Report Server via the configuration tool
7 Test the configuration
The result should look like the image below
And for SQL Server data source
If you still get the error it is due to the cache mechanism. The best way to get rid of it is to wait or to reboot the Power BI Report Server if you are allowed to (When in doubt, reboot).
I hope that it has given you some more information around how to configure Power BI Report Server in a distributed environment and an idea of why Kerberos isn’t that complicated, as long as you are very specific in what you are trying to achieve.
Happy Power BI’ing !!