Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Sep 5, 2022 8:27:13 AM
May 11, 2022 10:22:58 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 (&).
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.
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.
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.
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
