Filtering reports based on user accounts - Authenticaion

[rax99]

Hi,

Firstly apologies as I am new to this admin side of reports developing. 

So I have a question around authentication for individual users to a report.

My report currently contains a filter which filters a performence based report on a specific agent. My report will need to be deployed to a report server or embedded into a SharPoint webpart in order for the agents/users to consume.

My question is, how do I add a level of security to my report to allow users to view filtered report based on thier account. So what I essentially need is Agent1 to view the report that has been specifically filtered to his/her agent ID. Is there a way of passing in the user windows/powerbi credentials into the report layer and filter the report based on their userid?

Right now my report has every agentID listed where it is filterable/selectable based on the desired agent. This is obviously a security concern as agents should only be able to view their own data/performence,

What is the best method/solution of applying this authentication model?

[Anonymous]

This very much depends on how you are getting your data for these reports (what type of data sources)

[rax99]

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.

[Anonymous]

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.