Xero Data Factory Connector issue 400 error

While setting up the Xero connector I faced an issue when after 30 minutes connector stopped working and return an error 400:

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=,'",

After quick searching I discovered that this is a common issue with that connector, no matter that the access token was requested with the offline_access parameter, seems that the connector itself fails to update the token and continue to use the old one.

To help it out, I created additional short pipeline with key refresment steps and storing a new key into Azure KeyVault.

That’s a simple web request to grab a secret from the Vault, post it to renew and Save a new key back into the vault.

{
    "name": "GetOldKey",
    "type": "WebActivity",
    "dependsOn": [],
    "policy": {
        "timeout": "7.00:00:00",
        "retry": 0,
        "retryIntervalInSeconds": 30,
        "secureOutput": false,
        "secureInput": false
    },
    "userProperties": [],
    "typeProperties": {
        "url": "https://YourVault.vault.azure.net/secrets/YourSecret?api-version=7.2",
        "method": "GET",
        "linkedServices": [
            {
                "referenceName": "AzureKeyVault",
                "type": "LinkedServiceReference"
            }
        ],
        "authentication": {
            "type": "MSI",
            "resource": {
                "value": "https://vault.azure.net",
                "type": "Expression"
            }
        }
    }
}
{
    "name": "GetNewKey",
    "type": "WebActivity",
    "dependsOn": [
        {
            "activity": "GetOldKey",
            "dependencyConditions": [
                "Succeeded"
            ]
        }
    ],
    "policy": {
        "timeout": "7.00:00:00",
        "retry": 0,
        "retryIntervalInSeconds": 30,
        "secureOutput": false,
        "secureInput": false
    },
    "userProperties": [],
    "typeProperties": {
        "url": "https://identity.xero.com/connect/token",
        "method": "POST",
        "headers": {
            "Content-Type": "application/x-www-form-urlencoded"
        },
        "body": {
            "value": "refresh_token=@{activity('GetOldKey').output.value}&grant_type=refresh_token&client_id=YourClientID&client_secret=YourClientSeecret",
            "type": "Expression"
        },
        "linkedServices": [
            {
                "referenceName": "AzureKeyVault",
                "type": "LinkedServiceReference"
            }
        ]
    }
}
{
    "name": "SaveNewKey",
    "type": "WebActivity",
    "dependsOn": [
        {
            "activity": "GetNewKey",
            "dependencyConditions": [
                "Succeeded"
            ]
        }
    ],
    "policy": {
        "timeout": "7.00:00:00",
        "retry": 0,
        "retryIntervalInSeconds": 30,
        "secureOutput": false,
        "secureInput": false
    },
    "userProperties": [],
    "typeProperties": {
        "url": "https://YourVault.vault.azure.net/secrets/YourSeecret?api-version=7.2",
        "method": "PUT",
        "body": {
            "value": "{\n  \"value\": \"@{activity('GetNewKey').output.refresh_token}\"\n}",
            "type": "Expression"
        },
        "authentication": {
            "type": "MSI",
            "resource": "https://vault.azure.net"
        }
    }
}

And then simply triggered this pipleline to run every 15 minutes to refresh the token. Easy and simple.

Ofcource, wihtin the Xero connector you need to specify to use Azure KeyVault as the source of your Secrets.

Thanks to these ppl to fix this up:

docs.microsoft.com/en-us/answers/questions/112731/xero-azure-data-factory-connector-operates-success.html

www.alexvolok.com/2019/adfv2-rest-api-part1-oauth2

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.