Power BI Rest API wont accept Application Token

[Anonymous]

I have multiple Admin API calls in my pbix that run just fine if I use my own or the ServiceAccount token generated using PowerShell.

But as soon as I try to substitute in the Access Token I get back from my Registered Application, it fails to connect to the API and doesn't provide any relavant error message. Lately it has been stuck on just asking me to Specify how to Connect and Edit Credentials. They have to be left Anonymous because selecting any others tells you the API call must be made with Anonymous.

The API has all of the Read permissions for Power BI Service added and Admin approved. I know that since it does produce this Token for me in Power BI Desktop is half the battle. Now I'm trying to figure out what is missing. Everyone's tutorials, blogs, YouTube videos review the App Registration, granting it Permissions, and using the Token in the API call within Power BI Desktop. Nobody seems to review if there is some other permissions necessary here.

Thoughts?

[Anonymous · Accepted solution]

Solution found thanks to @SpartaBI !

1. Go to App registration -> New registration

2. Give your app a name and use the default settings

3. Create a secret, and make sure you copy the right value

4. Copy the ClientId & TenantId

No need to do anything else with the app config.

Just add "MyNewApp" to the AAD security group, wait for a couple of minutes, then try your code.

Then for running a Non-Admin API call, the App needs added into the Workspace Permissions you want to read from.

You do not need to add any delegated permissions to the app.
In order to allow a service principal access no non-admin APIs, it must have the same dataset/workspace permissions as any other user. For example- you can add the service principal as a member of a workspace.

This is exactly the purpose of the other setting in the Admin Portal.
It defines the service principals whose permissions are being managed by the Power BI Service itself.

[SpartaBI]

Hi @Anonymous,

I'm not sure why adding the Tenant.ReadAll had any effect on the admin APIs- It shouldn't have.

You do not need to add any delegated permissions to the app.
In order to allow a service principal access no non-admin APIs, it must have the same dataset/workspace permissions as any other user. For example- you can add the service principal as a member of a workspace.

This is exactly the purpose of the other setting in the Admin Portal.
It defines the service principals whose permissions are being managed by the Power BI Service itself.

If you need to add the service principal programmatically to workspaces, you'll need to use
Add Group User API
or Add User As Admin API

The first API can be authorized by any user or service principal which has the right level of permissions on the workspace (I imagine you do not have an existing service principal to use here...). The second API is not a read-only admin API, so you'll have to use an admin user.

Showcase Report – Contoso By SpartaBI

     

[Anonymous]

Thanks again for your help @SpartaBI.

I guess the flaw was me using that API call for a dataset refresh schedule that is apparently meant for a My Workspace, but the strange thing is it still worked on a Shared Workspace when I used my PowerShell generated Token.

This is what I was trying to use

This one just worked with the App Token with a groupid and datasetid for a test.

I was already trying the workspace permissions thing, but was using the Security Group that had the App's SP account in it. That apparently does not work. Once I added the App itself directly into the workspace permissions, now the GroupId/DatasetId API call works for me.

I'll post another reply combining your solutions to mark that 1 response as the solution. Thank you again!

[SpartaBI]

Hi @Anonymous,

Your M code works fine- I've tried it.

Did you set the configurations in the Power BI Admin Portal to allow service principals access to the Power BI Service & usage of the admin APIs?

I'm not sure that you need the first option, but you definitely need the second one.
You should add the service principal (it has the same name as your app) to an AAD security group, then add the latter to both settings.

[Anonymous]

Yes, we do have everything setup like this, all of the Enabled ones are set for Only our Power BI Admin Security Group.

Are you saying you can search for and add the Registered Application into that Security Group? Maybe thats the catch here? I had played around with removing the Only part on these settings so it was Entire Organization to see if that made a difference and it didn't.

**Edit, I do see under AD there is a section about Applications when viewing my Security Group. I am not an Azure Admin so I'm checking with one to try and add it here.

[SpartaBI]

Hi @Anonymous,

Yes. For example, in Azure I have an application

which is a member of a security group (the actual member is the service principal of the application)

In Power BI, the security group is set for both admin portal categories.

I can use "SP Power BI General" to run your script.

[Anonymous]

We tried this out. No luck. Created a Security Group, Service Principle account, added it into the Group, added the SP to Own the Application, and added the Group onto the Power BI Admin portal in those API settings.

You said the M-code worked for you, just to confirm, was it both the GET Access Token and the API Call for Workspaces and Expanded info?

Besides doing this, I'm really stuck now. What else makes sense to anyone?

[SpartaBI]

Hi @Anonymous,

Both the GET Access Token and the API Call for Workspaces work for me.

I'm not sure what you mean by "adding the SP to own the application".
I suggest you'll create a new app. From your opening post, I gather you grant it permissions- which is redundant. To create an app do this:

1. Go to App registration -> New registration

2. Give your app a name and use the default settings

3. Create a secret, and make sure you copy the right value

4. Copy the ClientId & TenantId

No need to do anything else with the app config.

Just add "MyNewApp" to the AAD security group, wait for a couple of minutes, then try your code.

[Anonymous]

Hi @SpartaBI ,

Can you clear one thing up for me, how are you adding the App to an AAD security group?

[SpartaBI]

Hi @Anonymous,

You do not need to grant the Power BI Service API Permissions to the App.
Those permissions are used to allow user delegation (i.e., the app acts on behalf of a user account).
When you use "grant_type = client_credentials", you are using the permissions granted to the app's service principal.

Adding the app's service principal to the security group:

Go to your AAD

Find the security group

Choose "Add members"

Search for the service principal (same name as the app) and add it

Showcase Report – Contoso By SpartaBI

     

[Anonymous]

Hi @SpartaBI 

Thank you for your patience and help!

We have 95% Success! We created a New App (with no API Permissions) and added both into the Security Group we created just for SP's.

*I think my confusion was that a Service Principle exists for the App Registration, and I was thinking it had to somehow use a physically created Service Principle user we created.

Our 1st App Registration that had the API Permissions, but was otherwise identical to the 2nd new App Reg, still wouldn't work after adding into the Security Group.

But at least the 2nd App is working in the API.

I said 95% because it works on all of my admin/ API calls, but it won't work on a non-Admin call. I have one trying to get the refresh schedules across the list of WorkspaceIDs and that says Access Forbidden. This same function structure works for another one that gets all Users off the Apps because thats an admin/ call.

Thoughts??

I'm thinking we need to now add in the API Permissions that the non-Admin calls get treated as Delegated and will need those added? I'm also hoping by adding those permissions, it wouldn't break whats working and cause us to have to recreate again (just saying this because I know how querky MS can be sometimes).

*FYI, yes that previous post would be at least part of the solution to this posting, but I'm hoping we figure out how to get this non-Admin API working, then consolidate the answers into 1 post and mark that as the solution. This can hopefully help a lot of people.

(datasetId as text) =>
let
Source = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/",
[
RelativePath = "datasets/" & datasetId & "/refreshSchedule",
Headers=[Authorization="Bearer " & #"GET Access Token"()]
] )),
value = Source[value],
#"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"

[Anonymous]

Hi @SpartaBI *update

The strange thing here is that the Admin/ API calls worked, the datasets/ call would say Access Forbidden.

We tried adding API Permissions to the App Registration and when doing all of the Read permissions that did not require Azure Admin Consent, nothing changed. Once we added any of the Tenant.ReadAll (Delegated or Application) and had the Azure Admin grant permissions, then the Admin/ API calls stopped working, but the datasets/ still didn't work either.

I'm running out of ideas here. When reading the REST API documentation, it does says under the datasets/ that you just needed scope for Datasets.ReadAll or Datasets/ReadWriteAll so I'm not sure why it still refuses to work.

I was even trying this call just to make sure it was not relying on any other calls in my PQ steps. It works with my PowerShell retrieved token.

let
Source = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/",
[
RelativePath = "datasets/{datasetid}/refreshSchedule",
Headers=[Authorization="Bearer " & #"GET Access Token"()]
] ))
in
Source

[Anonymous]

My comment about making the SP an Owner was because your picture of the App Registration that showed your SP account was an Owner on the application, I thought that was something also necessary for this to work. We can move on from that now.

Are you saying that you don't need to grant the Power BI Service API Permissions on this App Registration though?

[SpartaBI]

Hi @Anonymous,

You manage to use a Power BI Admin API from Powershell, but fail to run the same call from PQ?
Can you share your code?

The Web.Contents doc lists a "ManualStatusHandling" parameter.
I believe you can use it to specify {401} in order to receive the actual error message.

[Anonymous]

Just to clarify something, the way I used to get my access token when doing this more manually, before trying to automate by using the Registered App, I went to the Microsoft Documents site for the Power BI APIs. You used to be able to click Try It and in that window you could copy out your Access Token. Microsoft for some reason removed that part of the website and you can't Try It anymore. I wasn't having success getting a good token either from Postman, so I went the PowerShell route just for the manual token so I can at least use the report yet while trouble shooting this Registered App token usage.

PowerShell was simple, just made sure to have the cdmlets, #1 logged in (didn't matter if ServiceAccount or personal Login), and then ran the #2 to Get Token

1.      Connect-PowerBIServiceAccount
2.      Get-PowerBIAccessToken -AsString

Parameter values that are not my sensitive data are:

grant_type = client_credentials

resource = https://analysis.windows.net/powerbi/api

This is the M-code getting the Token from the Registered App

() =>
let
body = "grant_type=" & grant_type & "&client_id=" & client_id & "&client_secret=" & client_secretvalue & "&resource=" & resource,
Data = Json.Document(Web.Contents("https://login.microsoftonline.com/" & tenant_id & "/oauth2/token/", [Headers=[#"Content-Type"="application/x-www-form-urlencoded"], Content=Text.ToBinary(body)])),
access_token = Data[access_token]
in
access_token

This is the M-code where I am running one of the API calls. *Notice where I have the CurrentToken parameter being used, which uses the PowerShell produced Token. Once I remove that and use the GET Access Token Function, it won't work. I have even tested invoking the Function and pasting the result into the CurrentToken and didn't make a difference

let
Source = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/",
[
RelativePath = "admin/groups?$top=5000&$expand=datasets,dataflows,reports,dashboards,users",
Headers=[Authorization="Bearer " & CurrentToken/*#"GET Access Token"()*/]
] )),
value = Source[value],
#"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"