Configuring Power BI Report Server Preview for Kerberos Constrained Delegation

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:

https://powerbi.microsoft.com/en-us/documentation/reportserver-install-report-server/

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

The setup

Virtual Environment Servers

DC SQL1 SHP
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

Service Accounts

  • 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

SetSPN

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

  1. 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
  2. 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

  1. 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

  1. 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 :

  1. Add Service for delegation
  2. Click Users or Computers
  3. Choose the SSAS service account
  4. Add the MSOLAPSvc.3 Service
  5. Click Users or Computers
  6. Choose the DC computer account
  7. 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 !!

Advertisements

One comment

  1. […] Regis Baccaro shows how to set up constrained delegation when connecting Power BI Report Server to a…: […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: