In this scenario, it was presented that we have users in Domain A. We will call this guyinacube.com. The users in the local AD for guyinacube.com are sync’d to Azure AD for their guyinacube.com tenant. This is what users sign into Power BI with.
We then have an Azure VM which is part of Domain B. We will call that battlestar.com. This VM has Analysis Services, and we want to use live connections through the On-Premises Data Gateway.
One requirement for this deployment was that we cannot use a domain trust between guyinacube.com and battlestar.com. That makes this a little more complicated.
The question was why can’t we use the guyinacube.com users with the Analysis Services instance? They are in guyinacube.com and we are signing into the On-Premises Data Gateway with a guyinacube.com credential. Wouldn’t the gateway just pass that user to Analysis Services?
This really highlights the difference between Azure AD and local AD!
Power BI only talks to Azure AD. Power BI doesn’t know anything about the local AD. Analysis Services only talks to local AD and doesn’t know anything about Azure AD.
When we use a live connection to Analysis Services with Power BI, Power BI will send the userPrincipalName (UPN) string to the gateway. There is no token that gets passed. The upn string looks like an email address. For example, email@example.com. The gateway will take this string and use it for the EffectiveUserName property of the connection string to Analysis Services. Think of the gateway as a client application at this point.
Whatever string is passed to EffectiveUserName has to match a local AD account. Either in the same domain, or through a domain trust relationship. It doesn’t matter that the account exists in Azure AD. If the EffectiveUserName value cannot be translated to an actual local domain account, you will get an error.
There are a couple of workarounds that you can use to get around this.
Workaround 1: Put the Analysis Services instance in the same domain as the users
This is arguably the easiest workaround. Although it may not be technically feasible depending on your deployment. All of the accounts will exist in the same domain, and thus avoiding any account management overhead.
Workaround 2: Establish a trust
This is possibly the next best option to limit the amount of account management overhead. Establish a trust between Domain A and Domain B so they can talk together.
Workaround 3: Map user names to local accounts
Within the data source that you configure for the gateway, there is an option to Map user names. This allows you to alter what is sent for EffectiveUserName. For example, we could say that if the user’s upn is firstname.lastname@example.org, instead send email@example.com. This is just a simple string replace. You could say for @guyinacube.com, replace that with @battlestar.com. This would affect all accounts.
The downside to this option is you have to manage the entries within the data source in Power BI. Also, you have to have accounts that exist in the local AD for Domain B. If you have a lot of users, this is not ideal.
Workaround 4: Map user names to send CustomData and use row-level security
Thsi is similar to workaround 3, but you change the radio dial for the rule to use CustomData instead of EffectiveUserName. This will send the value of the item in the CustomData property of the connection string instead of EffectiveUserName. You could then use a lookup table for the values to control what is displayed. This doesn’t require any accounts to exist in the local AD. You still need to maintain the rules for the data source in Power BI.
Kay Unkroth has a great blog post that talks about usint this from a Reporting Services perspective. You can ignore the Reporting Services pieces of this and just focus on the implementation in Analysis Services knowing that the CustomData value will be sent by Power BI and the gateway will put that on the connection string.
Passing user names to Analysis Services with personalized connection strings in SQL Server 2016 Reporting Services