There has been some confusion regarding what is actually needed to use the Analysis Services Connector for Power BI. Originally there was a requirement that Azure Active Directory DirSync was required to get this to work. However, this was altered to indicate the following.
Why you might need use DirSync to connect to an on-premises Analysis Services Serverhttps://support.powerbi.com/knowledgebase/articles/505323-why-you-need-dirsync-to-connect-to-on-premises-ana
I wanted to walk through this a little bit and show how this actually works as it may be a little hard to wrap your head around. In this example, I will show you why you would not need DirSync to get this to work.
My tenant has several domains registered with it.
- guyinacube.com
- anothercube.guyinacube.com
- battlestarcloud.com
- guyinacube.onmicrosoft.com
Of these, guyinacube.com and anothercube.guyinacube.com are taking advantage of DirSync. Battlestarcloud.com is not. I want to focus on the battlestarcloud.com domain for this walkthrough. guyinacube.com VMs are local on my workstation. battlestacloud.com is on VM’s within Windows Azure. I do have a two way trust between the two forests though. However, the Battlestarcloud.com accounts are not being synced to my O365 Tenant.
If your domain isn’t listed, and you want to add it to your O365 Tenant you can look here on how to configure that.
You can see what accounts are synced with Active Directory within the Users list in the Office 365 Admin Portal.
You will notice that for johndoe@battlestarcloud.com it shows In Cloud. I actually created this account within O365 itself, but I do have that account created within the local Active Directory on my VM’s. Also, within Azure Active Directory, you can see where the users are from.
The domain, battlestarcloud.com, is still listed within Azure Active Directory though, as it needs to know that the domain is verified.
I just don’t have DirSync configured within that domain.
At this point we can configure the Analysis Services Connector on a machine within our battlestarcloud.com domain. For the Account used with the Connector, I used BATTLESTAR\pbiconnector.
I have a Tabular Instance on a machine called CAPTTHRACE and the instance name is TABULAR. Within there I have two Tabular Databases based on AdventureWorks.
After that, I can sign in to PowerBI.com with my johndoe@battlestarcloud.com account. Within Power BI, we can see that the AS Connector is configured on CAPTTHRACE.
When we click on CAPTTHRACE, we can see the list of Models available to use on that server. The fact that we see the list of models indicate that we were able to successfully connect to the Tabular instance with the AS Connector.
We can click on one of the models, and it will then be listed in our Dataset area within the main screen.
When we click on the dataset, it will take us to a blank report canvas. From here we can drag a few fields to create a chart and the data that is used for this is coming from the Tabular Instance on the CAPTTHRACE machine using a credential that we have not used DirSync for.
If we look at a SQL Profiler Trace, against that Tabular Instance, we can see what is actually happening.
The Connector will actually connect with the account you specified during configuration. In this case it is BATTLESTAR\pbiconnector. This account will need Admin rights on the server so that it make use of the EffectiveUserName property. We can see that EffectiveUserName was set with johndoe@battlestarcloud.com. We will use whatever is sent to use from Azure Active Director for the username. Whatever account that is sent by AAD will be used for the EffectiveUserName property. From Analysis Services perspective, this account needs to be a valid account within the local Active Directory. Which in this case it is.
If we try with another user, that doesn’t have access to the Tabular Instance, we would see the following:
I was trying to use this with johndoe@guyinacube.com which is not listed as an admin and doesn’t have rights to the database itself. So, we see nothing come back.
Why do we need DirSync?
We would need it if we had a tenant that only had a default domain. So, something like mytenant.onmicrosoft.com. If we had testuser@mytenant.onmicrosoft.com, that is what would be passed for EffectiveUserName. That obviously won’t work as that won’t be a valid user within your local environment. You could configure DirSync at that point. With DirSync enabled, it will copy your users from your local Active Director to Azure Active Direct and accounts would be created within the O365 Tenant. At that point you can sign in using that credential/email address.
Adam W. Saxton | Microsoft Business Intelligence Support – Escalation Services@GuyInACube | YouTube | Facebook.com\guyinacube