I have a Power BI Report Server working perfectly connecting to SQL Server machines with a default instance only. I can also connect to SSAS
named instances with the proper SPNs and by allowing Kerberos constrained delegation
from the Power BI service account to the MSOLAPSVC.3 service
(SQL Server Browser). I believe I have all proper SPNs set up properly as I can run linked servers and query just about anything from anything. Kerberos Configuration Manager shows green check marks for everything.
My only problem is connecting to named instances of the SQL Server engine (not SSAS). I get the error below, which you would normally get if SPNs are not registered properly.
DESCRIPTION: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Reason: Could not find a login matching the name provided. [CLIENT:
x.x.x.x]
Is there a difference in how named instances must be connected to from Power BI (and perhaps SSRS as well)? I am trying to do a DirectQuery against a remote named instance of SQL Server in a .pbix file in my environment.
Best Answer
After hours of troubleshooting, I've got this working. I had to allow the PBI service account to pass credentials to the destination SQL Server(s) on an EXPLICIT port, even when connecting to a default instance on 1433. If anyone has any input, feel free to comment, but here's what worked for me: