Forum Discussion

nivasnalla's avatar
nivasnalla
Frequent Visitor
7 years ago
Solved

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...
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    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

nivasnalla's avatar
nivasnalla
Frequent Visitor
7 years ago

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?

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
7 years ago

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