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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors