Using REST Connector with OAuth2 against Microsoft Dynamics 365 Business Central

[Sonja_Bauernfeind]

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 (&).

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.

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.

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.

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)”)

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.

[Luisanabria]

@wcilliers 

Hi, l tryed with only the Authorization and it works, l didnt use accept-encoding

if l use the accept-encoding my connection failed, ldk why this happend

[AlexOmetis]

"if l need for example "sales" and "clients" is neccesary to create 2 REST different connections"

No, I would always recommend using one connection for each data source (possibly a couple if you need GET and POST) and then modifying the URL, headers, body etc using the "with connection" modifier, see:

• Create a REST connection | Qlik Connectors Help
• REST connector: Using WITH CONNECTION - Qlik Community - 1523257
• Solved: How to make URL dynamic in REST connection ? - Qlik Community - 1665170

[wcilliers]

@Luisanabria , Thanks, I have tried this as well but still no success.

I have also now tried a different BC tenant, but I get the same results.

[Sonja_Bauernfeind]

Hello @wcilliers 

After checking with one of our subject matter experts, we believe that this may require a more direct investigation, and I'd recommend logging a support ticket. You can find instructions on how to do so here.

All the best,
Sonja 

[wcilliers]

Hi @Sonja_Bauernfeind 

Thank you. I have done some more testing and decided to do everything from the beginning. And I finally got it to work!! Yay!

There were 2 steps I missed.

1. Registering the Access INSIDE Business Central
https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/administration/automation-a...

2. Adding a redirect URL in the Azure Portal for my registered application
https://businesscentral.dynamics.com/OAuthLanding.htm 

Thanks again for the support. 

[Skage]

Hi @Sonja_Bauernfeind and other experts!

I've been assigned to get this to work but I'm running into problems with Sense May 2023 Patch 3. I've done this many times with other APIs but I'm hitting a wall here.

It works in Postman when I make a get-request to the token-endpoint with form-fields filled out with the four values as x-www-form-urlencoded content. I can also get the same to work as a post-request as well.

It doesn't work if I don't use the form fields.

Since the form fields work it must be properly configured in the other end so it has to do with the generated traffic.

The problem is that I can't figure out how to replicate the working settings with the rest-connector.

I can't create a valid GET request, no body and other methods like query parameters does not work.

I can't create a valid POST request according to this instruction. I get a 401 of all things 😄

I can't use a generic GET or POST connection with different types of manipulation "With connection" either.

Any ideas on how to get this to work?

TIA!

/lars

[Skage]

Hi again! @Sonja_Bauernfeind 

I knew the issue had to do with body content and how it was defined in Content-Type. It all makes sense when I started thinking about what I actually did vs what I was supposed to do.

I remembered a while ago when I run into a similar problem and solved it with great help from help from this article made by the fabulous @mountaindude 

https://community.qlik.com/t5/Member-Articles/Howto-URL-encoding-in-QlikView-and-Qlik-Sense/ta-p/149...

I had to url-encode all values for the fields and set the Content-Type to application/x-www-form-urlencoded. There might be easier ways but this is clean enough.

I hope this can help someone else.

/lars

[Sonja_Bauernfeind]

Thank you for getting back to us with this, @Skage !

[StefanBackstrand]

@Sonja_Bauernfeind Hi!

Is this supported and tested with the Qlik Cloud (SaaS) REST connector too?

[Sonja_Bauernfeind]

Hello @StefanBackstrand 

Let me find out for you.

All the best,
Sonja