How to refresh ODATA API with OAuth2?

[Anonymous]

Hello friends,

I am connecting to OData sourse using OAuth2.

In Desktop it works fine, but in the service I cannot see OAuth2 option

What is the right way to do this?

Thanks

Michael

[v-yuezhe-msft]

@Anonymous,

The OAuth2 Flow is not supported currently when refreshing web API/Odata API in Power BI Service, there is an idea about this issue, please vote it up.

To work around this issue, you can customize your M scripts by using relative path, then use Anonymous authentication In Power BI service. There are some similar threads for your reference.

https://community.powerbi.com/t5/Integrations-with-Files-and/Dataset-Refresh-REST-API-amp-JSON-with-required-Param-in-PB/td-p/331814
https://community.powerbi.com/t5/Service/Refreshing-a-Restful-Data-Source-with-API-Key/td-p/131298

Regards,
Lydia

[grayfair]

Lydia,

I'm not sure I understand how the RelativePath is supposed to be used to retrieve a Token from the OAuth2 service?  From what I understand (which could be wrong), we have to request an Authentication Token from a URL and then the Power M Query has to use the token in order to complete the query.   

The Power M Query I have below gets the token and then retrieves the data.  However, this does not work using the On-Premise Gateway.  Can you provide any insight on how to get this to work? 

Power BI Desktop Query (works fine - uses authentication token)

let

    actualUrl = "xxx/oauth/token",

    record = [grant_type="password",username="xxx",password="xxx"],

    body = Text.ToBinary(Uri.BuildQueryString(record)),

    uaaBasicKey= "Basic xxx",

    options = [Headers =[#"Content-type"="application/x-www-form-urlencoded", #"Accept"="application/json", #"Authorization" = uaaBasicKey], Content=body],

    Source = Web.Contents(actualUrl, options),

    retVal = Json.Document(Source,65001),

    token = retVal[access_token],

    bearerToken = Text.Combine({"Bearer" , token}," "),

    sessionLoginUrl = "https://xxx",

    sessionOptions = [Headers =[#"Content-type"="application/json", #"Authorization" = bearerToken], Content=body],

    sessionResponse = Web.Contents(sessionLoginUrl, sessionOptions),

    feed = OData.Feed("https://xxx?", null, [Headers=[#"Tenant"= "xxx" , #"Authorization"=bearerToken]])

in

    feed

[Anonymous]

Thanks @v-yuezhe-msft Lydia,

Isn't there a way to do this using a Gateway?

[v-yuezhe-msft]

@Anonymous,

Currently, gateway doesn't support OAuth2 in this case, you would need to change M scripts in Power BI Desktop firstly , then use anonymous authentication in Power BI Service.

Regards,
Lydia