Report server direct query with windows authentication gets SSAS connection error

[pbiuser1234]

Hi all,
I installed Power BI Report Server (developper edition).
Running direct query report with windows authentication is OK on report server url inside the server itself,
But I get an error for the same user outside the server saying "We couldn’t connect to the Analysis Services server"
I don't understand why is it trying to connect to SSAS, Data source is defined SQL server DB, not SSAS,

Also I have not installed SSAS at all. 
Note that If I change the data source to specific credentials (same user) it works OK.
Does this mean that SSAS installation is mendetory using power bi?
If not, why do I get this error? and only at the specific situation (windows authentication + running outside the machine)
Thanks in advanced

[d_gosbell]

The issue you are describing points to the fact that you either have not configured Kerberos authentication or it is not working correctly. You can find out how to configure Kerberos here https://docs.microsoft.com/en-us/power-bi/report-server/configure-kerberos-powerbi-reports 

Normal Windows auth can only do a single "hop"

eg  Client ----> Report Server

This is fine for import reports, but for Direct Query the authentication needs to go

 Client ---->  Report Server  ---X-->  Database Server

And it fails on the second "hop" from Report Server to Database Server. Configuring Kerberos will fix this by providing a secure mechanism to pass credentials across multiple "hops". (this is why the report works when you run it on the server as you have elimintated one of the "hops" where credentials need to be passed between machines)

[pbiuser1234]

Thanks a lot for the reply,
Regarding the eliminitation of one of the "hops" - in my case, SSMS is not installed on the power bi machine - database is on a different server, so when I run the report from the machine and it works, If I understand you correctly, there are still credentials that are passed between machines.
Regarding the Kerberos issue - I have allready set spn as the article applies, step 1 and 2.
Other steps refer to the Analysis Services service - which I have not installed - that is why I asked is SSAS installation a mendetory? since sql database is on a different server.
Thanks in advanced

[d_gosbell · Accepted solution]

---

@pbiuser1234 wrote:

Thanks a lot for the reply,
Regarding the eliminitation of one of the "hops" - in my case, SSMS is not installed on the power bi machine - database is on a different server, so when I run the report from the machine and it works, If I understand you correctly, there are still credentials that are passed between machines.

---

Yes, when you use windows authentication there are still credentials that pass between machines

---

@pbiuser1234 wrote:

Regarding the Kerberos issue - I have allready set spn as the article applies, step 1 and 2.

---

The SPNs are only part if it, you also have to have the constrained delegation configured in AD and if you have the slightest thing wrong in any part of the configuration it just does not work - you don't get a nice error message. If you run a profiler trace against SQL Server while trying to run the report you will probably see an anonymous user attempting to connect.

---

@pbiuser1234 wrote:

Other steps refer to the Analysis Services service - which I have not installed - that is why I asked is SSAS installation a mendetory? since sql database is on a different server.
Thanks in advanced

---

Installing SSAS is optional, but both SQL and SSAS use windows authentication so the if you have both you need to configure Kerberos for both.

The reason you get the error that Analysis Services could not connect is probably because Power BI Report Server includes an embedded version of SSAS - this is what runs the tabular models in your pbix reports. So that is the thing that is translating the queries into SQL and submitting them to the database server. But you do not need to install or configure it separately - it's an internal part of Power BI Report Server.