Microsoft’s Power BI and Azure Data Explorer (ADX) play really nicely together. Power BI has a 1st party connector which allows us to use Kusto Query Language (KQL) to pull data directly into Power BI.
However, there’s a gotcha I ran into recently while working on a project that took me a while to figure out.
When configuring the Azure Data Explorer (Kusto) connector from Power BI Desktop’s “Get Data” dialog;
We then need to fill out the various settings for the connector;
Hitting the OK button here will then either prompt you to sign into your Azure Account for ADX, or show a list of results from ADX if you’re already signed in.
However, instead we get a dialog saying “Unable to connect” with a message of;
We encountered an error while trying to connect. Details: “We reached the end of the buffer.”
A pretty unhelpful message to be fair. This took me ages to figure out, however it seems that the issue is that the login I was given had a domain name which didn’t match the domain name of the Azure Subscription.
I actually discovered this when I found a workaround.
It turns out that if you add a Microsoft Federated Email address to the Azure Data Explorer database by running a query similar to this from the ADX query page in the portal;
.add database myadxdatabase users ('[email protected]') 'Pete Gallagher (hotmail)'
We can then sign in with this Microsoft Federated email address instead of the account we’d use to sign into the portal, and the connection is successful.
This works even if this Microsoft email address isn’t a registered user in the Subscription through AAD.
However, the real fix is to make sure you have an email address login for the portal that matches the Subscription domain. You can find the domain in a few places, such as in the Subscriptions page in the portal, and finding the Directory shown in the overview page;
You’ll then need to remember to add the new user to the database as well, with something like;
.add database myadxdatabase users ('[email protected]') 'Pete Gallagher (contoso)'
Hoping this helps somebody out there!