Power BI Report Server Kerberos for a SQL datasource

Marries — Wed, 15 Aug 2018 09:01:05 GMT

Hi there.

We have multiple Power BI reports deployed to our On-Prem Power BI Report Server.

PS! It's a distributed environment....with SSRS on one server, SSAS on another and SQL on another.

Some reports are using SSAS as a data source.... and to get around the Kerberos issue, we use Windows Auth --> hard code a domain user --> and tick the box "Log in using these credentials, but then try to impersonate the user viewing the report". That is working fine for now...for SSAS only.

The problem is that when you are using a SQL data source with a Direct connection.

For some reason you don't get the same tick box "Log in using these credentials, but then try to impersonate the user viewing the report".

We need to be able to see who the user is that is using the report....for security and audit reasons.

Am I missing a setting or a config somewhere?

Or is the only option to use Kerberos with SPNs?

Something that is also very odd.... even on the SQL data source report...when you execute the report, and it fails.... part of the message says: "We couldn't connect to the Analysis Services server....". Is this maybe just a bug or old msg that wasn't updated?

I hope someone can point me in the right direction on how to get a report running...with Windows Auth... in the distributed environment.

I think I have watched all the youtube videos explaining Kerberos and SSAS...but I haven't seen something for SQL data source.

Regards,

Marius

Re: Power BI Report Server Kerberos for a SQL datasource

Anonymous — Wed, 15 Aug 2018 10:19:05 GMT

Hi Marius,

I had some fun setting Kerberos authentication up for a SQL data source too, and can relate to your issues!

You should get the "As the user viewing the report" option for Direct Query, are you definately selecting "Windows Auhtentication" from the authentication type drop down?

One "gotcha" which i encountered, when setting the SPNs for MSSQLSvc, you have to use the port number - it did not work without. So the SPNs i created for my service account running SQL were:

MSSQLSvc/myserver:1433
MSSQLSvc/myserver.fqdn.domain.com:1433

I found a guide on configuring Kerberos for SSRS/SQL Server which was really useful and applied to PBIRS:

http://download.microsoft.com/download/B/E/1/BE1AABB3-6ED8-4C3C-AF91-448AB733B1AF/SSRSKerberos.docx

Hope this helps!

topic Re: Power BI Report Server Kerberos for a SQL datasource in Report Server

Power BI Report Server Kerberos for a SQL datasource

Re: Power BI Report Server Kerberos for a SQL datasource