0

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.

0

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:

  1. Open Active Directory Users and Computers
  2. Right-click the service account running Power BI Report Service, and click "Properties"
  3. Click the "Delegation" tab, then "Add", then "Users or Computers"
  4. Enter the service account of the DESTINATION SQL Server instance and click "OK"
  5. Click the MSSQLSVC Service that shows a port, whether it's the default 1433 or another port. If you have multiple named instances, add the entry for each port number.
  6. Click OK, then OK again.
  7. Restart the Power BI Report Server MACHINE, not just the service.
  8. Open a Power BI (.pbix) file that has uses DirectQuery against a different SQL Server instance.
  9. Credentials should now be passing from you (the client), to Power BI Report Server, and then on to any destination server(s).
  • Note that if you have a NAMED INSTANCE and did NOT set a static port, you may run into issues with SPNs as the port can potentially changed. I recommend setting a static port on the instance. – SomeGuy Jun 3 at 15:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.