Connecting to Office365/Dynamics365

[sven-abx]

Hello Community,

maybe someone can help me out. I like to connect to our company CRM in Dynamics365. I found a commercial JDBC driver, but the problem here is, in the configuration user and password is clear code and therefor visible for everyone accessing the workflow.

is there any workaround or a better methode to connect to dynamics?

thanks and br,
sven

[tobias.koetter]

Hello sven,
did you try to use the Credentials Configuration node? When using credential variables the password isn’t visible in clear text anymore. Once created you can select the credential in the DB Connector node. For an example see here.
Bye
Tobias

[sven-abx]

hello @tobias.koetter,

thanks for your answer. can i use a normal jdbc connection for dynamics? because the paid version of driver i currently use doesn’t support credentials in your described way.

thanks and br,
sven

[tobias.koetter]

Hi,
maybe you can ask the JDBC driver vendor what options you have. I did a quick search but couldn’t find any free JDBC driver for Dynamics365. Maybe there is also another way to access the data e.g. via REST.
Bye
Tobias

[sven-abx]

Hi @tobias.koetter,

do you know how the POST-Request node needs to be configured to access Dynamics365 via WebApi?

Thanks and br,
Sven

[hops650]

Before using Knime, I used a JDBC connector from CData which let me connect to Dynamics 365 (this was a paid driver), the password was not stored in clear text. Just wanted to throw it out there an option if you didn’t want to implement your own REST workflows.

[sven-abx]

hi @hops650,

thanks for your reply. I know this driver, but additional software subscription was not a option for our customer.
i have a working workflow now build only with knime board nodes.

br,
sven

[Rayburn]

Hi Sven-abx,

I am curious to know how you managed to realize this connection. I am having the similar situation. Unfortunately, Google has not provided me with an answer yet. Can you help me out?

Brgds.

[sven-abx]

Hi @Rayburn,

yes, I was able to use it via Rest API which is provided by Dynamics365.
I will post some Screenshots, or is it urgent?

BR,
Sven

[Rayburn]

Hi Sven,

Thank you for getting back to me.
I was on vacation last week…
It is somewhat urgent, yes. It all starts with getting the data. That process is now “on hold”. After I get to the data, I can play around and work my “magic” in KNIME.

Screenshots and anything that can help me unlock the data in Dynamics 365 is greatly appreciated.

Thanks in advance.
Ray

[mlauber71]

@Rayburn if you add @sven-abx he might get a notification

[Rayburn]

@mlauber71 Top tip!

[Rayburn]

@sven-abx My bad… hope this reaches you.

Ray

[sven-abx]

hello @Rayburn,

sorry for the delay, but here we go:

maybe you don’t need all nodes, because i wanted to get all available tables in dyanmics365 and then retrieve the data.
it is a webapi request, so you probably can choose some other nodes like python.

the needed nodes:

config post request node:

json to table:

string manipulation:

get request:

the timeout should be relative high, because dynamics need some tome to response

here i create a flow variable, because my goal was to retrieve every available table in dynamics

json path:

ungroup:

string manipulation:

get request:

link to the workflow on the knime-hub: Dynamics365WebAPI-Connection – KNIME Hub

br,
sven

[Rayburn]

@sven-abx Nice! Thanks. Will let you know the outcome.

[Rayburn]

@sven-abx Hi Sven, could not even make it past the first screenshot: “your tenant here”… as in my name? Company name? Does not end there, I am new to using Post request. I probably have the necessary information, just not sure how to put what where…

[sven-abx]

hello @Rayburn,

it should look simliar to this: ```
https://login.microsoftonline.com/bd88124a-ddca-4a9e-bd25-f11bdefb3f18/oauth2/v2.0/token

found here: How to – Connect to Dynamics 365 Web API using OAuth 2.0 – Client Credentials – Nishant Rana's Weblog

br,
sven