DirectQuery from Report Server to remote MSSQL-Server isn't working
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.
Power BI Report Server RLS is error while query is passed to MSSQL
RLS error sometimes occurs when reports are run in Power BI Report Server. In order to track what query is transferred to the database, we installed Profiler on MSSQL. As a result, we got that query was transferred without user when RLS fell, an example of a query received from PBI in MSSQL: select fn.system_code , fn.country , fn.region , fn.Client_type , fn.total_sum , fn.od_dt , ul.local_user FROM dbo.get_User_List() ul cross apply dbo.pb_return (ul.email) fn At the same time, multiple queries can be simultaneously passed from PBI to MSSQL when report is executed, but the RLS error set only in the first query. The error is floating, and if the first request was successful, then the report is generated successfully until the session ends. An example of a request from PBI to MSSQL, where RLS worked correctly: SELECT TOP (1000001) [t0].[Client_type], (COUNT_BIG(DISTINCT [t0].[od_dt]) + MAX(CASE WHEN [t0].[od_dt] IS NULL THEN 1 ELSE 0 END)) AS [a0],SUM([t0].[equity]) AS [a1] FROM ( ( select fn.system_code , fn.country , fn.region , fn.Client_type , fn.total_sum , fn.od_dt , ul.local_user FROM dbo.get_User_List() ul cross apply dbo.pb_return (ul.email) fn ) ) AS [t0] WHERE ([t0].[local_user] = N'[email protected]') GROUP BY [t0].[Client_type] Why is there a problem happened with RLS? And is there any solution? PBI RS Version 1.13.8086.22725(January 2022) Microsoft SQL Server 2017