Forum Discussion
Filtering reports based on user accounts - Authenticaion
So currently I extract the data from a SQL Server Db, rather than a direct query. Its also woth mentioning the agents do not have any user permissions to the underlying Db, so im unable apply the authentication at the Db layer.
In that case your options are pretty limited.
Right now you can only really filter in the way you are suggesting using row level security. There is support for RLS in imported data sets on the cloud service but not for the PBI-SSRS product
Currently on PBI-SSRS you have to use a SSAS Tabular model (this can of course just expose your underlying SQL tables) and implement RLS there. I generally prefer this approach anyway as I like having an abstraction layer between the DB and the reports. I also tend to use views rather than tables as sources in my SSAS Tab models. This gives me an extra layer that lets me whack the data with a hammer to get into the right shape if it decides to be un-cooperative.
You don't need to give everyone access to the SSAS Tab just a service account, you setup the data source in SSRS-PBI to impersonate the user internally, though if this is split across multiple servers you may well need to configure Kerberos so credentials can be passed around.
There is a new version of PBI-SSRS due imminently. It may support RLS in imported data sets as that's been supported in the cloud service for quite a while and it's been a high priority moan here on the forums for a while (understandably).
So it could be worth waiting a couple of days to see what the latest shiny version will do for you. Of course using the latest shiny version comes with a whole different set of risks. But thats the way the cookie crumbles etc.