Can't Authenticate against login microsoft online to generate bearer token

[trt18-sistemas]

Hi there,

I'm following these videos tutorials (part 1: https://youtu.be/fjzmz0BNN_g and part 2: https://youtu.be/N8qYRSqRz84) and almost everything is running fine. I can create the app (directly at azure portal as these video seems to be a little bit old), connect to the REST API and list groups/workspaces. But when I try to generate the Bearer token authenticating with my Power BI user, I get the following error message:

"It was not possible to authenticate using the provided credentials. Try again".

(

I tried to replace the username and password by "App Secret Id" and "App Secret Value", but the error message "Query error. DataSource.Error: Web.Contents fail to get the content of "https://login.microsoftonline.com/commom/oauth2/token/" (400): Bad Request. Detaisl: ..." (image bellow) was launched.

Could anybody help me understand what is going wrong and how to solve this problem?

Below is my code:

function GerarToken

() =>
let
    username = USER,
    pwd = PASSWORD,
    body = "grant_type=Password&resource=https://analysis.windows.net/powerbi/api&client_id=APP_ID_CLIENT&username="&username&"&password="&pwd,
    Data=Json.Document(Web.Contents("https://login.microsoftonline.com/common/oauth2/token/", [Headers=[#"Content-Type"="application/x-www-form-urlencoded"], Content=Text.ToBinary(body)])),
    access_token = Data[access_token]
in
    access_token

Query to List Groups

let
    token = GerarToken(),
    Source = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/groups?$top=100", [Headers=[Authorization="Bearer " & token]])),
    value = Source[value],
    #"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "isReadOnly", "isOnDedicatedCapacity", "name"}, {"id", "isReadOnly", "isOnDedicatedCapacity", "name"})
in
    #"Expanded Column1"

Regards

[v-henryk-mstf]

Hi @trt18-sistemas ,

You can try to use following M query code if they help with you scenario:

let

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],

Result =

Web.Contents(

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

[

Headers = [

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

Authorization = "Bearer " + token,

RelativePath = "/xxxxx/xxxxx"

]

]

)

in

Result

Regards,

Xiaoxin Sheng

[trt18-sistemas]

Hi @v-henryk-mstf ,

Thanks for your time.

I've used your code as example and it generated a token sucessfully. Although, When I try to use the token to list my workspaces (groups), the API reply with a "Forbiden" error message.

Below is my code following your example (some variables were omitted for securety behalf):

Here is the output:

To make sure that it's not some issue on Power BI, I've tried the same on Postman and, as in Powe BI, I could generate a token, but when I try to query the list of workspaces  a "403 Forbidden" message is replied.

For some reason that I don't know why, the tokens generated by Azure AD doesn't seems to work with Power BI.

[trt18-sistemas]

One more info. I tried the same thing by using Postman online and it worked, but using Powe BI the autentication error message continues.

I would be greatefull for any help.

[trt18-sistemas · Accepted solution]

Replying here the solution found.

As posting above, the @v-henryk-mstf suggestion works to generate the bearer token. After using its example code, another error occurred. It was happening because, for some reason, the permission to use API, accessible at "Administration Portal >> Tenant Configuration >> Allow principal services to use Power BI  API", must be set as "All organization" (yellow highlighted), instead of "Specific groups" (red underlined).