Xero Azure Data Factory connector operates successfully for 30 minutes, then fails

[5eba5tian00]

Hi, The ADF Data Factory Xero connector runs successfully as a linked service for appox. 30 minutes, but then starts failing. I am using the OAuth 2.0. config, and suspect it is to do with the refreshing of the token.

I have followed the instructions at Xero for configuring OAuth (links below), can confirm offline_access is scoped, and am obtaining the refresh token initially via Postman. I can also confirm that I am following the guidance at MS Docs.

Of particular interest is the statement at the ADF Xero Connector page:

_" refreshToken | The OAuth 2.0 refresh token associated with the Xero application, used to refresh the access token when access token expires. Applicable for OAuth 2.0 authentication. Learn how to get the refresh token from this article.Refresh token will never expired. To get a refresh token, you must request the offlineaccess scope.Mark this field as a SecureString to store it securely in Data Factory, or reference a secret stored in Azure Key Vault. " The statement "Refresh token will never expired" appears to be incorrect, and at 30 minutes my token can no longer be utilised.

Example error is as follows: { "errorCode": "2200", "message": "ErrorCode=UserErrorFailedToConnectOdbcSource,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [HY000] [Microsoft][Xero] (61) API Connection Failed. Bad Request. HTTP Response code: 400\r\nERROR [HY000] [Microsoft][Xero] (61) API Connection Failed. Bad Request. HTTP Response code: 400,Source=Microsoft.DataTransfer.Runtime.GenericOdbcConnectors,''Type=System.Data.Odbc.OdbcException,Message=ERROR [HY000] [Microsoft][Xero] (61) API Connection Failed. Bad Request. HTTP Response code: 400\r\nERROR [HY000] [Microsoft][Xero] (61) API Connection Failed. Bad Request. HTTP Response code: 400,Source=,'", "failureType": "UserError", "target": "Copy Xero Projects", "details": []

Xero OAuth 2.0: https://developer.xero.com/documentation/oauth2/auth-flow#:~:text=Refreshing%20access%20tokens&text=Your%20app%20can%20refresh%20an,during%20the%20initial%20user%20authorization.&text=Each%20time%20you%20perform%20a,token%20returned%20in%20the%20response.

Data Factory Xero Connector: https://docs.microsoft.com/en-us/azure/data-factory/connector-xero

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 0f04a295-e4b9-a23b-02e7-232aab5db7ce
• Version Independent ID: cbcadc03-205b-c383-fd3d-7aa4d763f122
• Content: Copy data from Xero using Azure Data Factory - Azure Data Factory
• Content Source: articles/data-factory/connector-xero.md
• Service: data-factory
• GitHub Login: @linda33wj
• Microsoft Alias: jingwang

[SwathiDhanwada-MSFT]

@5eba5tian00 Thanks for your comment. We will review the issue and get back to you shortly.

[CHEEKATLAPRADEEP-MSFT-zz]

@5eba5tian00 Since this issue is not related directly to the doc, I would recommend you to create a thread on the forums - Microsoft Q&A or Stack Overflow Once you post your issue on forums, it will have visibility across the community which is a better suited audience for such types of issues.

[5eba5tian00]

@5eba5tian00 Since this issue is not related directly to the doc, I would recommend you to create a thread on the forums - Microsoft Q&A or Stack Overflow Once you post your issue on forums, it will have visibility across the community which is a better suited audience for such types of issues.

Thanks, will try there.

[KranthiPakala-MSFT]

Hi @linda33wj - This issue looks similar to SR# 120080423001351. Could you please review this issue and update the document as appropriate? - Thank you

@5eba5tian00 Sorry for your experience with Xero connector. We have assigned this issue to Content author to further review this feedback and update the document with relevant info.

We appreciate your patience and thanks for bringing this to our notice.

[linda33wj]

We added additional notes in the doc to clarify the current refreshToken limitation. And we are tracking internally on eliminating such friction in the future.

please-close

[charlespickettau]

Hi

Can I reopen this please? I don't understand why ADF collects the refresh token when it's not set up to exchange the refresh token when the Access Token expires.

I get the documentation has been updated to clarify that it doesn't work if you operationalise it - but this seems a fairly big limitation.

Regards Charlie

[5eba5tian00]

Hi

Can I reopen this please? I don't understand why ADF collects the refresh token when it's not set up to exchange the refresh token when the Access Token expires.

I get the documentation has been updated to clarify that it doesn't work if you operationalise it - but this seems a fairly big limitation.

Regards Charlie

Hi Charlie, following a pattern to obtain and store a new refresh token before proceeding to use the Xero Connector using the original token works around this issue, see image below for my approach using the Key store for both the original refresh token and then the newly obtained refresh token.

[charlespickettau]

Thanks

Hi Can I reopen this please? I don't understand why ADF collects the refresh token when it's not set up to exchange the refresh token when the Access Token expires. I get the documentation has been updated to clarify that it doesn't work if you operationalise it - but this seems a fairly big limitation. Regards Charlie

Hi Charlie, following a pattern to obtain and store a new refresh token before proceeding to use the Xero Connector using the original token works around this issue, see image below for my approach using the Key store for both the original refresh token and then the newly obtained refresh token.

Thanks Sebastian - that's really helpful, will look to use this option.

My wider point is this is a standard OAuth2 workflow so I'm a bit surprised that ADF doesn't have this plugged in behind the scenes. The other option we've used elsewhere is creating a custom function to do the load from the Xero API, but that makes the off the shelf connector a little redundant.

Cheers Charlie

[5eba5tian00]

Thanks

Hi Can I reopen this please? I don't understand why ADF collects the refresh token when it's not set up to exchange the refresh token when the Access Token expires. I get the documentation has been updated to clarify that it doesn't work if you operationalise it - but this seems a fairly big limitation. Regards Charlie

Hi Charlie, following a pattern to obtain and store a new refresh token before proceeding to use the Xero Connector using the original token works around this issue, see image below for my approach using the Key store for both the original refresh token and then the newly obtained refresh token.

Thanks Sebastian - that's really helpful, will look to use this option.

My wider point is this is a standard OAuth2 workflow so I'm a bit surprised that ADF doesn't have this plugged in behind the scenes. The other option we've used elsewhere is creating a custom function to do the load from the Xero API, but that makes the off the shelf connector a little redundant.

Cheers Charlie

Not a problem! Yeah I use a combination of the Xero Connector and a custom function from the Xero API, as I have found some data is better served from one approach vs the other.

[charlespickettau]

Thanks Sebastian. Maybe you should sell your connector to MS 😄

[venkatmexico]

i am looking for help here i am getting error while copy data from Xero to ADF

Error details Error code 2108 Troubleshooting guide Failure type User configuration issue Details {"error":"invalid_request"} Source Pipeline WEBACTIVITY

[venkatmexico]

Hi @5eba5tian00

Can you help in this task?

[SeanCapes]

Is this going to be fixed? Strikes me, as above, that this should be fixed itself in the connector. If the refresh token is supplied, that should be stored and used / refreshed as required.

[charlespickettau]

Agreed @SeanCapes - the connector is fairly useless without it (need to create custom functions) and it looks like the refresh token is used because it's asked for.

As an aside - we ended up looking at a tool called StitchData which has a much better approach to auth etc. (NB - I'm not affiliated). We also looked at AirByte but it was a bit more painful to host.

[SeanCapes]

@charlespickettau Did you manage to get the custom web pipeline, as shown by @5eba5tian00 above, working? If so, any chance you can provide a skeleton of the XML code? Thanks in advance

[charlespickettau]

We didn't - the whole point of using the ADF connector was to minimise custom code. We ended up just writing our own connector in Azure Functions.

Charles Pickett (he/him) PwC | Partner Mobile: +61 (0) 424 405 456 Email: @. au.linkedin.com/in/cerpickett http://www.linkedin.com/in/cerpickett EA: Erica Danielle Nievas | email: @.

On Tue, 21 Jun 2022 at 18:24, SeanCapes @.***> wrote:

@charlespickettau https://github.com/charlespickettau Did you manage to get the custom web pipeline, as shown by @5eba5tian00 https://github.com/5eba5tian00 above, working? If so, any chance you can provide a skeleton of the XML code? Thanks in advance

— Reply to this email directly, view it on GitHub https://github.com/MicrosoftDocs/azure-docs/issues/63464#issuecomment-1161424616, or unsubscribe https://github.com/notifications/unsubscribe-auth/AOILFZTANNUKGZWED6WI3XLVQF3Z7ANCNFSM4R5V2UBA . You are receiving this because you were mentioned.Message ID: @.***>

-- This email is sent by PwC. If you have received the email in error, please let us know immediately by contacting the sender. You must not continue to read the email or open or read any attachments once you become aware that the email has been sent to you in error and you must delete the email. Nor must you use, or disclose, or copy the email. We may seek your written undertaking that you have complied with these requirements. Confidentiality or legal privilege in any material in the email and any attachments, is not lost or waived by our mistaken disclosure to you. Our approach to privacy is set out in our Privacy Policy available at www.pwc.com.au/privacy http://www.pwc.com.au/privacy. Any opinions or other information in this email that do not relate to the official business of PwC are neither given nor endorsed by it. If this email contains a marketing message that you would prefer not to receive in the future please reply to the sender and copy your reply to @. @.> with "unsubscribe" in the subject line". Liability limited by a scheme approved under Professional Standards Legislation.

[SeanCapes]

Thanks for the response @charlespickettau , appreciated. Looks like I'm going to try the custom connection and the REST connector. As you've said, next steps are code which defeats the point of the implementation and making it customisable at run time.

[tlgemp]

Has anyone found a solution for this issue? Is there any guide to implement the solution suggested by https://github.com/charlespickettau ?

[sayanm7]

Hi @5eba5tian00 can you provide me your linkedin profile or email ID?

[sayanm7]

Thanks Sebastian. Maybe you should sell your connector to MS 😄

Have anybody created this pipeline?

[tlgemp]

Yes, I have created the pipeline . Are you stuck?

From: sayanm7 @.> Sent: Tuesday, May 7, 2024 10:55 AM To: MicrosoftDocs/azure-docs @.> Cc: Theo Lopes Garcia @.>; Comment @.> Subject: Re: [MicrosoftDocs/azure-docs] Xero Azure Data Factory connector operates successfully for 30 minutes, then fails (#63464)

Thanks Sebastian. Maybe you should sell your connector to MS 😄

Have anybody created this pipeline?

— Reply to this email directly, view it on GitHubhttps://github.com/MicrosoftDocs/azure-docs/issues/63464#issuecomment-2097908960, or unsubscribehttps://github.com/notifications/unsubscribe-auth/BFY7XVG7SZ6OUYUZTIO2ISDZBCQG3AVCNFSM4R5V2UBKU5DIOJSWCZC7NNSXTN2JONZXKZKDN5WW2ZLOOQ5TEMBZG44TAOBZGYYA. You are receiving this because you commented.Message ID: @.**@.>>

[sayanm7]

Yes @tlgemp. I am confused basically. Is it possible for you to send the Json Code of that pipeline.

[tlgemp]

{ "name": "XeroRefreshToken", "properties": { "activities": [ { "name": "GetRefreshTokenFromVault", "type": "WebActivity", "dependsOn": [], "policy": { "timeout": "0.12:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": true, "secureInput": true }, "userProperties": [], "typeProperties": { "method": "GET", "url": https://xeroapi.vault.azure.net/secrets/xerotoken?api-version=7.0, "authentication": { "type": "MSI", "resource": https://vault.azure.net } } }, { "name": "SetRefreshTokenVariable", "type": "SetVariable", "state": "Active", "onInactiveMarkAs": "Succeeded", "dependsOn": [ { "activity": "GetRefreshTokenFromVault", "dependencyConditions": [ "Succeeded" ] } ], "policy": { "secureOutput": true, "secureInput": true }, "userProperties": [], "typeProperties": { "variableName": "RefreshToken", "value": { "value": @.('GetRefreshTokenFromVault').output.value", "type": "Expression" } } }, { "name": "GetBearerToken", "type": "WebActivity", "dependsOn": [ { "activity": "SetRefreshTokenVariable", "dependencyConditions": [ "Succeeded" ] }, { "activity": "SetClientidVariable", "dependencyConditions": [ "Succeeded" ] }, { "activity": "SetAPISecretVariable", "dependencyConditions": [ "Succeeded" ] } ], "policy": { "timeout": "0.12:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": true, "secureInput": true }, "userProperties": [], "typeProperties": { "method": "POST", "headers": { "Authorization": { "value": "Basic @{base64(concat(variables('Clientid'), ':', variables('APISecret')))}", "type": "Expression" }, "Content-Type": "application/x-www-form-urlencoded" }, "url": https://identity.xero.com/connect/token, "body": { "value": @.('grant_type=refresh_token&refresh_token=',variables('RefreshToken'))", "type": "Expression" } } }, { "name": "GetClientidFromVault", "type": "WebActivity", "dependsOn": [], "policy": { "timeout": "0.12:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": true, "secureInput": true }, "userProperties": [], "typeProperties": { "method": "GET", "url": https://xero.vault.azure.net/secrets/xeroid/122222222222222222222?api-version=7.0, "authentication": { "type": "MSI", "resource": https://vault.azure.net } } }, { "name": "GetAPISecretFromVault", "type": "WebActivity", "dependsOn": [], "policy": { "timeout": "0.12:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": true, "secureInput": true }, "userProperties": [], "typeProperties": { "method": "GET", "url": https://xero.vault.azure.net/secrets/xerosecret/222222222222222222?api-version=7.0, "authentication": { "type": "MSI", "resource": https://vault.azure.net } } }, { "name": "SetAPISecretVariable", "type": "SetVariable", "state": "Active", "onInactiveMarkAs": "Succeeded", "dependsOn": [ { "activity": "GetAPISecretFromVault", "dependencyConditions": [ "Succeeded" ] } ], "policy": { "secureOutput": true, "secureInput": true }, "userProperties": [], "typeProperties": { "variableName": "APISecret", "value": { "value": @.('GetAPISecretFromVault').output.value", "type": "Expression" } } }, { "name": "SetClientidVariable", "type": "SetVariable", "state": "Active", "onInactiveMarkAs": "Succeeded", "dependsOn": [ { "activity": "GetClientidFromVault", "dependencyConditions": [ "Succeeded" ] } ], "policy": { "secureOutput": true, "secureInput": true }, "userProperties": [], "typeProperties": { "variableName": "Clientid", "value": { "value": @.('GetClientidFromVault').output.value", "type": "Expression" } } }, { "name": "Save New Xero Refresh Token", "type": "WebActivity", "dependsOn": [ { "activity": "GetBearerToken", "dependencyConditions": [ "Succeeded" ] } ], "policy": { "timeout": "0.12:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": true, "secureInput": true }, "userProperties": [], "typeProperties": { "method": "PUT", "url": https://xero.vault.azure.net/secrets/xerotoken?api-version=7.0, "body": { "value": @.('{\"value\":\"',activity('GetBearerToken').output.refresh_token,'\"}')", "type": "Expression" }, "authentication": { "type": "MSI", "resource": https://vault.azure.net } } }, { "name": "Save New Xero Access Token", "type": "WebActivity", "dependsOn": [ { "activity": "GetBearerToken", "dependencyConditions": [ "Succeeded" ] } ], "policy": { "timeout": "0.12:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": true, "secureInput": true }, "userProperties": [], "typeProperties": { "method": "PUT", "url": https://xero.vault.azure.net/secrets/xerotoken?api-version=7.0, "body": { "value": @.('{\"value\":\"',activity('GetBearerToken').output.access_token,'\"}')", "type": "Expression" }, "authentication": { "type": "MSI", "resource": https://vault.azure.net } } } ], "variables": { "RefreshToken": { "type": "String" }, "Clientid": { "type": "String" }, "APISecret": { "type": "String" } }, "annotations": [], "lastPublishTime": "2023-11-01T00:00:00Z" },

"type": "Microsoft.DataFactory/factories/pipelines"

}

Hope it helps, mate!

From: sayanm7 @.> Sent: Tuesday, May 7, 2024 11:44 AM To: MicrosoftDocs/azure-docs @.> Cc: Theo Lopes Garcia @.>; Mention @.> Subject: Re: [MicrosoftDocs/azure-docs] Xero Azure Data Factory connector operates successfully for 30 minutes, then fails (#63464)

Yes @tlgemphttps://github.com/tlgemp. I am confused basically. Is it possible for you to send the Json Code of that pipeline.

— Reply to this email directly, view it on GitHubhttps://github.com/MicrosoftDocs/azure-docs/issues/63464#issuecomment-2098051812, or unsubscribehttps://github.com/notifications/unsubscribe-auth/BFY7XVBVVSVJZLQT2ALMOEDZBCV6ZAVCNFSM4R5V2UBKU5DIOJSWCZC7NNSXTN2JONZXKZKDN5WW2ZLOOQ5TEMBZHAYDKMJYGEZA. You are receiving this because you were mentioned.Message ID: @.**@.>>

[tlgemp]

I am no expert, but that is what worked for me in the absence of a proper connector.

From: sayanm7 @.> Sent: Tuesday, May 7, 2024 11:44 AM To: MicrosoftDocs/azure-docs @.> Cc: Theo Lopes Garcia @.>; Mention @.> Subject: Re: [MicrosoftDocs/azure-docs] Xero Azure Data Factory connector operates successfully for 30 minutes, then fails (#63464)

Yes @tlgemphttps://github.com/tlgemp. I am confused basically. Is it possible for you to send the Json Code of that pipeline.

— Reply to this email directly, view it on GitHubhttps://github.com/MicrosoftDocs/azure-docs/issues/63464#issuecomment-2098051812, or unsubscribehttps://github.com/notifications/unsubscribe-auth/BFY7XVBVVSVJZLQT2ALMOEDZBCV6ZAVCNFSM4R5V2UBKU5DIOJSWCZC7NNSXTN2JONZXKZKDN5WW2ZLOOQ5TEMBZHAYDKMJYGEZA. You are receiving this because you were mentioned.Message ID: @.**@.>>

[sayanm7]

Thanks mate. @tlgemp I will reach out to you. if I need additional help.

[tlgemp]

No problem. Have fun😊

From: sayanm7 @.> Sent: Tuesday, May 7, 2024 12:34 PM To: MicrosoftDocs/azure-docs @.> Cc: Theo Lopes Garcia @.>; Mention @.> Subject: Re: [MicrosoftDocs/azure-docs] Xero Azure Data Factory connector operates successfully for 30 minutes, then fails (#63464)

Thanks mate. @tlgemphttps://github.com/tlgemp I will reach out to you. if I need additional help.

— Reply to this email directly, view it on GitHubhttps://github.com/MicrosoftDocs/azure-docs/issues/63464#issuecomment-2098192910, or unsubscribehttps://github.com/notifications/unsubscribe-auth/BFY7XVG3OTTP4GR4DQHZQ4TZBC3ZLAVCNFSM4R5V2UBKU5DIOJSWCZC7NNSXTN2JONZXKZKDN5WW2ZLOOQ5TEMBZHAYTSMRZGEYA. You are receiving this because you were mentioned.Message ID: @.**@.>>

[sayanm7]

Hi @tlgemp, is it possible to send a screenshot of your connector?