Forum Discussion

GeorgeBeveridge's avatar
GeorgeBeveridge
Regular Visitor
1 year ago
Solved

Custom Connector OAuth2 On-Premises Data Gateway: Broken Redirect

Hello,
I have a custom connector which uses OAuth2 with my Django web app.
It works correctly with Power BI desktop and in the Visual Studio Code Power Query SDK.

I have set up an On-Premises Data Gateway to use it in the Power BI service, but I cannot get it to work.

In the Power BI service, when I choose "Edit credentials", either when creating a new connection to my gateway, or manually adding the datasource of my semantic model to my gateway:

If I manually close the popup the connect button is greyed out.

I have tried changing my pbi_redirect_uri to https://gatewayadminportal.azure.com/OAuthRedirect, but then it never makes a request to my web app. The popup opens and closes, and then when I try to create the connection it makes a post request to my web app with no auth token.

Can anyone tell me where https://gatewayadminportal.azure.com/OAuthRedirect is coming from, and ideally how to make my connector work in the Power BI service?

Thank you


Additional info:
I do not have a Power BI premium license or premium workspace.
I am not using the (personal) gateway.

 

powerQRy.pq:

[Version = "1.0.0"]
section powerQRy;
//==== OAuth2 with PBI requires PKCE - https://learn.microsoft.com/en-us/power-query/handling-authentication ====
Base64UrlEncodeWithoutPadding = (hash as binary) as text =>
    let
        base64Encoded = Binary.ToText(hash, BinaryEncoding.Base64),
        base64UrlEncoded = Text.Replace(Text.Replace(base64Encoded, "+", "-"), "/", "_"),
        withoutPadding = Text.TrimEnd(base64UrlEncoded, "=")
    in 
        withoutPadding;

code_verifier = Text.NewGuid() & Text.NewGuid();
code_challenge = Base64UrlEncodeWithoutPadding(Crypto.CreateHash(CryptoAlgorithm.SHA256, Text.ToBinary(code_verifier, TextEncoding.Ascii)));
// ==== ====

oauth_client_id = "***";
base_url = "https://***/";
authorization_url = base_url & "oauth/authorize/";
token_url = base_url & "oauth/token/";
content_url = base_url & "api/power_bi/tables";
pbi_redirect_uri = "https://oauth.powerbi.com/views/oauthredirect.html";

[DataSource.Kind="powerQRy", Publish="powerQRy.Publish"]
shared powerQRy.Contents = () => 
    let
        headers = 
            [
                #"Authorization" = "Bearer " & Extension.CurrentCredential()[access_token],
                #"Content-Type" = "application/json"
            ],
        response = Json.Document(Web.Contents(content_url, [Headers = headers])),
        responseTable = Table.FromRecords(response, {"app", "name", "route_endpoint", "isLeaf"})
    in
        responseTable;

powerQRy = [
    TestConnection = (dataSourcePath) => { "powerQRy.Contents" },
    Authentication = [
        OAuth = [
            StartLogin = StartLogin,
            FinishLogin = FinishLogin
        ]
    ],
    Label = "powerQRy"
];

StartLogin = (resourceUrl, state, display) =>
    let
        AuthorizeUrl = authorization_url & "?" &
            Uri.BuildQueryString([
                client_id = oauth_client_id,
                response_type = "code",
                redirect_uri = pbi_redirect_uri,
                state = state,
                scope = "read",
                code_challenge = code_challenge,
                code_challenge_method = "S256"
            ])
    in
        [
            LoginUri = AuthorizeUrl,
            CallbackUri = pbi_redirect_uri,
            WindowHeight = 780,
            WindowWidth = 1024,
            Context = [
                code_verifier = code_verifier
            ]
        ];

FinishLogin = (context, callbackUri, state) =>
    let
        Parts = Uri.Parts(callbackUri),
        Code = Record.FieldOrDefault(Parts[Query], "code", ""),
        TokenResponse = Json.Document(Web.Contents(
            token_url,
            [
                Content = Text.ToBinary(Uri.BuildQueryString([
                    grant_type = "authorization_code",
                    code = Code,
                    redirect_uri = pbi_redirect_uri,
                    client_id = oauth_client_id,
                    code_verifier = context[code_verifier]
                ])),
                Headers = [#"Content-Type" = "application/x-www-form-urlencoded"]
            ]
        ))
    in
        TokenResponse;

powerQRy.Publish = [
    Beta = true,
    Category = "Other",
    ButtonText = { Extension.LoadString("ButtonTitle"), Extension.LoadString("ButtonHelp") },
    LearnMoreUrl = "https://powerbi.microsoft.com/",
    SourceImage = powerQRy.Icons,
    SourceTypeImage = powerQRy.Icons
];

powerQRy.Icons = [
    Icon16 = { 
        Extension.Contents("powerQRy16.png"), 
        Extension.Contents("powerQRy20.png"), 
        Extension.Contents("powerQRy24.png"), 
        Extension.Contents("powerQRy32.png") 
    },
    Icon32 = { 
        Extension.Contents("powerQRy32.png"), 
        Extension.Contents("powerQRy40.png"), 
        Extension.Contents("powerQRy48.png"), 
        Extension.Contents("powerQRy64.png") 
    }
];

 

6 Replies

  • v-sdhruv's avatar
    v-sdhruv
    Icon for Community Support rankCommunity Support

    Hi GeorgeBeveridge ,

    When you're using a custom connector that implements OAuth2, Power BI Desktop handles the auth flow directly and stores the token locally. But in the Power BI Service, things get more complex, especially when it comes to non-certified connectors and OAuth2.So make sure you are using a certified connector.

    You're seeing a redirect to:
    https://gatewayadminportal.azure.com/OAuthRedirect

    This is part of the OAuth2 flow used by the On-premises Data Gateway, not Power BI Service directly.
    The problem is, your connector likely does not explicitly support this kind of redirect, or your redirect_url in your OAuth2 provider (your Django app) is not properly configured to accept that redirect and exchange the code/token.

    You can follow these checks-

    1.Update your Django OAuth2 app to allow both redirect URLs:

    https://oauth.powerbi.com/views/oauthredirect.html

    https://gatewayadminportal.azure.com/OAuthRedirect

    Register these as redirect URLs in your Django app

    2.When Power BI redirects to:

    https://gatewayadminportal.azure.com/OAuthRedirect?code=...&state=...

    You must ensure that:

    This URL is registered as a valid redirect in your Django OAuth2 config.

    Your connector's RedirectUrL in the TestConnection and OAuth2 record allows it.

    If Power BI is unable to exchange the authorization code for a token at this step, the auth flow will break.

    If the popup closes immediately, it usually means Power BI never received a token.

    Hope this helps to fix your issue.

    If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
    Thank you.

     

  • Thank you v-sdhruv !

    You recommend I use a certified connector, is that a requirement? I am using my own custom connector which I will not be able to have certified as it does not meet Microsoft's requirements.

    I have registered https://gatewayadminportal.azure.com/OAuthRedirect as a redirect url in my Django application but still get the same result. Since the edit credentials pop-up does get an authorization code in it's response url, I believe my app's oauth2 implementation is working. But the pop-up gets stuck open and the authorization code is not ingested by the Gateway / Service.


    How do I alter my connector to support https://gatewayadminportal.azure.com/OAuthRedirect? When I simply swap `pbi_redirect_url = https://gatewayadminportal.azure.com/OAuthRedirect`, it still works in PBI desktop, but in the PBI service, the Service / Gateway does not make any request to my web app (I can see the logs), and the popup opens and closes quickly. Then when I try to "Create connection" it makes a token request to my app (as per the FinishLogin function) but passes no authorization token (I can see in my web app that the code is a blank string in its request).

     

  • v-sdhruv's avatar
    v-sdhruv
    Icon for Community Support rankCommunity Support

    Hi GeorgeBeveridge ,

    Thanks for the update.
    So, when Power BI sends the user through the login process and tells your server to redirect, looks like it
    is either ignoring the redirect_url or it is failing the validation (because the URI isn’t in the allowlist),

    As a result, Power BI gets back a redirect with no code --> your FinishLogin sees an empty code and
    the connection fails.

    Can you specify if there is any error message you are getting when you are configuring your connector with the Gateway?
    Additionally, you can visit these links that might help troubleshoot the issue.

    Troubleshoot on-premise gateways - Power BI

    The custom connector I've been developing works fine in Power BI Desktop. But when I try to run it in Power BI service, I can't set credentials or configure the data source. What's wrong?
    Also,you can test your connection following these steps which might help with the issue.
    Test Connection

    Hope this helps!

  • v-sdhruv's avatar
    v-sdhruv
    Icon for Community Support rankCommunity Support

    Hi GeorgeBeveridge ,
    Just wanted to check if you had the opportunity to review the suggestions provided?
    If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
    Thank You

  • v-sdhruv's avatar
    v-sdhruv
    Icon for Community Support rankCommunity Support

    Hi @GeorgeBeveridge ,
    Just wanted to check if you had the opportunity to review the suggestions provided?
    If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
    Thank You

  • v-sdhruv's avatar
    v-sdhruv
    Icon for Community Support rankCommunity Support

    Hi @GeorgeBeveridge ,
    Just wanted to check if you had the opportunity to review the suggestions provided?
    If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
    Thank You