Forum Discussion
Row Level Security in Power BI Report Server
I'm about out of my depth here, but I'll try to help.
When you set up the report on Report Server, when you go to Manage > Data Sources and look at the Credentials section, you MUST use 'Windows Authentication' as the Authentication Type, and you MUST use the 'As the user viewing the report' option.
Then in SQL Server, for whatever database you're using that same user ID must have permissions on the database. I think just db_datareader is all you need but I'm not certain about that.
The only trick we do is to use an active directory group. This is where I'm sketchy, but if I understand it correctly we actually give the AD group the SQL permissions, then just add the Windows user ID to that AD group. It isn't necessary to do it this way, it just makes our user management a little easier. We already have mechanisms in place to handle adding and removing users from the AD group as employment and roles change - by using the AD group we don't have to seperately manage SQL permissions per user ID.
I hope that helps. If not, if you can provide details I can try and provide more info.
Hmm. Thanks Davidm5, but we've already got that setup. We have a group saved into the DB Server and have datareader permissions set for the required DB.
The following link in a previous thread describes our setup: DirectQuery-Login-failed-for-user-NT-AUTHORITY-ANONYMOUS-LOGON.
I'll clarify the problem by saying that the DirectQuery and RLS stuff works - but only when viewing the report via Chrome. Using IE throws either a Kerberos constrained delegation error, or 'connection string not properly formed' error. So even though we're looking at the same report, different browsers behave differently.
Internet options have been set on IE and the site has been added as 'Intranet' level security.
:smileyfrustrated:
I'm sorry I don't have an answer for you, but I can tell you that it's working in IE for us so I can confirm it's possible. I don't know what magic the networking folks might have done behind the scene to pull that off though.
No problems davidm5, we managed to figure it out, thanks for your contribution.
For those of you still struggling with Kerberos authentication between an SQL Server (2016) and On-Prem report server, here is a few things to check when following the Kerberos configuration article posted on the MSDN.
- Ensure your user has access to the datasource with datareader permissions, either via a dedicated logon or as part of a group logon.
- Ensure that you set up constrained delegation for the (domain) user accounts that run SQL Server and Report Server
- Ensure that you have enabled constrained delegation on the server accounts themselves (i.e. the Computer's AD Entry must allow for Kerberos Authentication)
That last, bolded part is what had caused us headache. From our perspective, it seems that the MSDN article missed a few steps..
I'm trying to do something similar with the SQL server security table but ran into problems.
I have my table of users and can filter the SQL queries fine using the current user. However, when I run a scheduled refresh of the data in Report server it uses the specified credentials and so returns no data.
I tried creating a measure using CurrentUser = USERNAME() and then filtering the model by this but it won't let me use this measure in a table filter.
I want to refresh the data with the admin credentials and return all rows, then filter by the current user.
Any suggestions?
