How to use an OData access token with Azure Data Factory Web activity to query Dynamics 365 web API?

Question

Purpose: To consume D365 web API services from Azure Data Factory.

What I have done so far: Using Postman I was able to successfully generate a web request to consume Dynamics 365 API using an access token.

So next step was to generate the request in Azure Data Factory using two Web activities. The first being for the acquiring of the access token and the second to actually make the API call. So far I have been able to generate the access token through the first Web activity using the following URL:

https://login.microsoftonline.com/<tenant id>/oauth2/v2.0/token

I pass the token to a variable to be used by the second Web activity which will contain the access token plus the Web API URL:

https://*******.crm6.dynamics.com/api/data/v9.2/$metadata#EntityDefinitions('contact')/Attributes

The token is passed this way:

{
    "name": "Get D365 Entity Columns",
    "type": "WebActivity",
    "dependsOn": [
        {
            "activity": "Set accessToken variable",
            "dependencyConditions": [
                "Succeeded"
            ]
        }
    ],
    "policy": {
        "timeout": "7.00:00:00",
        "retry": 0,
        "retryIntervalInSeconds": 30,
        "secureOutput": false,
        "secureInput": false
    },
    "userProperties": [],
    "typeProperties": {
        "url": "https://*******.crm6.dynamics.com/api/data/v9.2/$metadata#EntityDefinitions('contact')/Attributes",
        "method": "GET",
        "headers": {
            "Authorization": {
                "value": "@concat('Bearer ', variables('accessToken'))",
                "type": "Expression"
            }
        },
        "body": ""
    }
}

The problem: I am getting a 401 Unauthorised error even though I'm passing the access token. Should I be including the App Registration details such as ClientId, ClientSecret in the body? That has already been used obtained through the token. Or maybe the issue is with the authentication. What type of authentication should I use?

Answer 1

We had been struggling with this exact same issue for the past 3 days; we got a good access token, but then when we used it, it didn't work.

But we came up with a good workaround, and we are wondering if there may be is a bug in POSTMAN. We based our solution off of https://axparadise.com/how-to-use-postman-to-access-d365fo-odata-endpoint/ and that lead us to getting a bearer token using the POSTMAN Test scripts. Ran that, and it it immediately worked.

I compared the decoded output of a POSTMAN "Get New Access Token" Token to the one from the script on the blog above. Obviously the time stamps and such will be different, but there were some other significant differences, specifically the "aud" or audience field (you can see all these field types at https://docs.microsoft.com/en-us/azure/active-directory/develop/id-tokens)

Comparison of the Two Tokens