Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Refreshable Token for Admin Rest API

Hi All,

 

I am a Power BI admin and i am trying to create a governance report for my Power BI tenent. I am using the Admin REST API but the token gets expired after about an hour. Can someone help me know how i can get a refreshable token which i can use in my report?

 

24 REPLIES 24
RH-BIP
Helper III
Helper III

Hi @Anonymous,

 

I have added your code to my query, but it is not working. The error is: 
--> PowerBI DataSource.Error: Web.Contents failed to get contents (400): Bad request

 

What could be the problem?

 

RHBIP_1-1667826564632.png

 

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

 

Anonymous
Not applicable

It wont work, you will have to create a app and then create a secret key, the app can be created using the App management in Azure portal and the secret too for the app. That Secret and app will have to be used in "client_id=XXXX" and "client_secret=XXXX" respectively. You will need help from your Office admin to obtain these two.

Hi @Anonymous 

 

I have created an app in Azure, so I did fill in the secret key and app id in MQuery, but replaced them with XXXX for security reasons. I also added the app registration to a Azure security group and added the security group to the workspaces. So why is this not working?

 

Find it very strange that there are so many people that are not able to use the PowerBI Rest API because of this. This should not be that hard right?

Anonymous
Not applicable

Go to your Power BI Admin portal and under "Allow service principals to use read-only admin APIs" select "Specific Security Group" and under that mention your APP name there and then try again.

Hi @Anonymous already did that too, and just to be sure did the same for: 'Allow service principals to use Power BI APIs'

Anonymous
Not applicable

Yes thats correct, basically that SPN or the app name you created needs to be in a Ad security group and that security group needs to go in that setting(only if you are enableling for security group). 
Anyways give me some time i will share with you the M code which i am using and also the full step by step guide. 

That would be great! thanks in advance @Anonymous !

 

 

Anonymous
Not applicable

Try this, create a function in PowerQuery using the code below, this will be used as a refreshable token.

() =>
let
body = "grant_type=client_credentials&resource=https://analysis.windows.net/powerbi/api&client_id=(mentioned your clientID/appid here)&client_secret=(mentioned your secret key)",
Data=Json.Document(Web.Contents("https://login.microsoftonline.com/2c93d3cb-f285-49c4-8265-fba1bd1fe468/oauth2/token/", [Headers=[#"Content-Type"="application/x-www-form-urlencoded"],Content=Text.ToBinary(body)])),
access_token = Data[access_token]
in
access_token

Hi @Anonymous 
The function it self works, but the refreshable token is about half in lenght shorter than the one I receive from https://learn.microsoft.com/en-us/rest/api/power-bi/admin/groups-get-groups-as-admin?tryIt=true&source=docs#code-try-0 and I still get the same error as before: "We couldn't authenticate with the credentials provided. Please try again"..

I have the same thing happening with the lenght shorter.  Did you ever find a solution for this?

Thanks.

Anonymous
Not applicable

Firstly, from the error it shows that there is no error in the syntex. Before posting the response here last week i had tested and refreshed it to make sure its still working and it worked fine. I am a little doubtful if you are either using the correct appid (which is the username) and secretkey (which is used as password) or the one you are using has not expired, because you choose an expiry when you create it. Can you clear the datasource credentials and start with a new workbook also i hope you have placed this appid in security group in the tenant settings . 

Hi DataUpperCut,

 

Thank you for your patience..really appreciated!


The appid used is the one in yellow..

RHBIP_0-1669044928916.png

and the secretkey i use see below (i renewed it today just to be sure) 

RHBIP_1-1669045034771.png

and this is the security group i have added to all workspaces:

RHBIP_2-1669045220270.png

Is this correct?

 



Anonymous
Not applicable

Did you add this secrutitygroup in the Power BI tenant setting under
 "Allow service principals to use read-only admin APIs" select "Specific Security Group"?

Yes I did that also, see image below.

 

RHBIP_0-1669061581370.png

 

Anonymous
Not applicable

All right, lets do this then. Watch the video below (part 1 and 2) and see if you missed anything while registring app and then giving the right access to the app (this part is usually done by the admin of O365)

https://www.youtube.com/results?search_query=power+bi+custom+admin+usage+

Anonymous
Not applicable

Can someone help?

v-henryk-mstf
Community Support
Community Support

Hi @Anonymous ,

 

I think you can try to add the 'get access token' steps into your query table then use variable to store the generated token for further operation instead of use static token strings. (these token string will be re-invoked/generated every time the query refreshed)

let

//get token

url = "https://login.microsoftonline.com/xxxxxxx/oauth2/token",

GetJson =

Web.Contents(

url,

[

Headers = [

Accept = "application/json",

ContentType = "application/x-www-form-urlencoded"

],

Content =

Text.ToBinary(

"grant_type=client_credentials&

client_id=xxxxxxx&

client_secret=xxxxxxx&

scope=xxxxxx"

)

]

),

token = Json.Document(GetJson)[access_token],

//other api usage wiht above token

Result =

Web.Contents(

"https://xxxxx.xxx.com",

[

Headers = [

#"Content-Type" = "application/json",

Authorization = "Bearer " + token,

RelativePath = "/xxxxx/xxxxx"

]

]

)

in

Result

 

Regards,

Xiaoxin Sheng

Hi,

 

I just been working on accessing APIs in my Power Desktop report without much success.  However the above script has been the first one which has been the only one which has been able return the token when I invoke the function - as below -

KarenL7_1-1679585643812.png

 

Here is the function which I amended as I called the function in a separate query

 

 

() =>

let

//get token

url = "https://login.microsoftonline.com/*********/oauth2/token",

GetJson =

Web.Contents(

url,

[

Headers = [

Accept = "application/json",

ContentType = "application/x-www-form-urlencoded"

],

Content =

Text.ToBinary(

"grant_type=client_credentials&

client_id=*************

client_secret=**************

)

]

),

access_token = Json.Document(GetJson)[access_token]

in

access_token

 

 

I have called the function in my query using

KarenL7_0-1679585593130.png

 

however the query still says "Access to the resource is forbidden" so it does not seem to be calling the function to accessing the token correctly.

 

Is there something that I am missing.

 

Thanks

 

Karen

Hi

 

I have now posted elsewhere for this

 

https://community.powerbi.com/t5/Desktop/Refreshable-Token-for-Power-Bi-Desktop/m-p/3152349#M1065034

 

But I can see that this is an issue with authorisation - so will update the other post.

 

Thanks

 

Karen

HI @Anonymous,

The bold part should be the personal token, id, and secret that the rest api requires which register and generated during the prerequisite steps and you can find them on the azure side:

Register an app to embed Power BI content in a Power BI embedded analytics application - Power BI | Microsoft Docs

The above are M query codes, you can use them on power query side. If you are not working with power query environment, you can take a look at the following link about using C# to get access tokens.

Solved: REST API Get Access Token - Microsoft Power BI Community

You can modify the function to store the 'get token' result into variables and check the expiry date before you use other APIs. (add if statement to check the expired date to choose to use an available token or invoke methods to generate new tokens)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.