Power BI Analysis Services Connector and Azure Active Directory Domain Services

Note: This post represents functionality in Power BI and Azure Active Directory as of October 2015.

Leveraging the new Azure Active Directory Domain Services feature with the Power BI Analysis Services Connector works! While it doesn’t provide any functionality on top of what you could already do with a full Active Directory domain controller in an Azure VM, it does simplify the setup. Pricing depends on your directory size, but if you have a small number of users/group/servers, AAD Domain Services may be only $37/month compared to a pair of A1 Azure VMs for a high availability domain controller which is about $134/month.

Previously I’ve blogged about the Power BI Analysis Services Connector and how it uses Azure Active Directory (AAD) authentication and EffectiveUserName. I described a UPN suffix trick to setup a demo environment. One bit of information is out-of-date now.

I want to do a quick proof-of-concept, so I will spin up a Power BI tenant called spatulacity.onmicrosoft.com. That’s Azure Active Directory; that’s not an Active Directory domain you can join your virtual machine to directly.

The AAD team has been busy and has released a new feature called AAD Domain Services. When you turn this feature on, you can join an Azure VM directly to a domain-controller-as-a-service. You don’t have to build your own VM to serve as a domain controller. In this blog post I describe that it works with the Power BI Analysis Services Connector. I also discuss a reference architecture for using the Power BI Analysis Services Connector in an extranet scenario.

High Level Logical Architecture

You log into Power BI with Azure Active Directory authentication. Period. Inside Power BI, one type of data source is live connectivity to Analysis Services through the Analysis Services connector. That connector must be installed on a domain-joined server. If you’re doing this in Azure and don’t already have domain controller VMs built out, then AAD Domain Services is a perfect fit. You put your Analysis Services VM in the virtual network that is associated to AAD Domain Services and then domain join it.

To drill into a bit more detail on the setup of AAD Domain Services, in the old Azure portal configure the directory, create an “AAD DC Administrators” group, enable domain services, add the domain services IP addresses as DNS servers on your virtual network, then domain join your Azure VM. The full instructions can be found here.

 


AAD Domain Services and Power BI Extranets

AAD can have internal users and external users. An internal user would be joe@spatulacity.onmicrosoft.com. An external user would be a user from a different AAD. For example, if a supplier of Spatula City is PlasticParts.com, then an external user might be zane@plasticparts.onmicrosoft.com or zane@plasticparts.com (depending on their AAD setup). We could add Zane to the Spatula City AAD and grant them access to resources.

But how do you share Power BI reports with external users? (This is the top requested Power BI feature at the moment.) If you build a report and try to share with an external user in Power BI, it will give you an error saying you can’t share outside of your organization.

So sharing a dashboard itself with an external user isn’t supported in Power BI at the moment. One solution would be for Spatula City to create a public content pack that would appear in the Get Data… Services screen next to the likes of Google Analytics and Salesforce content packs. But at the moment content packs are periodic refresh and the data must compress down to 250MB or less. So content packs at the moment aren’t a good fit if you need live data or need to allow external reporting on terabytes of data.

The following reference architecture for external users to access Analysis Services in Power BI can be successful (with some caveats):


VM #1 has the Analysis Services models. It also has the Analysis Services connector that’s signed into the Spatula City Power BI tenant. It is domain joined to Spatula City AAD Domain Services.

VM #2 is just a small VM that has another Analysis Services connector but this one is signed into the PlasticParts.com Power BI tenant. The reason for the separate VM is that you can only have one Analysis Services Connector installed per VM. Spatula City IT will install the Analysis Services Connector and work together with Plastic Parts IT to get the connector signed into the Plastic Parts Power BI tenant. This VM is also domain joined to the same Spatula City AAD Domain Services.

When zane@plasticparts.com signs into Power BI (the Plastic Parts Power BI tenant) and starts a report connected live to Analysis Services, the Analysis Services connector on VM #2 opens a connection to Analysis Services on VM #1 and passes EffectiveUserName=zane@plasticparts.com on the connection string. And because zane@plasticparts.com is an external user that has been previously added to the Spatula City AAD, this all works.

Though I mention this in an AAD Domain Services blog post, you could make this reference architecture work if you use Active Directory VMs with the alternate UPN suffix trick for those external users.


Pros and Cons of External Users and AAD Domain Services

Pro #1: Both Spatula City and Plastic Parts can control Zane’s access. If Zane leaves the company, Plastic Parts can disable the user and he won’t have access to Power BI. Or if Spatula City stops working with Plastic Parts as a supplier, Spatula City can remove their users from its AAD to remove access.

Con #1: The main downside of the above scenario is that external users are a very limited fit with AAD Domain Services currently. For example, if there’s a joe@spatulacity.com and a joe@plasticparts.com then currently only one of the two users will work inside AAD Domain Services. Why? Internally, both of these users get converted to GREGDS.LOCAL\joe and only one of the two (undeterminstically) will work. (The same can actually happen with internal users if you have a couple of domains like joe@spatulacity.com and joe@spatulacitycanada.com.) Hopefully this is a limitation that will be fixed inside AAD Domain Services in the coming months.

Con #2: Prior to a B2B feature coming out in September 2015, external users were a pain to provision. You can’t add the first @plasticparts.com to @spatulacity.com AAD without adding a Microsoft account (e.g. LiveID like spatulajoe@outlook.com) as an admin of both AADs. And there’s currently no API or PowerShell way of adding external users. Luckily this recently changed with the new AAD B2B invitation feature. Upload a CSV of the external user email addresses, the users get an email invitation, and you’re done. No need to make a Microsoft account an admin of both AADs.

Con #3: This setup requires Spatula City to have a working session with Plastic Parts IT so that they can provide the login credentials to connect it with the PlasticParts.com Power BI tenant.

Con #4: If build out an Active Directory VM and an Analysis Services VM for demo purposes, you can stop both VMs after the demo is over so you only have to pay for VHD storage not any compute. AAD Domain Services can’t currently be paused. And disabling Domain Services will delete the domain such that you have to domain join the computer again from scratch once you enable Domain Services again.

Con #5: The user that creates the dashboard in the Spatula City Power BI tenant can’t just share that with an external PlasticParts.com user. However, he can email a Power BI Desktop file or an Excel workbook with Power View sheets to the PlasticParts.com user. If that emailed file is connected to Analysis Services then when that user uploads it to the PlasticParts.com tenant, Power BI will connect up that report to the Analysis Services connector in the PlasticParts.com tenant. Then the user can pin the appropriate tiles and it will all be connected live to Analysis Services.


Conclusions

In summary, AAD Domain Services works great with Power BI Analysis Services Connector. AAD Domain Services doesn’t really offer any additional functionality over full Active Directory domain controller VMs, but it does simplify the setup. It’s a great fit for a Power BI demo environment or for an Azure-only business who wants to use Analysis Services inside Power BI. While external users work, there are serious limitations around username clashes that will hopefully be resolved in the future. Given the pent up demand for sharing Power BI content with third parties I decided that publishing this admittedly imperfect reference architecture was worthwhile.