Executing a DAX query from .NET using new REST API

[alexeisenhart]

I'm trying to query a dataset hosted in powerbi.com from a .NET application. I followed the advice of Kay Unkroth in his post https://powerbi.microsoft.com/en-au/blog/announcing-the-public-preview-of-power-bi-rest-api-support-....

If I delegate permissions as the logged in user, then my query works and I get the expected result of my DAX query. But if I change the login process to use a client ID and secret, then I get a 403 error.

I've tried with a dataset hosted in My Workspace and in the actual Team Workspace that I want to query.

This code results in a token that works, but it requires delegation (the popup window to supply your username and password with two-factor authentication and 60-minute expiring tokens), which I'd like to avoid. I'm logging in as myself: I'm an admin in Power BI and the Power Platform, and I explicitly added myself to the dataset as a Builder. The App Registration in Azure has Delecated permission for all Power BI Service APIs, and this call works.

private static async Task<string> GetToken_Delegate()
{
    IPublicClientApplication PublicClientApp = PublicClientApplicationBuilder.Create(clientId)
        .WithRedirectUri(replyUrl)
        .WithAuthority(AzureCloudInstance.AzurePublic, tenantId)
        .Build();
    Microsoft.Identity.Client.AuthenticationResult authResult = await PublicClientApp.AcquireTokenInteractive(scopes: new[] { resourceId + "/Dataset.Read.All" })
        .ExecuteAsync();

    return authResult.AccessToken;
}

This code authenticates using a clientID and a secret code. It authenticates and returns a token, but the same DAX query fails with a 403 error. The App Registration in Azure has Application level Tenant.Read.All and Tenant.ReadWrite.All for Power BI Service, granted by the admin, but still no good.

private static string GetToken_Secret()
{
    Dictionary<string, string> content = new Dictionary<string, string>();
    content.Add("scope", resourceID + "/Dataset.Read.All");
    content.Add("grant_type", "client_credentials");

    Microsoft.IdentityModel.Clients.ActiveDirectory.ClientCredential credential = new Microsoft.IdentityModel.Clients.ActiveDirectory.ClientCredential(clientId, secret);

    string authorityUri = $"https://login.microsoftonline.com/{tenantId}"; ///oauth2/authorize"; <-- commented this out because it complained about an "authorize" tenant. Identical code hasn't thrown this error for me before, so I'm not sure what's up with that. I think it's an issue with the latest version of Microsoft.Identity.Client.

    Microsoft.IdentityModel.Clients.ActiveDirectory.TokenCache tokenCache = new Microsoft.IdentityModel.Clients.ActiveDirectory.TokenCache();

    AuthenticationContext context = new AuthenticationContext(authorityUri);
    Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationResult result = Task.Run(() => context.AcquireTokenAsync("https://[My Environment Here].crm.dynamics.com", credential)).Result;

    return result.AccessToken;
}

Just for compleness, here's the code that I'm using to query the dataset. I pass in to this the token that is aquired from either of the two above code samples.

public void GetValues()
{
    //string authToken = GetToken_Delegate().Result;
    string authToken = GetToken_Secret();

    string jsonResponse;
    if (QueryDataset(datasetId, daxQuery, authToken, out jsonResponse))
    {
        Console.WriteLine(jsonResponse);
    }
    else
    {
        Console.WriteLine("The Web request did not succeed.");
    }
}

static bool QueryDataset(Guid datasetId, string daxQuery, string authToken, out string jsonResponse)
{

    HttpClient client = new HttpClient();
    client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", authToken);

    string url = $"https://api.powerbi.com/v1.0/myorg/datasets/{datasetId}/executeQueries";
              
    var requestBody = JsonConvert.SerializeObject(new QueryRequest(daxQuery));

    var response = client.PostAsync(url, new StringContent(requestBody, UnicodeEncoding.UTF8, "application/json")).Result;
    jsonResponse = response.Content.ReadAsStringAsync().Result;

    return response.IsSuccessStatusCode;
}

Any ideas? I'm open to other solutions. I want to execute a DAX statement w/o requiring the MFA popup dialog.

[Gentiana]

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...

Hope this helps!

[Anonymous]

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

[alexeisenhart]

Hello! Thank you for your thoughts! I'll reply to each point in turn.

• Only datasets in a new workspace experience workspace, that is to say a V2 workspace, are supported.
  • I'm testing with a new v2 workspace that I created this week specifically for testing this feature. My service principal is added to the workspace as an admin.
• 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.
  • I'm using a dataset that has many tables imported from a SQL Database. The data is are configured for Import mode (rather than Live or Direct Query).
• The tenant setting Allow XMLA endpoints and Analyze in Excel with on-premises datasets must be enabled.
  • This is enabled, for the entire organization.
• One query per API call.
  • Good here -- the unit test is making exactly one query and then checking the response. There should be no more than 3 rows returned.
• One table request per query.
  • Good here.
• Maximum of 100,000 table rows per query.
  • Good here.
• The dataset must reside in My workspace or another new workspace experience workspace.
  • As mentioned in a bullet above, I'm using a new v2 workspace right now for testing. I have tested with My Workspace and also the old group-based workspaces. In all examples, if I use the interactive MFA login, then all 3 workspaces returned data correctly.

Just to clarify the issue, if I login with the interactive MFA login process, then this feature works wonderfully. It only breaks when I try to use a service principal that is authenticating with a client id and secret key: I get an auth token, but the same DAX query fails with a 403. Unfortunately, because I'm using this code in a Unity application, I cannot use the interactive MFA login process.

[acteon_jon_catt]

Good Morning Alex
I found your post while Googling a similar 403 Forbidden issue accessing the PowerBI Admin API's via a Service Principal.
I'm hoping you resolved this issue in the end, and that you could share how it was fixed here for myself and others.

Thanks in advance,

Jon.

[alexeisenhart]

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.

[alexeisenhart]

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.

[alexeisenhart]

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
}

[alexeisenhart]

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.