Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DariaM
Regular Visitor

Power 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'user@mail.ru')

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)

4 REPLIES 4
v-luwang-msft
Community Support
Community Support

Hi @DariaM ,

Did the updated version fix the issue?

If your problem been solved,  please consider Accept a correct reply as the solution or share your own solution to help others find it.

 

Best Regards
Lucien

Hi @v-luwang-msft?,

 

Unfortunately the update didn't help (May 2022). I hope someone has come across a similar problem and can help find a solution. Thank you for your responses.

v-luwang-msft
Community Support
Community Support

Hi @DariaM ,

Sorry I can't reproduce your problem locally, would you try opening the report locally in Desktop and using view as roles to get the same problem?

vluwangmsft_0-1654502488961.png

Best Regards

Lucien

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.