Sending external users as effective user ID

[pmdci]

Hello,

I have noticed that a while ago, Microsoft has added the ability to add external users (that is, non-Windows accounts) into roles in SSAS tabular models, as per the screenshot below:

Source: http://byobi.com/2017/02/managing-user-access-to-azure-analysis-services-databases/

We have an on-prem SQL Server 2016 (latest SP) with a tabular model that we access via the on-prem data gateway. As a test for row-based level security, we are trying to send an external user as the effective user ID, but that does not seem to work. Basiclly I added a user (e.g. joe@blogs.com) and sending that user as the effective user id returns an access denied error. If we pass an existing Windows account (i.e. an Active Directory user) then it works as expected.

I wonder if Power BI would ever work with external users specified in an SSAS tabular model?

Regards,

P.

[v-ljerr-msft]

Hi @pmdci,

Is the test user(external) a B2B collaboration user in your organization? According to this documentation, the external users can be added with this feature should be B2B collaboration users that have been added to your organization. 

In this sample, a B2B external user and a group are added to the Analyst role with Read permissions for the SalesBI database. Both the external user and group must be in same tenant Azure AD.

Reference:

https://docs.microsoft.com/en-us/azure/analysis-services/analysis-services-database-users

https://docs.microsoft.com/en-us/azure/active-directory/active-directory-b2b-what-is-azure-ad-b2b

In addition, have you tried the solution in this article to get Power BI RLS(SSAS Live) to work with external users? 

Regards

[pmdci]

Here is a tought. Perhaps a little far fetched...

I wonder if the Power BI gteway automatically forwards external users in the taxonomy of their correspondent shadow account in the AD -- even if not the AD in Azure (that is, an AD on prem). So for example if I log-in as john@example.com, it effectively forwards john_example.com#EXT#@mydomain.com. So I have created this account in my local AD and I have added it into a role in a test tabular model. I'll revert back on my tests later on.

If it does not, then we'll develop a solution around shadow accounts.

[pmdci · Accepted solution]

I got it working!!! But I must I had to create a mapping in PowerBI.com. Here is what I did.

1. Created a john_example.com#EXT#@mydomain.com account in my on-prem Active Directory. This would be the shadow account for user john@example.com
2. In PowerBI.com I went to the data source in settings, and clicked on map user names in the users settings. I then aded a replacement rule to replace @example.com with the shadow account string.

Boom! it worked 🙂

Bottom line is that SSAS -- at least on-prem, does NOT support external user IDs. I had to create a shadow account in my Active Directory to which the user gets mapped into.

[NirH_at_BITeam]

Hi,

Where is this mapping users reside. Can't seem to find it.

Can you pls. be more specific on where to define this mapping.

Thansk!

NH

[Anonymous]

Hi - does this only work for Analysis Services? Or can we map external users to SQL Server on-prem, too?

[pmdci]

Hi!

Thank you so much for providing us with this additional documentation.

No, the user is not B2B. But I am not sure if that would work either because having an external user in our Azure AD does not mean that that user would exist in our on-prem AD. Bear in mind that, as per my original post, this is an on-prem implementation of SSAS which authenticate users within our on-prem Active Directory.