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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
PeterFallon
New Member

PowerBI oAuth Custom Connector is called multiple times to authenticate instead of once

Hi, this behaviour, started on client machines about 3 weeks ago. We have verified this on clean virtual machines, both Windows 11 and Windows 10 (both fully updated).

 

Our connector uses OAuth/OpenID/Identity Server 4 as per the sample templates to connect to REST services on our website. It has been working well for over a year now, and only started failing around 3-4 weeks ago.

 

The PowerBI reporting template we use makes several PowerQuery calls to the underlying REST queries. The following can be seen more obviously on Windows 10 (Windows 11 seems to hide things):

 

  1. Open PowerBI and open our connector directly (empty template) and log in. You can run any query presented. You can then open one of the complex reporting templates we built for clients (powerquery calls interrogate the underlying REST calls multiple times) and it WORKs - without asking for re-authentication

  2. Open PowerBI and one of our Reporting Templates. Hit Refresh. If you aren't currently authenticated, it opens a separate login window PER PowerQuery call (all on top of each other). To the end-user, it looks like the login works, but then the powerBI refresh dialog resets to say they aren't authenticated, and they have to repeat the process. If you plough through all of the login windows presented, THEN it works. Moreover - you are now authenticated - so a Refresh call later works as expected (until the original login expires 8 hours later).

The issue is that 2) above only started happening 3 weeks back (give or take). Prior to that, hitting refresh on a template would ask you to authenticate ONCE and once only, and all of the PowerQuery calls would share the same authentication.

 

Also, if you open the Options/data Source Settings/Sources in current file and clear the permissions from this template, you can trigger the whole issue in 2) above all over again.

 

We also have a virtual machine setup that our IT support can demonstrate in a Teams meeting if you want to see this happening.

 

Connector Core Code:

 

XeP3.Feed  = (url as text) =>
    let
        source = Web.Contents(url, [ Headers = DefaultRequestHeaders ]),
        json = Json.Document(source)
    in
        json;

// Production
BaseOAuth_uri = "https://auth.xep3.com/";
BaseUrl = "https://app.xep3.com/api/report";

DefaultRequestHeaders = [
    #"Accept" = "application/json;odata.metadata=minimal",  // column name and values only
    #"Content-Type"="text;charset=UTF-8",
    #"OData-MaxVersion" = "4.0"                             // we only support v4
];

XeP3Connector = [
    // Put a TestConnection expression here when I get it working - it will perform a test call to something simple
    Authentication = [
        OAuth = [
            StartLogin = StartLogin,
            FinishLogin = FinishLogin,
            Logout = Logout,
            Refresh = Refresh,
            Label = "XeP3 Login"
        ]
    ],
    // TestConnection is required to enable the connector through the Gateway
    TestConnection = (dataSourcePath) => { "Xep3.Contents" },
    Label = Extension.LoadString("DataSourceLabel")
];

// Constants for OAuth Connection
client_id = "xxx";
client_secret = "yyy";
redirect_uri = "https://oauth.powerbi.com/views/oauthredirect.html";

// These build on the common url set above for the envionment
token_uri = BaseOAuth_uri & "connect/token";
authorize_uri = BaseOAuth_uri & "connect/authorize";
logout_uri = BaseOAuth_uri &  "endsession?id_token_hint=";

windowWidth = 1400;
windowHeight = 1000;

//------------------------------------------------------------------------------------------------
// StartLogin builds a record containing the information needed for the client
// to initiate an OAuth flow. 
StartLogin = (resourceUrl, state, display) =>
    let
        authorizeUrl = authorize_uri & "?" & Uri.BuildQueryString([
            client_id = client_id,  
            redirect_uri = redirect_uri,
            state = state,
            scope = "api xep3 openid profile",
            response_type = "code",
            response_mode = "query",
            login = "login"
        ])
    in
        [
            LoginUri = authorizeUrl,
            CallbackUri = redirect_uri,
            WindowHeight = windowHeight,
            WindowWidth = windowWidth,
            Context = null
        ];

//------------------------------------------------------------------------------------------------
// FinishLogin is called when the OAuth flow reaches the specified redirect_uri.
// It retrieves the security token which is then implicitly passed in the headers for all subsequent calls
FinishLogin = (context, callbackUri, state) =>
    let
        // parse the full callbackUri, and extract the Query string
        parts = Uri.Parts(callbackUri)[Query],
        // if the query string contains an "error" field, raise an error
        // otherwise call TokenMethod to exchange our code for an access_token
        result = if (Record.HasFields(parts, {"error", "error_description"})) then 
                    error Error.Record(parts[error], parts[error_description], parts)
                 else
                    TokenMethod("authorization_code", "code", parts[code])
    in
        result;

//------------------------------------------------------------------------------------------------
// Called when the access_token has expired, and a refresh_token is available.
Refresh = (resourceUrl, refresh_token) => TokenMethod("refresh_token", "refresh_token", refresh_token);

Logout = (token) => logout_uri & token;

//------------------------------------------------------------------------------------------------
// This is the call to the OAuth url that gets the token for the current login.
TokenMethod = (grantType, tokenField, code) =>
    let
        queryString = [
            client_id = client_id,
            client_secret = client_secret,
            scope = "api xep3 openid profile",
            grant_type = grantType,
            response_type = "id_token token",
            redirect_uri = redirect_uri
        ],
        queryWithCode = Record.AddField(queryString, tokenField, code),

        tokenResponse = Web.Contents(token_uri, [
            Content = Text.ToBinary(Uri.BuildQueryString(queryWithCode)),
            Headers = [
                #"Content-type" = "application/x-www-form-urlencoded",
                #"Accept" = "application/json"
            ],
            ManualStatusHandling = {400} 
        ]),
        body = Json.Document(tokenResponse),
        result = if (Record.HasFields(body, {"error", "error_description"})) then 
                    error Error.Record(body[error], body[error_description], body)
                 else
                    body
    in
        result;

//-------------------------------
// Helper Functions
//-------------------------------
Value.IfNull = (a, b) => if a <> null then a else b;

ShowError = (Title, message) =>
    error Error.Record(Title, message)
1 ACCEPTED SOLUTION
PeterFallon
New Member

The full discussion is on:

 

https://github.com/microsoft/DataConnectors/issues/499

 

But workarounds have been found for this issue while MS works on the underlying problem. I've also verified that rolling back to the April 2022 Release works fine as well.

View solution in original post

2 REPLIES 2
PeterFallon
New Member

The full discussion is on:

 

https://github.com/microsoft/DataConnectors/issues/499

 

But workarounds have been found for this issue while MS works on the underlying problem. I've also verified that rolling back to the April 2022 Release works fine as well.

PeterFallon
New Member

For anyone interested, at the suggestion of Microsoft (on the separate Github thread), I took a known working PowerBI installation (October 2021), and rolled it forward until it broke.

 

The May 2022 release is the culprit.

 

While waiting to see if/when Microsoft can fix the issue - if you have a current install and experience this problem - roll back to the April 2022 release if you can.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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