article Using REST Connector with OAuth2 against Microsoft Dynamics 365 Business Central in Official Support Articles

Using REST Connector with OAuth2 against Microsoft Dynamics 365 Business Central

Sonja_Bauernfeind — Mon, 05 Sep 2022 12:27:13 GMT

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:

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.

Re: Using REST Connector with OAuth against Microsoft Dynamics 365 Business Central

AlexOmetis — Wed, 11 May 2022 15:41:31 GMT

Also worth noting you can use the "Next URL" Pagination option with "root/@odata.nextLink" as the value to make sure you get all the data with the GET connection - mine cut off at 20,000 rows otherwise.

Re: Using REST Connector with OAuth against Microsoft Dynamics 365 Business Central

diego_rueda — Tue, 28 Jun 2022 15:59:09 GMT

Hi,

What kind of Authentication Schema must be used in both connections?.

Re: Using REST Connector with OAuth against Microsoft Dynamics 365 Business Central

Sonja_Bauernfeind — Thu, 07 Jul 2022 13:28:59 GMT

Hello @diego_rueda

The REST Connector is here used in two connection definitions, one to obtain the access token and one to get the data. These are what defines the authentication against MS Dynamics. Then there is also an option for “authentication scheme” within the connection definition UI, for which the default selection ‘Anonymous’ should be just kept as is.

Re: Using REST Connector with OAuth2 against Microsoft Dynamics 365 Business Central

Luisanabria — Mon, 16 Jan 2023 19:26:37 GMT

Hi @Sonja_Bauernfeind good day

Im trying to connect following your steps, but l have many dudes about it, hope you can guide me.

1. Is neccesary add Qlik as a new aplication in Azure Portal before to this process?

2. Request body:

grant_type=
&client_id=
&client_secret=
&scope=https://api.businesscentral.dynamics.com/.default

Where can l get that information? My provider should give me that?

Hope you can help me, l really would aprecciate!
thanks

Re: Using REST Connector with OAuth2 against Microsoft Dynamics 365 Business Central

wcilliers — Tue, 24 Jan 2023 03:50:08 GMT

Hi @Sonja_Bauernfeind ,

Thank you for your guidance on this. Are you able to share some more details around the second 'GET' part of the connection. I have followed your steps and was able to get my token. However, for the second connection I am getting the below error using the exact same steps as you;

Any guidance on the entire setup of the second connection would be appreciated.

Thanks,

Wynand

Re: Using REST Connector with OAuth2 against Microsoft Dynamics 365 Business Central

Sonja_Bauernfeind — Wed, 25 Jan 2023 08:06:40 GMT

Hello @wcilliers and @Luisanabria

Let me look into these queries for you.

All the best,
Sonja

Re: Using REST Connector with OAuth2 against Microsoft Dynamics 365 Business Central

Sonja_Bauernfeind — Wed, 25 Jan 2023 08:16:01 GMT

Hello @Luisanabria

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. If you do not have access to the above, then your provider will need to give them to you.

All the best,
Sonja

Re: Using REST Connector with OAuth2 against Microsoft Dynamics 365 Business Central

Sonja_Bauernfeind — Wed, 25 Jan 2023 08:45:15 GMT

Hello @wcilliers

Have you included the token as a header with his request as per the step Use Query Header Parameters in REST Connection?

Providing a screenshot of this step may help us narrow down the problem.

All the best,
Sonja

Re: Using REST Connector with OAuth2 against Microsoft Dynamics 365 Business Central

wcilliers — Sun, 29 Jan 2023 23:19:49 GMT

Hi @Sonja_Bauernfeind ,

Yes I have included the headers.

Thanks,

Wynand

Re: Using REST Connector with OAuth2 against Microsoft Dynamics 365 Business Central

Luisanabria — Mon, 30 Jan 2023 16:44:45 GMT

@Sonja_Bauernfeind

Hi all,

l tried many times and problem still unauthorized but my Business Central Provider give me more permissions l didnt have.

Thanks, your post helps me so much, the only dude l have, is, if l need for example "sales" and "clients" is neccesary to create 2 REST different connections? and future create a new REST for a new type data l need?

Re: Using REST Connector with OAuth2 against Microsoft Dynamics 365 Business Central

Luisanabria — Mon, 30 Jan 2023 16:46:53 GMT

@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

Re: Using REST Connector with OAuth2 against Microsoft Dynamics 365 Business Central

AlexOmetis — Mon, 30 Jan 2023 17:09:03 GMT

"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:

Re: Using REST Connector with OAuth2 against Microsoft Dynamics 365 Business Central

wcilliers — Mon, 30 Jan 2023 23:31:05 GMT

@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.

Re: Using REST Connector with OAuth2 against Microsoft Dynamics 365 Business Central

Sonja_Bauernfeind — Thu, 02 Feb 2023 08:45:35 GMT

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

Re: Using REST Connector with OAuth2 against Microsoft Dynamics 365 Business Central

wcilliers — Mon, 06 Feb 2023 22:51:33 GMT

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-apis-using-s2s-authentication#task-2-set-up-the-azure-ad-application-in-

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

Thanks again for the support.

Re: Using REST Connector with OAuth2 against Microsoft Dynamics 365 Business Central

Skage — Thu, 12 Oct 2023 16:22:34 GMT

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

Re: Using REST Connector with OAuth2 against Microsoft Dynamics 365 Business Central

Skage — Fri, 13 Oct 2023 07:04:09 GMT

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/1496533

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

Re: Using REST Connector with OAuth2 against Microsoft Dynamics 365 Business Central

Sonja_Bauernfeind — Tue, 17 Oct 2023 07:02:48 GMT

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

Re: Using REST Connector with OAuth2 against Microsoft Dynamics 365 Business Central

StefanBackstrand — Mon, 20 Nov 2023 11:02:48 GMT

@Sonja_Bauernfeind Hi!

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