Forum Discussion
Custom Connector failing on refreshing data with multiple calls to get a refresh token
We have a custom connector that is using the PKCE OAuth2 flow based around the provided example here and we are having users report occasional issues where they need to sign in again on Power BI Desktop to refresh their data. In looking at API logs on our end, it appears we have some cases where we see repeat calls to use the same refresh token to fetch an updated authentication token in a short period. We speculate there is parallel loading of various tables in Power Query and multiple queries are trying to use the same refresh token to refresh the authentication token at the same time. The first attempt to use the refresh token is successful with a 200 response but all the other calls using the same refresh token receive a 400 status with an error message which breaks the data refresh and the users have to login again for each additional query. This gets messy since often our users might be loading dozens of different tables through our connector.
This only appears to be an issue with Power BI Desktop. The refresh is working fine on the Power BI web service.
Here is a snippet of the authentication from our connector:
StartLogin = (resourceUrl, state, display) =>
let
clientId = getClientIdByRegion(resourceUrl),
// We'll generate our code verifier using Guids
codeVerifier = Text.NewGuid() & Text.NewGuid(),
AuthorizeUrl = authorize_uri & "?" & Uri.BuildQueryString([
client_id = clientId,
response_type = "code",
code_challenge_method = "plain",
scope="",
code_challenge = codeVerifier,
state = state,
redirect_uri = redirect_uri])
in
[
LoginUri = AuthorizeUrl,
CallbackUri = redirect_uri,
WindowHeight = 720,
WindowWidth = 1024,
// Need to roundtrip this value to FinishLogin
Context = codeVerifier
];
// The code verifier will be passed in through the context parameter.
FinishLogin = (c, dataSourcePath, context, callbackUri, state) =>
let
Parts = Uri.Parts(callbackUri)[Query]
in
TokenMethod(dataSourcePath, Parts[code], "authorization_code", context);
TokenMethod = (dataSourcePath, code, grant_type, optional verifier) =>
let
// region = Record.Field(dataSourcePath, "region"),
clientId = getClientIdByRegion(dataSourcePath),
codeVerifier = if (verifier <> null) then [code_verifier = verifier] else [],
codeParameter = if (grant_type = "authorization_code") then [ code = code ] else [ refresh_token = code ],
query = codeVerifier & codeParameter & [
client_id = clientId,
grant_type = grant_type,
redirect_uri = redirect_uri
],
ManualHandlingStatusCodes= {},
Response = Web.Contents(base_path & "/authentication" & "/token", [
Content = Text.ToBinary(Uri.BuildQueryString(query)),
Headers = [
#"Content-type" = "application/x-www-form-urlencoded",
#"Accept" = "application/json"
],
ManualStatusHandling = ManualHandlingStatusCodes
]),
Parts = Json.Document(Response)
in
// check for error in response
if (Parts[error]? <> null) then
error Error.Record(Parts[error], Parts[message]?)
else
Parts;
Refresh = (ca, resourceUrl, oldCredentials) => TokenMethod(resourceUrl, oldCredentials[refresh_token], "refresh_token");How can we ensure there is only one call to refresh the authentication token when there are multiple queries that depend on the authentication token?
9 Replies
- AlexZakFrequent Visitor
Hi,
not sure if this will solve your issue but here is my tokenMethode with works.
TokenMethod = (dataSourcePath, grantType, tokenField, tokenFieldValue, optional verifier) => let codeVerifier = if (verifier <> null) then [code_verifier = verifier] else [], queryString = codeVerifier & [ client_id = Settings(dataSourcePath)[client_id], grant_type = grantType, redirect_uri = redirect_uri, code_verifier = verifier ], queryWithCode = Record.AddField(queryString, tokenField, tokenFieldValue), tokenResponse = Web.Contents(baseAuthentificationURL(Settings(dataSourcePath)[tenant]) & "/token", [ 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; //Called on token experation Refresh = (dataSourcePath, refresh_token) => TokenMethod(dataSourcePath, "refresh_token", "refresh_token", refresh_token);- JoeFieldsFrequent Visitor
Does the authentication API you are calling allow the refresh token to be used more than once? Our authentication endpoint allows refresh tokens to be used only once. I have seen some other strategies where the authentication endpoint accepts additional calls using the same refresh token if they occur within a short period. Would be best if the connector code could lock the authentication to one thread.
- AlexZakFrequent Visitor
Microsoft identity platform and OAuth 2.0 authorization code flow - Microsoft Entra | Microsoft Docs
the refresktoken normally has a live time not a usage count. anyway the refresh call will return a new refresh token if you ask for "offline_access" scope.
The custom connector is returning this token to PBI as the body of the result.
I would recommend you to use fiddler to look for the communication there you will see more information on oauth flow. There you will see the token refresh call and can see if you service provides a new token. And if BPI is using it in the next refresh.
- JoeFieldsFrequent Visitor
Yes, here is a Fiddler snapshot of our authentication calls. First one is always 200 but the other calls receive a 400 because the refresh token can only be used once. There is one authentication call for each query from our connector even though the same access token can be used for all queries, Power BI seems to run each query in parallel including the authentication.
- AlexZakFrequent Visitor
can't tell you filtered the between 149 and 152 and
take a look into the response body of the 200 request it should look like this:
{
"token_type":"Bearer",
"scope":"....",
"expires_in":5332,
"ext_expires_in":5332,
"access_token":"<token>"}
--> There is no request limit only a expirantion time.
and in the 400 request it should give you an oauth error in the return header.
normally if the token is expired your request should look like this:
- balaguru_zohoRegular Visitor
did you find any solution for above issue?
we are getting same issue.
Oauth Data Refresh called multiple times on token expire scenario · microsoft/vscode-powerquery-sdk · Discussion #288 (github.com) - Suraj_NcircleFrequent Visitor
JoeFields
Hi, I am experiencing the exact same problem. Did you manage to find a solution?