Executing a DAX query from .NET using new REST API

I'm reading https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/execute-queries and I have some follow up questions that might help whittle down to the problem. Does anyone know the answer to the questions below?

"The user must have Manage dataset access permissions." In this case, I'm trying to log in as an application rather than a user... is that not supported? Is there a way to associate a user account with the App Registration in Azure AD?

"Required Scope: Dataset.ReadWrite.All or Dataset.Read.All" The application has
Tenant.Read.All and Tenant.ReadWrite.All, but there isn't an Application level permission for Dataset.Read.All, only a delegated permission. Might that be the problem?

Regarding the listed limitations, I think I'm good on all of these, because the delegated method works and returns a result, and if I log into the "Try it" button on that webpage, I can also post the same query in the REST API Try It panel.

Here are the details of the response to the Client/Secret attempt. This is the issue that I'm trying to fix.

StatusCode: 403, ReasonPhrase: 'Forbidden', Version: 1.1, Content: System.Net.Http.StreamContent, Headers:
{
  Strict-Transport-Security: max-age=31536000; includeSubDomains
  X-Frame-Options: deny
  X-Content-Type-Options: nosniff
  Access-Control-Expose-Headers: RequestId
  RequestId: 3365f512-3b49-42e7-89c1-1deb9a90bbef
  Date: Wed, 10 Nov 2021 14:04:49 GMT
  Content-Length: 0
}

I'm working with Microsoft support to get some insight into this issue. Here's a summary of where the problem is today.

This project is a library for a Unity3d application, and the MFA popups are not supported. When the MFA popup code is reached, when hosted by Unity, the application hangs and no login windows are presented. I’ve also tried to get an authentication token by embedding my personal username/password, but that is failing with MsalUiRequiredException: AADSTS50076.

I’ve also tried ClientID and Secret – this returns a token successfully, and this would be my preferred solution, but the DAX query fails with a 403 http error when using this token.

The absolute problem that I’m running into is that I’m trying to take advantage of the new REST API support for DAX Queries in PowerBI.com, which is in public preview right now.

To summarize:

1. Querying the REST API with a token acquired via MFA works fantastically, but I can’t use MFA in Unity3d.
2. Querying the REST API with a token acquired via client/secret fails with a 403.
3. Attempting to get a token with an embedded application username and password fails to return a token due to MsalUiRequiredException: AADSTS50076.

These results hold true for datasets hosted in:

1. The old team-based Workspaces
2. v2 Workspaces
3. My Workspace

The App Registration from AAD (the service principal) has admin access in my v2 Workspace, so it should have permission to execute Build commands. I believe that Build permission and v2 Workspaces are a requirement for this feature.

I'm open to alternative solutions to querying the dataset, but I believe XMLA is off the table because I don't have a premium Power BI account.

This page (https://docs.microsoft.com/en-us/power-bi/admin/read-only-apis-service-principal-authentication) details how to enable service principal authentication for readonly admin APIs. I don't think it the steps detailed here are 100% applicable because I am not aware of an readonly-admin API call for executeQueries, but this information might be relevant to debugging this issue.

This documentation specifies that "Make sure the app you use doesn't have any Power BI admin roles set on it in Azure portal." The app does not have any Power BI roles (from "Roles and Administrators | Preview."

It also says "Make sure there are no Power BI admin-consent-required permissions set on this application." The app has all Power BI delegated and application API permissions, and the application-level permissions are admin-consent-required.

I have enabled "Allow service principals to use admin Power BI APIs" in the Power BI admin portal, and "Allow service principals to use Power BI APIs" as mentioned in https://docs.microsoft.com/en-us/power-bi/developer/embedded/embed-service-principal.

Hi alexeisenhart ,

 It seems there is nothing wrong with settings. Please review the following limitations.

• Only datasets in a new workspace experience  workspace, that is to say a V2 workspace, are supported.
• Datasets that are hosted in Azure Analysis Services or that have a live connection to an on-premises Azure Analysis Services model aren't supported.
• The tenant setting Allow XMLA endpoints and Analyze in Excel with on-premises datasets must be enabled.
• One query per API call.
• One table request per query.
• Maximum of 100,000 table rows per query.
• The dataset must reside in My workspace or another new workspace experience workspace.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards

Community Support Team _ Polly

The API does allow service principals making requests. A service principal is an application which is given admin rights to a workspace.
If you are trying to always execute via the Service principal, then all you need is to get the access token for that service principal. The scope permissions for the app need to be Dataset.ReadWrite.All and they can be set in the Azure Portal. Marco Russo has a tutorial on how to manually set those permissions up: Creating a service principal account for Power BI API - SQLBI
Here's how an app I setup looks like in Azure Portal:

From there, you can create also a client secret from Certificates & secrets. I created a Client Secret, got its value and secret ID and used in C# code.

Here's some code I had to authenticate the service principal:

public class SPNConfig
{
	public string ClientId;
	public string ClientSecret;
	public string TenantId;

	public string GetAuthority()
	{
		return $"https://login.microsoftonline.com/{TenantId}";
	}
}       

 static void InitPowerBI(SPNConfig spnConfig)
{
	#region CreateAccessToken
	IConfidentialClientApplication app = ConfidentialClientApplicationBuilder
		.Create(spnConfig.ClientId)
	   .WithClientSecret(spnConfig.ClientSecret)
	   .WithAuthority(new Uri(spnConfig.GetAuthority()))
	   .Build();

	// Use .default to get all the permissions available (those configured in AAD for this app)
	string[] scopes = new string[] { $"{resource}/.default" };

	AuthenticationResult result = null;
	try
	{
		result = app.AcquireTokenForClient(scopes).ExecuteAsync().Result;
	}
	catch (MsalUiRequiredException ex)
	{
		// The application doesn't have sufficient permissions.
		// - Did you declare enough app permissions during app creation?
		// - Did the tenant admin grant permissions to the application?
		Console.WriteLine(ex.Message);
	}
	catch (MsalServiceException ex) when (ex.Message.Contains("AADSTS70011"))
	{
		// Invalid scope. The scope has to be in the form "https://resourceurl/.default"
		// Mitigation: Change the scope to be as expected.
		Console.WriteLine(ex.Message);
	}

	#endregion

	Console.WriteLine($"App-Only Access Token:\n{result.AccessToken}\n");
	_appToken = result.AccessToken;
	_client = new HttpClient();
	_client.DefaultRequestHeaders.Authorization =
		new AuthenticationHeaderValue("Bearer", _appToken);
}

If you are trying to have multiple users use this application, you may need to go the route of Service Principals profiles: Announcing general availability of service principal profiles in Power BI Embedded | Microsoft Power BI Blog | Microsoft Power BI

Hope this helps!