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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
nivasnalla
Frequent Visitor

Direct query with dynamic row level security authentication

Hello, 

 

I am trying to figure out how the authentication mechanism work with direct query from the service to data gateway and to the  SQL Server. I know how the AS Live connection authentication work and I am curious on how the current logged user in the service gets access to the SQL server to run the query against the SQL database.  Does the direct query run under the account setup for the data source on the service ? Or it runs on the actually logged user on the service just like the AS live connection ? Also when we are filtering out the rows based on the function UserPrincipalName(). Does the rows get filtered on the SQL Server or on the service?

1 ACCEPTED SOLUTION
v-jiascu-msft
Employee
Employee

Hi @nivasnalla,

 

You can try a software like SQL Server Profiler then you will see it clearly what happens in the Server side. Please refer to the snapshot below. I defined a role [Color] = "Blue". 

Direct_query_with_dynamic_row_level_security_authentication

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-jiascu-msft
Employee
Employee

Hi @nivasnalla,

 

You can try a software like SQL Server Profiler then you will see it clearly what happens in the Server side. Please refer to the snapshot below. I defined a role [Color] = "Blue". 

Direct_query_with_dynamic_row_level_security_authentication

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Yep, that is right. I ran the SQL Profiler and I was able to see the actual filter defined at roles passed  to the  SQL Server and filtered the rows at SQL itself and not at the  PowerBI service for "Direct Query". I was previously thinking that the service would load all the data into the model first and filter gets applied at the service. 

 

I have noticed one more thing, when I am applying the row level security, if I make changes to the underlying data when I try to refresh the report for the first time, it istaking a while to load the data and any subsequent changes made to the data, report reflects almost instantly.  Is it because of browser cache or something?

Hi @nivasnalla,

 

It's hard for me to find out what exactly happens. But I think it's cache. The Direct Query mode doesn't store any data. It caches some data in order to enhance the performance. So it could take a while to modify the cache while the underlying data changes. 

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

When you set up your gateway you will select what User/Pass is sent from the Gateway to the source in order to provide authentication and grab the required data.

 

Row Level Security is handled by Power BI itself, thus you will grab all of the necessary data from the source and Power BI itself will apply the filtering to ensure the user only sees whatever is set up in the Roles they have been assigned inside the Power BI model. 

@Anonymous: Above explanation for Row level security is true for Import Query and not for Direct Query.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors