XMLA end point and row level security (RLS)

[Nishantjain]

Hi,

Is it possible to manage row level security in power BI service using XMLA end point with SSMS or something similar?

I would like to create our amend roles, add or remove users etc

Thanks

[GilbertQ]

Hi there

Currently the XMLA end points are read only.

It is planned to allow for writing to the XMLA end point, which will then mean that yes you can add or remove roles and users and or groups.

[Nishantjain]

Thanks that's great.

Does this mean that I can atleast read the current roles and users right now

[GilbertQ]

Yes that is 100% correct you can read anything from the XMLA end point

[Nishantjain]

Great. Can you point me to any boog/documentation how to do that?

[GilbertQ]

Hi @Nishantjain 

Have a look at this blog post

https://powerbi.microsoft.com/en-us/blog/power-bi-open-platform-connectivity-with-xmla-endpoints-pub...

[Nishantjain]

@GilbertQ 

Thanks for the link. I have gone through the blog and it explain how to connect using XMLA endpoints. Do you know how to extract the information once I have connected using SSMS?

Thanks

[GilbertQ]

Hi there

You can connect with Excel, I have not created a blog post on how to do it yet, but it is in my plans

[Nishantjain]

Thanks.

Managing RLS is very manual in the PBI service and is not helpful in our organisation which has over 30000 employees. If you can recommend anything that would be great.

Thanks

[GilbertQ]

Hi there

There is Dynamic RLS in which you can define a single role and the RLS is applied dynamically where you can do it via AD Security Groups

Here is my blog post on how to achieve this: https://www.fourmoo.com/2018/02/20/dynamic-row-level-security-is-easy-with-active-directory-security...

[Nishantjain]

@GilbertQ 

I have already implemented Dynamic RLS where possible, but in most cases it is not a feasible solution. Simply because it requires a data set to be refreshed. It is not possible to refresh the dataset everytime we add a new user to the dataset.

Nishant 

[GilbertQ]

Hi there

That is by design as far as I know.

I would also set the expectation with the business that new users will be added once a day, when you can refresh the dataset?

[Nishantjain]

I appreciate that this is by design.

I was hoping for more of an automated solution to manage users. 

I have looked into using SSMS 18.0 and AMO but neither seems to be allowing me to manage roles. I hope there will be something in the near future to automated RLS. Given that the team is focusing on bringing new features specially for large Enterprises, I am surprised that this hasn't been look at. 

Thanks for your help @GilbertQ 

[GilbertQ]

Hi there

If you do use AD Security groups you can have it all automated, but as always with any technology you will have to get the data updated in order to get new changes applied.