Direct query with dynamic row level security authentication

[nivasnalla]

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?

[v-jiascu-msft · Accepted solution]

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". 

Best Regards,

Dale

[nivasnalla]

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]

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

[Anonymous]

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. 

[nivasnalla]

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