Forum Discussion
Power BI Rest API wont accept Application Token
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?
- Anonymous4 years ago
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 & TenantIdNo 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.
15 Replies
- SpartaBICommunity Champion
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.- AnonymousNot applicable
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.
- SpartaBICommunity Champion
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.
- SpartaBICommunity Champion
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.- AnonymousNot applicable
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!
- AnonymousNot applicable
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 & TenantIdNo 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. - SpartaBICommunity Champion
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.- AnonymousNot applicable
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
- Connect-PowerBIServiceAccount
- 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_tokenThis 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"