Goal

This document describes how to install and configure the Denodo OData 4 Custom Wrapper in order to connect to Microsoft Dynamics 365 using the OData 4 API and OAuth 2.0 authentication.

Microsoft Dynamics 365 Introduction

Microsoft Dynamics 365 is a product line of enterprise resource planning (ERP) and customer relationship management (CRM) applications commercialized by Microsoft. This article describes the process for connecting to the ERP module also called Microsoft Dynamics 365 for Finance and Operations.

Microsoft Dynamics 365 publishes an OData 4 API in order to be able to connect from different sources and perform operations in the server. The authentication method supported by the OData API is OAuth 2.0.

Importing the OData 4 Custom Wrapper

 

In order to connect to an OData 4 source, the Denodo OData 4 Custom Wrapper is needed. The Denodo OData 4 Custom Wrapper is a component available in the DenodoConnects section of the Denodo Support Site that provides CRUD access to your OData entities supporting OData protocol version 4.

Once the Denodo OData 4 Custom Wrapper has been downloaded, you can unzip the distribution in a known location in the Denodo server filesystem. You will find the dist folder with the jar files of the Custom Wrapper and doc folder with the documentation. For more information, refer to the Denodo OData4 Custom Wrapper - User Manual.

To import the custom wrapper, follow these steps:

1. From the design studio, go to: File - Extension management (in Denodo 6.0: Jar management)

2. Click on Import button and load the “denodo-odata-wrapper-

{vdpversion}-{version}-jar-with-dependencies.jar” file downloaded from

Denodo Support Site.

Follow the below steps to connect Microsoft Dynamics 365 from the Denodo platform:

Step 1: Register an app in Azure Active Directory

Step 2: Assign secrets for created Apps

Step 3: Get Authorization and Token Endpoints

Step 4: Obtain Access and Refresh Token using OAuth 2.0 Credentials Wizard

Step 5: Configure OData Wrapper Data Source

Step 6: Create Base Views

Step 1: Register an app in Azure Active Directory

First of all, It is necessary to register Denodo in Azure Active Directory. Make sure that the Azure Account and Dynamics 365 share the same TenentID (which means the same Microsoft account to login between Azure portal and Dynamics 365 office account).

In order to register the Application in Azure Active Directory, follow the below steps.

1. Navigate to https://portal.azure.com using the same login that used for the Dynamics 365 account.
2. Search for the App registration service using the search box and navigate to the App registration service by selecting the search output.
3. Click New Registration Icon will display you the new dialog to Register an application. This dialog required the following information to register an application in Azure AD.

1. Application Name: Denodo8
2. Who can use this application or access this API: Choose the best one based on the requirement
3. Redirect URI: https://localhost:9443/oauth/2.0/redirectURL.jsp

        

After entering the details the registration screen will looks like below

4. Click the Register button to successfully register the application.
5. Once Registered, an overview tab will display the registered information in detail as shown below.

6. Note down the Application(client) ID, this will be referenced later in Step 4 & 5.

Step 2: Assign secrets for created Apps

1. In addition to App registration, Client secrets need to be created. To create client secrets, navigate to the App Registration>Certificates & secrets tab.
2. Click New Client Secret and this will display a new dialog to add the client secret for the registered application.
3. Provide the valid Description & Expires and click Add button to assign new client secret as shown below.           

4. Note down the value of the Value attribute(client secret) ID, this will be referenced later in Step 4 & 5.

Step 3: Get Authorization and Token Endpoints

        

1. Authorization and Token Endpoints are mandatory to get the access and refresh tokens.
2. In order to get the endpoints, Navigate to the App registration>overview and click the endpoints icons. This will show the endpoints of OAuth2.0 as shown below.

3. Note down the OAuth 2.0 authorization endpoints(v2) and OAuth 2.0 token endpoints (v2),  this will be referenced later in Step 4 & 5.

Step 4: Obtain Access and Refresh Token using OAuth 2.0 Credentials Wizard

1. Open the OAuth 2.0 wizard from the Design Studio.

        

Note: Prior to version Denodo 8, use Virtual Dataport Administration Tool to launch OAuth 2.0 Wizard.

2. Fill the form with the following information:

1. Authentication grant: Authorization code grant (only from Denodo 7.0).
2. Client identifier: Client Id assigned to this application (Obtained from Step 1).
3. Client secret: Client Secret assigned to this application(Obtained from Step 2).
4. Authentication method: Include the client credentials in the body of the request.
5. Enter the Authorization endpoint (Obtained from step 3). It should look like this:

https://login.microsoftonline.com/<azure_directory_id>/oauth2/v2.0/authorize

6. Enter the Token endpoint (Obtained from step 3). This endpoint should look like this:

https://login.microsoftonline.com/<azure_directory_id>/oauth2/v2.0/authorize

7. Enter a redirect URI. The recommendation is using the default one: https://localhost:9443/oauth/2.0/redirectURL.jsp (the port, host and protocol might differ, depending on your VDP Server configuration). Ensure the redirect URI has been configured in the Dynamics 365 side as an accepted one, otherwise you will not be able to retrieve the tokens.
8. Set the ‘State’ request parameter: If you have any problem with Obtaining the authorization URL try unchecking this option.
9. Scopes: Add the necessary scopes. In our example, we need to add two values:

1. The instance URL with the permission user_impersonation, i.e.: https://<instance_name>.crm8.dynamics.com/user_impersonation
2. For retrieving the refresh token the scope offline_access

3. Click Generate the authorization URL.
4. Open the url in any browser and accept the requested permissions. A new authorization page will be displayed to grant permission to the registered App.

5. On Accept, Access will be given and response will be redirected to the URL.
6. Copy the generated url in the response and paste it in the field Paste the authorization response URL.
7. Click Obtain the OAuth 2.0 credentials.  Select the option Copy the credentials to clipboard to save the response for Step 5 Configuration.

Step 5: Configure OData Wrapper Data Source.

Once we have generated Access & refresh tokens for the wrapper, we can create our Custom data source to use the custom wrapper for connecting to our OData 4 data source, In this case: let's assume our Dynamics is running on the following URL:

https://<instance_name>.crm8.dynamics.com/

Note: The crm8 part of the URL can differ depending on the location of the data center.

To create the data source, follow these steps:

1. From the design studio, go to: File>New>Data source >Custom
2. In the Create a New Custom Data Source window, do the following:

1. Set a name for the new OData data source in the Name field.
2. Click on Select Jars and select the file imported in the previous section.
3. The Class name field must be filled with: com.denodo.connect.odata.wrapper.ODataWrapper
4. Click on the refresh button at Click to refresh the input parameters of the data source

3. Set the following parameters:

1. Service Endpoint: This value should be provided by the Dynamics 365 administrator. It can look like this: https://<instance_name>.crm8.dynamics.com/api/data/v9.0/

1. However, the endpoint could also look similar to this:

https://<instance_name>.crm8.dynamics.com/data/

If in doubt, contact the Dynamics 365 administrator.

2. Service Format: JSON
3. Check Use OAuth2 option.
4. Access Token: Enter the Access Token obtained with the OAuth 2.0 wizard (Obtained from Step 4).
5. Refresh Token: Enter the Refresh Token obtained with the OAuth 2.0 wizard (Obtained from Step 4).
6. Client Id: Client Id assigned to this application (Obtained from Step 1).
7. Client Secret: Client Secret assigned to this application (Obtained from Step 2)
8. Token Endpoint URL: URL to make OAuth refresh requests when the Access Token has expired (Obtained from Step 3)
9. Refr. Token Auth. Method: Select Send client credentials using the HTTP Basic authentication scheme
10. Click the Save button.

Step 6: Creating base views

Once we have configured our data source pointing to the MS Dynamics 365 server, we can create different views for accessing the desired entities.

As an example, we can create a base view for accessing the following Dynamics url that provides information about Accounts (accounts entity):

https://<instance_name>.crm8.dynamics.com/api/data/v9.0/

To create the base view follow these steps:

1. Double-click on the Dynamics OData source and then click on Create base view.
2. Set as the Entity collection field the entity you want to access: accounts

1. Optional: Check Enable Pagination to add to the output the fetch_size and offset_size parameters for paginating the results. This option is useful if the Dynamics 365 server limits the number of rows returned by a single query.

3. Click Ok.
4. Enter a name for the base view, eg: bv_accounts.
5. Click Save to create the base view.

References

Denodo OData4 Custom Wrapper - User Manual

Microsoft Developer Network: Connect to Microsoft Dynamics 365 web services using OAuth

Microsoft Azure Developer’s Guide: Integrating applications with Azure Active Directory