DirectQuery from Report Server to remote MSSQL-Server isn't working

[Sal_]

Hello Community,

i am trying to configure a working DirectQuery from my Report Server to an MSSQL-Server which is not running ob the same server. But i can't figure out how to configure the authentication in the right way.

I build an report with DirectQuery in Power IB Desktop, everything works fine. When i publish the report to my report server and start it in the web frontend, i get an error message:

An unexpected error occurred. Please try again later.
Please try again later or contact support. If you contact support, please provide these details.
We couldn't connect to the Analysis Services server. Make sure you've entered the connection string correctly.

At the same time the log of the MSSQL-Server shows an login failure "Login failed for user 'NT AUTHORITY\ANONYMOUS'." from the Report Server. So it seems that my login at the web frontend isn't passed to the MSSQL-Server.

I've done some research and found an possible solution in the documentation:

• https://docs.microsoft.com/en-US/power-bi/report-server/configure-kerberos-powerbi-reports

A Domain-Admin helped me to configure the SPNs in the way that the documentation says, but it won't work.

Here are some information about the current setup:

• Server 1: "Domain\Server_MSSQL"
  • Application: MSSQL-Server 2019 (Developer); only Windows-Authentication
  • Instance: default
  • Service "MSSQLSERVER": User "Domain\SQL-Server"
  • Service "MSSQLServerOLAPService": User "Domain\SQL-Server-Analysis"
  • Service "SQLBrowser": User "Local Service"
• Server 2: "Domain\Server_PBIRS"
• Application: Power Bi Report Server Jan 2022 (Developer)
• Service "PowerBIReportServer": User "Domain\PBIRS-Service"
• PBIRS-User Datenbank: "Domain\PBRIS-DB"
• PBIRS-User Unattended User: "Domain\PBIRS-Service"
  (for testing, will be "Domain\PBIRS-Unattended" after Problem is solved)

*Server- and Usernames in here are just dummys*

We have done the following steps:

Edit rsreportserver.config

<Authentication>
	<AuthenticationTypes>
		<RSWindowsNTLM/>
		<RSWindowsKerberos/>
	</AuthenticationTypes>
	<EnableAuthPersistence>true</EnableAuthPersistence>
	<RSWindowsExtendedProtectionLevel>Off</RSWindowsExtendedProtectionLevel>
	<RSWindowsExtendedProtectionScenario>Proxy</RSWindowsExtendedProtectionScenario>
</Authentication>​

Register SPNs

• SetSpn -l *Domain\Server_MSSQL*

Registered SPN for CN=*Server_MSSQL*,OU=*Datenbank-Computer*:
        MSOLAPDisco.3/*Server_MSSQL*
        MSOLAPDisco.3/*Server_MSSQL*.*Domain*
        WSMAN/*Server_MSSQL*
        WSMAN/*Server_MSSQL*.*Domain*
        TERMSRV/*Server_MSSQL*
        TERMSRV/*Server_MSSQL*.*Domain*
        RestrictedKrbHost/*Server_MSSQL*
        HOST/*Server_MSSQL*
        RestrictedKrbHost/*Server_MSSQL*.*Domain*
        HOST/*Server_MSSQL*.*Domain*​

• SetSpn -l *Domain\Server_PBIRS*

Registered SPN for CN=*Server_PBIRS*,OU=*Computer*:
        WSMAN/*Server_PBIRS*
        WSMAN/*Server_PBIRS*.*Domain*
        TERMSRV/*Server_PBIRS*
        TERMSRV/*Server_PBIRS*.*Domain*
        RestrictedKrbHost/*Server_PBIRS*
        HOST/*Server_PBIRS*
        RestrictedKrbHost/*Server_PBIRS*.*Domain*
        HOST/*Server_PBIRS*.*Domain*​

• SetSpn -l *Domain\PBIRS-Service*

Registered SPN for CN=*PBIRS-Service*,OU=*Service-User*:
        HTTP/*Server_PBIRS*
        http/*Server_PBIRS*.*Domain*​

• SetSpn -l *Domain\SQL-Server-Analysis*

Registered SPN for für CN=*SQL-Server-Analysis*,OU=*Service-User*:
        MSOLAPSvc.3/*Server_MSSQL*
        MSOLAPSvc.3/*Server_MSSQL*.*Domain*​

• SetSpn -l *Domain\SQL-Server*

Registered SPN for für CN=*SQL-Server*,OU=*User*:
        MSSQLSvc/*SQL-Server*
        MSSQLSvc/*SQL-Server*.*Domain*​

Configure Delegations for "Domain\PBIRS-Service"

• Trust this user for delegation to specific services only
• Use any authentication protocol
• Services
  

  Service Type	User oder Computer	Port	Service Name
  MSOLAPDisco.3	*Server_MSSQL*
  MSOLAPSvc.3	*Server_MSSQL*.*Domain*
  MSSQLSvc	*Server_MSSQL*.*Domain*

We think we missed some detail, but can't figure out by ourselfs what it is. So i hope someone can help us to fix it, so i can start installing the productive system.

[sheigor]

Developer Edition?
Are you sure you have enable network protocols for SQL and SSAS?
https://docs.microsoft.com/en-us/sql/relational-databases/lesson-2-connecting-from-another-computer?...

[Sal_]

Hello @sheigor,

thank you for your reply.

When i enter administrator-credentials in the datasource configuration of the report i get the data. But this is in my opinion only an workaround. I'm looking for an solution to get it running without giving extra credentials to my users.

The problem is the forwarding of the login credentials from the report server to the database, so i can configure the userrights for my tables and views.

kind regards

Sal_

[v-luwang-msft]

Hi @Sal_ ,

I have seen the same problem before and it is due to Power BI Desktop for RS and Power BI Report Server versions. You can try updating PBRS and Power BI Desktop for RS to the latest version and try again.

Similar error refer:

https://community.powerbi.com/t5/Report-Server/PowerBI-RS-May-2019-Desktop-Error-Deploying-to-SSRS-D... 

Download url:

https://www.microsoft.com/en-us/download/details.aspx?id=56722 

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[Sal_]

Hi Lucien (@v-luwang-msft),

first of all thank you for your reply.

I've read many solutions in this community too and tried most of the solutions - exept the upgrade obviously.

Today i made the upgrade of my Report Server to Version May 2022, but the Problem is still the same. I can't run my DirectQuery report and i can see an anonymous login attempt at the MSSQL-Server.

I still think there is something wrong with my configuration. Maybe something in the configfiles of my Report Server, maybe something with the SPNs maybe something i didn't find in other solutions or documentations.

Unfortunately the upgrade wasn't the solution for my problem.

Best regards
Sal_