Suggest an Idea

[dgoehler]

<It would be nice if there would be also a connector for Microsoft ERP System Dynamics 365 Business Central in Qlik Sense Cloud. This is API: https://docs.microsoft.com/en-us/dynamics-nav/api-reference/v2.0/

For Dynamics 365 Business Central Cloud Azure Active Directory Authentication would be necessary. Basic Authentication is currently also possible but will be turned off in 2022 (https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/upgrade/deprecated-features-...). For Dynamics 365 Business Central OnPrem Basic and Azure Active Directory Authentication are supported. Most Customers have only setup Basic Authentication.

[to759_PM]

@P-O_Davidson 
Thanks for your feedback, but keep in mind "within 2022" might be to late, as the feature of Basic Authetication will be removed with Wave 1 2022 scheduled for April this year (meaning in a few days), then all new Business Central setups will not be able to be connected with Qlik and WebAPI (Odata V4 JSON).

This will effect one system of us now and additional, later the year, we have to upgrade three additional instances to Wave 1 2022, which are already linked via REST and Basic Authentication, so a running feature will not be available anymore. 

And we have already tested the available hints at:

Setting up selected REST sources for data loading ‒ Qlik Connectors

Handling Refresh Tokens with the Qlik REST Connect... - Qlik Community - 1492836

We were not able to get it working!

This is an argument to switch to Power BI, DataFactory and Synapse instead of using Qlik ... I have to point this out, these are realistic scenarios!!! We have to keep productive, and if this is the only way, then with Power BI.

At least this will effect up to 30 Business Central installations in the next two years migrated from NAV to Business Central!!

Maybe this is an argument to priorize the issue, and keep in mind this is relevant for client managed Qlik  installations also as for SaaS.

BR,

to

[AlexOmetis]

I see updates to v20.0 of Business Central are starting to be scheduled with the latest date allowed to roll out being 18 June. I assume this is the release that will remove the web services key authentication so have postponed ours until then.

@P-O_Davidson Is there an update on when Qlik will support BC and/or OAuth? 

[Carl_Hunter]

Hi, any update to this? v20 is now in production and causing problems with Qlik integration. 

[Giuseppe_Novello]

Hope you are doing well! So we can understand the situation and engage the proper channels and provide the most accurate response, could you please open a product support ticket, here are instructions on how to do so:

https://community.qlik.com/t5/Knowledge/How-to-create-a-case-and-contact-Qlik-Support/ta-p/1710992

Thanks!

Giuseppe Novel

[Carl_Hunter]

@Giuseppe_Novello one of our customers has done this, with not much in the way of a response. Ticket ref 00035745

We have replicated the error end to end and are in the process of documenting. We will raise a separate ticket with our findings

[Alexey-Dubovenko]

@to759_PM  if yo are talking about using REST connector and oAuth for Business Central,  it  works with following : 

1. you create a single page app on azure and add permissions to read/write automation from business central;

2. you grant consent for this app in a  business central;  

2. you create a REST connection (A)  to get the token to: https://login.microsoftonline.com/<YOUR_TENANT_ID>/oauth2/v2.0/token , this is a POST request, a body should look like: 

grant_type=client_credentials
&client_id=<your cli id >
&client_secret=<your cli secret >
&scope=https://api.businesscentral.dynamics.com/.default

3. using this token you add a new REST connection (GET) for required data entity, eg: 

https://api.businesscentral.dynamics.com/v2.0/sandbox/api/v2.0/companies (change /sandbox/ to /production/ if desired)

you put there 2 mandatory query headers 

Authorization  Bearer xxxxxxxx<your token value>

accept-encoding   gzip, deflate (not having this token will lead to request time out!!!)

4. this token will expire, so to produce a script which is able to be scheduled for reload, you will have to peek a value of a token from connection A and override it in connection (B) using WITH CONNECTION syntax, eg: 

LIB CONNECT TO 'BusinessCentral-auth';

RestConnectorMasterTable:
SQL SELECT
"token_type",
"expires_in",
"ext_expires_in",
"access_token"
FROM JSON (wrap on) "root";

[AuthData]:
LOAD [token_type],
[expires_in],
[ext_expires_in],
[access_token]
RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

Let accessToken = peek('access_token',0,'AuthData');
TRACE $(accessToken);

LIB CONNECT TO 'BusinessCentral-companies';

RestConnectorMasterTable:
SQL SELECT
"__KEY_root",
(SELECT
"id",
"systemVersion",
"name",
"displayName",
"businessProfileId",
"systemCreatedAt",
"systemCreatedBy",
"systemModifiedAt",
"systemModifiedBy",
"__FK_value"
FROM "value" FK "__FK_value")
FROM JSON (wrap on) "root" PK "__KEY_root"WITH CONNECTION (
HTTPHEADER "Authorization" "Bearer $(accessToken)"
);
[value]:
LOAD [id],
[systemVersion],
[name],
[displayName],
[businessProfileId],
[systemCreatedAt],
[systemCreatedBy],
[systemModifiedAt],
[systemModifiedBy],
[__FK_value] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_value]);

DROP TABLE RestConnectorMasterTable;

  PS: I was not able to make it working in POSTMAN and Fiddler (compose), have no idea why , but Qlik REST connector works as a charm. 

[dgoehler]

@Alexey-Dubovenko 

In Postman, you can set up the credentials in the folder:

"Get New Access Token" and "Use Token" to get a Bearer Token that is valid for 1 hour.

In the individual requests set it to Type = Inhert from parent in the Authorization Tab.

[AlexOmetis]

@Alexey-Dubovenko - thanks for the pointers, I've managed to put together an initial extract using that. I also found https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/administration/automation-ap... that had a little more detail on some of the steps. 

[P-O_Davidson]

Hi Everyone,

Please see this Knowledge Base article on how to use the REST Connector with OAuth against Microsoft Dynamics 365 Business Central.

We also have two related priorities on the roadmap, one for enhancing the REST Connector and one for adding a dedicated connector for MS Dynamics 365 Business Central. Both these are considered for future release, roughly within the next 6-12 months.

Thanks,
P-O

[acarrera]

Hi all,

Got a 401 error "requested resource requires authentication" when testing the connection in the Data Load Editor on Qlik Cloud. Set up the application on Azure and followed the steps outlined in this article - it's also found in this thread: 

https://community.qlik.com/t5/Knowledge/Using-REST-Connector-with-OAuth-against-Microsoft-Dynamics-3...

Anyone else encounter this error and have a resolution?

Thanks,

Alex