RLS for guest accounts that are personal email addresses not matching what is in AAD or guest users

[WEtapForPregame]

We have this working now, but only by having the guest tell us what shows up on a tool we created that displays the userprincipalname() when they go to the Power BI report.

We have guests that mostly use a gmail.com email addresses.

We have seen these prefixes live.com# and mail# to the user email address, when calling userprincipalname().

We were expecting to see this user with (User Principal Name) XXXXXXXXX_gmail.com#EXT#@PregameCom.onmicrosoft.com email address XXXXXXXXXX@gmail.com but instead we see mail#xxxxxxxxxx@gmail.com

What confused us the most was another gmail user with (User Principal Name) yyyyyyyyyyyy_gmail.com#EXT#@PregameCom.onmicrosoft.com email address YYYYYYYYYYYY@gmail.com but instead we see live.com#yyyyyyyyyyy@gmail.com

We orginally had the user principal name for the RLS it didn't work. We have since changed it to mail#xxxxxxxxxxx@gmail.com, and it's working now.

The RUB what should we use for the prefix so that it works the first time for external users that we invite as guest viewers of the data?  It's a horrible experence right now, as many of the guests are not techincal.

-Todd

[buhari91]

I was in the same boat and below solution worked for me: @WEtapForPregame 

[userprincipalname] = USERPRINCIPALNAME() || CONTAINSSTRING(USERPRINCIPALNAME(), [mail]) && NOT(ISBLANK([mail]))

where [userprincipalname] and [mail] is the column on RLS table. 

Please accept it as solution if it resolves your issue.
Thank you

[emoroz]

could you help me understand what this is doing? 

[v-zhangti]

Hi, @WEtapForPregame 

Please refer to the following links in the hopes of helping you.

Solved: RLS for External guest users - Microsoft Power BI Community

Row Level Security with SSAS Tabular Live Connection in Power BI - RADACAD

Solved: Dynamic RLS - Microsoft Power BI Community

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[WEtapForPregame]

@v-zhangti 

I believe we read someplace, that to make sure it's secure we must use the User Principal Name, an not any other attibute for RLS.  So the expected value returned the User Principal Name of a guest user that we've invited could be any text followed by a # then their email address?  If we remove everything from the beginning to the # and evaluate that to the email address we used to invite them, that is recommended?

It would be super helpful to the community I believe if someone from Microsoft could comment on the behavior of the User Principal Name for guest users.  Specifically when it comes to personal accounts, and these observed prefixes,  mail# & live.com# I don't know if there are more but that is what we've seen so far.

-Todd

[WEtapForPregame]

I hope someone from Microsoft can answer if this is the expected result, or is this a bug.

[GilbertQ]

Hi @WEtapForPregame 

I have also had this challenge in the past and what I did in PowerQuery was to have both options in my RLS table. This allowed it to work no matter which way it is returned.