User Profile
DariaM
Regular Visitor
Joined 4 years ago
User Widgets
Contributions
Re: Power BI Report Server RLS is error while query is passed to DB.
Good afternoon! We use PBIRS with a request through "DirectQuery". Manager roles don't work in desktop version in this situation. The problem started in March and it continues to this day. Updated to the latest version "May 2022". The problem occurs randomly on different reports.926Views0likes0CommentsRe: Power BI Report Server RLS is error while query is passed to MSSQL
Good afternoon! The problem started in March and it continues to this day. Updated to the latest version "May 2022". The version "January 2022" was previously installed. The problem occurs randomly on different reports with a request through "DirectQuery".555Views0likes0CommentsPower 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 2017637Views0likes2CommentsPower BI Report Server RLS is error while query is passed to DB.
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 (RTM-GDR) (KB4583456) - 14.0.2037.2 (X64) Nov 2 2020 19:19:59 Copyright (C) 2017 Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)966Views0likes4Comments
Data Privacy
Microsoft Fabric Community and Privacy
To learn more about how we manage your data, please review the Microsoft Fabric Community Data Privacy guide.