Have you struggled to understand Azure Active Directory (AAD)? Let’s look at 3 scenarios, focused around Power BI, to help understand Azure Active Directory versus your local Active Directory (AD).

You may not know the difference between AAD and local AD. Honestly, why does it matter? As long as your reports work and you can do your job, it isn’t a big deal.

These all come from real discussions I’ve had in the last week or two.

Why it is important to understand Azure Active Directory

Power BI only interacts with Azure Active Directory. It has no knowledge of your local Active Directory configuration. Analysis Services is the exact opposite of that. It understands your local AD and knows nothing about your Azure AD setup.

The more you understand about both Azure AD and local AD, the better you understand how the components interact with each other. Such as the gateway and Analysis Services concerning Power BI. You will also know what deployments will work, and which will not.

Let’s take a look at some scenarios to hopefully help to clarify the difference between Azure AD and local AD.

Scenario 1: Azure AD to Azure VM with different domains

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, john@guyinacube.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 john@guyinacube.com, instead send john@battlestar.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

Scenario 2: Kerberos and Azure AD

Kerberos is near and dear to my heart. I did numerous postings on it on the PSSSQL blog over the years. The question that was posed for this scenario was:

If I sign into Power BI, and my user is passed to the gateway, am I then using Kerberos to connect to the data source?

Maybe? At least not in the way that you are thinking.

It has nothing to do with Azure AD. Any account information from Azure AD would not be a Windows token. At best it would be a claims/SAML token, but we don’t even get that from a gateway perspective. A Windows token is required for use within a Windows environment to use Kerberos.

While Kerberos is more secure than NTLM, the other benefit to using it is to delegate a user’s credentials from one service to another. When we look at the gateway, we need to think of that as a client application. It will connect to the data source and that will be a single hop. It doesn’t require delegation. We are just going from point A to point B.

Now, we may end up using Kerberos. Let’s use SQL Server as the example data source. When we connect to SQL Server with Windows credentials, we use the Negotiate SSP. A simple way to look at this is that we check to see if a SQL servicePrinicpalName (SPN) exists or not. If it does, we go Kerberos. If it doesn’t we go NTLM. For a single hop, either would connect successfully.

There is a way this could actually work. I use Reporting Services integrated with SharePoint as the example. SharePoint uses claims authentication internally. If you have a report in Reporting Services that will connect with Windows authentication to a data source, we have to use the Claims to Windows Token Services (C2WTS) to convert the claims token to a windows token. You could technically do the same thing from a gateway perspective. Take that UPN and convert it into a Windows token. This is essentially what Reporting Services does. However, the minute you do that, you need delegation rights from the gateway to the data source. This complicates the configuration. Which is what resulted in all those blog posts I did on the topic. It is confusing and complicated.

SharePoint and Kerberos










Again this answer is maybe. However, it has nothing to do with Azure AD as we don’t get any token from Azure AD.

Scenario 3: Using ADFS with Gateways

The question for this one was:

I signed into Power BI with Active Directory Federated Services (ADFS) which uses my local AD. So, I have a Windows token right? I can do use the gateway with my user account?

No. From a gateway perspective, ADFS doesn’t change the situation.

When you configure ADFS with your Azure Active Directory tenant, it has to create accounts within Azure AD that match your local AD accounts. It uses DirSync as part of Azure AD Connect to accomplish this. Effective, you have two accounts. One in Azure AD and one in your local AD.

ADFS really helps with the Single Sign-On (SSO) scenario. And it will help you when signing into Power BI. But, when it comes to the gateway, Power BI will still just pass the string UPN to the gateway for Analysis Services live connections. This is the same as in Scenario 1. ADFS doesn’t buy you anything for that situation.

Remember, Power BI can only talk to Azure AD. The account has to be represented in Azure AD. It knows nothing of your local AD.