Get authorization code in JSON format

[naelske_cronos]

Hello there,

I have a big question regarding the use of the OAuth protocol in Power Query.

To get an access_token from the Azure Active Directory or other applications whom uses the OAuth protocol, I use the following code which I will build a HTTP request and retrieve via POST the access_token, this via JSON. As you can see, I'm working with the OAuth 2.0 flow which requests the username and password.

Spoiler

let
    Source = (AccountId as text, ApplicationId as text, Password as text, Username as text, ClientSecret as text) => let
    aadRequest = [
        client_id = ApplicationId,
        grant_type = "password",
        password = Password,
        resource = "https://analysis.windows.net/powerbi/api",
        scope = "openid",
        username = Username,
        client_secret = ClientSecret
    ],
    aadContent = Text.ToBinary(Uri.BuildQueryString(aadRequest)),
    aadTokenRequest = Web.Contents("https://login.microsoftonline.com/" & AccountId & "/oauth2/token",
        [
            Headers = [#"content-type"="application/x-www-form-urlencoded"],
            Content = aadContent
        ]
    ),
    aadTokenJSON = Json.Document(aadTokenRequest),
    aadToken = aadTokenJSON[access_token]
in
    aadToken
in
    Source

As you can see in Postman, I also get the right answer in JSON:

Another way to get the access_token is via the authorization code as explained here OAuth 2.0 authorization flow. When using Postman to retrieve the authorization code, I get an HTML document in return where I can't find the authorization code. When I'm using this as URL like login.microsoftonline.com/TENANT_ID/oauth2/authorize?client_id=CLIENT_ID&response_type=code, it is returning the right response URL back like: REDIRECT_URI?code=AUTHORIZATION_CODE&session_state=SESSION_STATE

Like retrieving the Azure Active Directory access_token with username and password, I want to do the same by using the authorization code, but first I have to get this authorization code and use it to get the access_token. The problem is when I'm running the following code below in Power Query I get problems like: dataformat.error we found extra characters at the end of json input. I guess this has to do with the fact it is returning a non-properly HTML-page.

let
    Source = (TenantID as text, ApplicationID as text, CallbackURL as text) =>
let
    AuthorizationCodeHTTPContent = [
        client_id = ApplicationID,
        response_type = "code",
        redirect_uri = CallbackURL,
        resource = "https://analysis.windows.net/powerbi/api"
    ],
    BuildAuthorizationCodeHTTPContent = Text.ToBinary(Uri.BuildQueryString(AuthorizationCodeHTTPContent)),
    AuthorizationCodeRequest = Web.Contents("https://login.microsoftonline.com/" & TenantID & "/oauth2/authorize?",
        [
            Content = BuildAuthorizationCodeHTTPContent
        ]
    ),
    AuthorizationCodeRequestJSON = Json.Document(AuthorizationCodeRequest)
in
    AuthorizationCodeRequestJSON
in
    Source

Is there a way to retrieve the authorization code like passed in the response URL but here in Power Query? Is there a function in Power Query to retrieve the authorization code from the URL? or put the HTML content in proper JSON format?

Thanks in advance

Regards

[Jayendran]

Hi @naelske_cronos ,

Actually the Oauth2 Authorization Flow is mainly intended for Native Application/Web App which is more intended for Interactive Process.

The way it should work always like in the browser. Because the browser will automatically redirect to the URL which you provided in the URL of the request. While in the Postman it will just execute the API and it will NOT REDIRECT YOU like browser. That's why earlier I called it as a interactive process.

Like POSTMAN , PowerBI Desktop is also a Standalone application which will just execute the API and it has no ability to redirect to the given address.

You should not implement Authorization Code flow in any of the Standalone application like PowerBI Desktop/POSTman. You should use this only from the Native  App/Web APP.