Using REST Connector with OAuth2 against Microsoft Dynamics 365 Business Central

Sonja_Bauernfeind · Sep 5, 2022 8:27:13 AM

Background

Microsoft is deprecating basic authentication in Microsoft Dynamics 365 Business Central (online), recommending users to instead authenticate with OAuth2. This is in effect from version 20 of Business Central, rolled out in April 2022. Basic auth has been extended for users previously having Business Central v19 who now are upgraded to v20. New customers starting on version 20 must open a support ticket with Microsoft to have basic auth enabled. Basic auth will be turned off for all customers in Business Central v21 (targeted for October 2022).

Environment:

Qlik REST Connector
Qlik Cloud
Qlik Sense Enterprise on Windows

Assumed Scenario and Prerequisite

This article describes how the existing REST Connector in Qlik Sense can be configured to use OAuth2. The suggested OAuth flow uses Service-to-Service authentication against Microsoft Dynamics 365 Business Central. It requires that a single-page application is registered in the Azure portal with granted consent in the Business Central environment and that the related Client ID and Client Secret are known.

Obtain the Access Token

The configuration requires a separate connection definition for the initial login to get an access token from the Azure portal. The URL to use for this is:

https://login.microsoftonline.com/<BC Tenant ID>/oauth2/v2.0/token

Use the POST Method in a REST Connection

Within a REST Connector definition, use the URL noted above together with the POST method.

The request body must include the parameters Client ID (client_id) and the Client Secret (client_secret), together with grant_type=client_credentials and scope set to the API default (scope=https://api.businesscentral.dynamics.com/.default). You must separate the parameters with the ampersand character (&).

Edited connection.png

Adjust the Script to Capture a Token Parameter

This connection generates a JSON table (named ‘root’), which includes a column with the access token. You can extract the value of the access token into a parameter in the load script with the LET keyword.

REST Business Central Sandbox Auth.png

Use TRACE in the script to output the accessToken value in the data load progress window. The token is necessary to copy as you will paste it in the subsequent step, for the initial load of data.

Data Load Progress.png

Get the Data with Separate REST Connection

The standard URL for the API endpoint is:

https://api.businesscentral.dynamics.com/v2.0/<environment name>/api/v2.0/<API resource>

See Microsoft documentation for alternatives and further details.

Use Query Header Parameters in REST Connection

Within a separate REST Connector definition, use the API URL together with the GET method, and other defaults or adjustments as required, to get access to the data from the desired Business Central API resource.

The connection definition must include two query header parameters, one for the authorization token and one to set accept-encoding to gzip, deflate.

Query headers.png

The Authorization parameter must initially have the value Bearer followed by a valid accessToken value, for example, copied from the data load progress window in the previous step.

Adjust the Script to Dynamically use an Obtained Token

This connection generates a table with the data from the API resource defined in the URL. In this example, the company's resource was used (https://api.businesscentral.dynamics.com/v2.0/Sandbox/api/v2.0/companies).

Adjust the load script by adding a WITH CONNECTION statement including the Authorization header parameter and the accessToken parameter from the preceding script, i.e.
WITH CONNECTION (HTTPHEADER “Authorization” “Bearer $(accessToken)”)

WITH CONNECTION.png

This ensures that the new valid access token generated in the preceding part of the script gets inserted each time the load script is run.

You can configure additional API resources in separate connection definitions or by dynamically define the related URLs and tables in the load script.

Sonja_Bauernfeind · ‎2023-11-30

Hello @StefanBackstrand

Yes, this is supported with REST connector in Qlik Cloud as well. It works the same both on-premise and in SaaS. Also, it is not only for Microsoft Business Central but can be adopted similarly for other sources.

All the best,
Sonja

Mark_Leifeld · ‎2024-11-14

I had some issues getting data from every Business Central endpoint after /companies/.
The standard permissions sets were not enough.
Adding "D365 BUS FULL ACCESS“ solved the problem.
So if you can get data from /companies but not from companies(id)/salesorder or something else, it could help you 🙂

menta · ‎2024-12-20

I have a problem with access_token.

I setup the post section and Qlik give me the access_token

When I put this token into get section I receive the 401 (unauthorized)...

I check the call with postman and the result is an access_token longer that one in Qlik

Access_Token in Qlik 1396 Char

Access_Token in Postman 1724 Char

Using for the rest the access_token from postman the call run properly, within the one from Qlik it doesn't works.

Can anyone help me?

Thank you

willtech93 · ‎2025-04-14

Hi All, hope you all doing well.

Recently, I've tried to create a REST connector(both in qlik on premise and cloud) with Entra ID for retrieving a token (Since this token expires every 60 minutes). But all my attemps were unsuccessful. I had double checked the steps described in the article, but at the moment of testing the connection, it prompts the following error:

To be more clear, I have replicated this on postman and the token is generated succesfully.

If you have any input for this, I'll appreciate it.

Regards.

Ken_T · ‎2025-06-18

does this all work on the relatively new REST (via Direct Access gateway) connection type, on SAAS and QCG?

Using REST Connector with OAuth2 against Microsoft Dynamics 365 Business Central