SSAS Tabular model Row Level Security is not working in Power BI Desktop

[mittalpatel130]

Hello - 

We have set up a SSAS Tabular Model and created 2 roles as below. 

1. Admin - Administrator, Full Control

2. Employees - Read Only

There are 2 users added as members in Employees role and we applied Row Level Security using filter on one of the tables. We deployed it successfully on server and tested, it shows correct results in Browse queries for each role.

Then we opened Power BI Desktop on one of the employees machine, connected to Analysis Server database using live connection. Employee got access to model and all the tables, but all data too.  Row-level-security didn't apply at Power BI Desktop level when connected to Tabular model using Live Connection. 

Am I missing here anything? Is there anything we have to do for username vs emailid? Because we added usernames to AD groups for giving permissions in RLS and Power BI refers email ids. 

As part of this project we need to roll out the Model to the users and not the reports. Users from different department are going to build their own reports and hence it is important to get RLS working at Power BI Desktop level. 

Thank you,

[v-eachen-msft]

Hi @mittalpatel130 ,

You could refer to this document :

https://docs.microsoft.com/en-us/power-bi/desktop-tutorial-row-level-security-onprem-ssas-tabular

It details how to set up RLS in SSAS Tabular model.

[mittalpatel130]

Hello.. 

Yes we implemented Tabular model as suggested in this article. RLS works as expected when you publish and share reports with end users.

But it doesn't work when end user connects to Power BI Desktop and Tabular model on local machine. We don't see RLS applied for that user in Power BI Desktop. 

Hope this clears my question. 

Thanks!

[leed]

Hi @mittalpatel130 

You need to test it in Power BI Service. It will render the correct USERNAME.

You can create 2 measures, 1 = USERNAME() the other one = USERPRINCIPALNAME()

Then publish to the service, the result should be different.

[mittalpatel130]

Leed -  We already tested on Power BI Service and it works perfectly fine.  Do you mean Tabular model RLS doesn't reflect in Power BI Desktop when User connects to it?

We are planning to rollout Tabular model as a solution and users will create report themselves in Power BI Desktop for their own department. It is necessary that they see their own data and not all departments' data.

[leed]

Hi @mittalpatel130 

The service and desktop should show you the same result.

RLS does reflect in Power BI destkop, but if you open from desktop, the user instead being xxx@company.com they will be an AD user like US\xxx

Thats the reason you need to show up the 2 measures USERNAME() and USERPRINCIPALNAME() to see the difference between them then decide which one to use in SSAS TAB RLS.

Best,

[mittalpatel130]

Hi @leed 

I got it what you mean. 

Here is the screen print of my DAX filter and I don't think so I can add USERPRINCIPALNAME() here.

[leed]

Hi @mittalpatel130 

Can you show me what do you have in your membership ?

[mittalpatel130]

Hi @leed  

Sure.

[leed · Accepted solution]

@mittalpatel130 

It should work on both desktop and service this case.

Last test I can think about is, create the 2 measures I mentionned above, display it in desktop by these 2 users to see what they actually see.

check ou this blog to get better understanding about this test : https://www.kasperonbi.com/power-bi-desktop-dynamic-security-cheat-sheet/

[leed]

Hi @mittalpatel130 

Test with USERPRINCIPALNAME() instead of USERNAME() 🙂

Best,

[mittalpatel130]

Hello Leed - 

Thank you for your response.

Can you please brief me on how to test with USERPRINCIPAL()? 

Thanks!

[leed]

Hi @mittalpatel130 

The same way you did with USERNAME(), you just needed to replace the last one by USERPRINCIPALNAME().

On Power BI Service, unfortunately, if you pass by Live connection, you need to have a real user for the test, you cannot use RLS.

Best,

DA

[mittalpatel130]

Hello Leed,

I'm using simple Row level security with a filter condition on tables.. like Country = 'US' .. 

It is not a dynamic security. Still your comments apply to it? I'm little confused on where to add that USERPRINCIPALNAME()? On DAX filters or in members of the Role? 

Thank you very much for your response!

[leed]

@mittalpatel130 

2 screenshots :

1/ where to add membership.

1/ 

2/ where to add USERPRINCIPALNAME()

[manalla]

@leed 

I see your screen shots. Which version of SSAS is that? I am using 2016 SSAS Tabular and dont see UserPrincipalName(). 

Do higher version of SSAS has this DAX function?

Thanks

Manoj

[leed]

Hi @manalla 

This is a pretty basic DAX function, I think it's avaialable on all the version.

Whatever, below is the detail of my SSAS tabular server.

Best,

[manalla]

Hi @leed 

Thanks for you response.

Your version is 14.0.6.443 - This is 2017 SQL Build Version

My version is 13.0.5201.2 - This is 2016 SQL Build version.

But compatability is 1200 which is supported from 2016.

So i need to conclude that in 2016 SSAS Tabular, userprincipalname() DAX function doesnt work, like said in many other forums.

Thanks

Manoj

[leed]

Thank you for letting me know! Appreciate it!