Goal

This document describes how to connect to Microsoft Dynamics 365 using the OData 4 API and OAuth 2.0 authentication.

Starting with Denodo 9.0 a new wizard specific to Microsoft Dynamics has been introduced to simplify the integrations with Microsoft Dynamics 365.

In previous versions of Denodo similar steps can be followed but the Denodo OData 4 Custom Wrapper will have to be installed and configured.

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 CRM module called Dynamics 365 Customer Engagement.

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

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

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: Denodo
2. Who can use this application or access this API: Choose the best one based on the requirement
3. Redirect URI:

• http://localhost:9090/oauth/2.0/redirectURL.jsp (if SSL is not enabled in your Denodo Platform)
• https://localhost:9443/oauth/2.0/redirectURL.jsp (if SSL is enabled in your Denodo Platform)

        

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 a 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 Web Design Studio.

        

2. Fill the form with the following information:

1. Authentication grant: Authorization code grant.
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 used by the authorization servers: Include the client credentials in the body of the request.
5. Enter the Token endpoint URL (obtained from step 3). This endpoint should look like this:

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

6. Enter the Authorization server URL (obtained from step 3). It should look like this:

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

7. Enter a Redirect URI. Ensure the redirect URI is the same as configured in the Dynamics 365 side as an accepted one, otherwise you will not be able to retrieve the tokens. In our example  https://localhost:9443/oauth/2.0/redirectURL.jsp (the port, host and protocol might differ, depending on the Denodo Platform configuration).
8. Check 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 default permissions, i.e.: https://<instance_name>.crm8.dynamics.com/.default
2. To retrieve the refresh token the scope offline_access.

3. Click Generate the authorization URL and then Generate.
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, click on Paste the authorization response URL and paste it in the field Authorization response URL.
7. Click Obtain the OAuth 2.0 credentials then on OAuth 2.0 credentials and then select the option Copy the credentials to the clipboard to save the response for the next step.

Step 5: Creating a MS Dynamics data source

From the Web Design Studio go to File > New > Data source and enter “Dynamics” in the search box and select Dynamics 365 Customer Engagement from the options that will show up.

The Dynamics 365 customer engagement wizard will show up. To connect to MS Dynamics we will have to enter the following information:

• Database: The Denodo virtual database where the data source and views will be created.
• Instance URL: The URL of the instance we will be connecting to, normally with the format <instance_name>.crm.dynamics.com.
• API version: Keep default value (v9.0).
• Access token: Token obtained from the OAuth 2.0 Credentials Wizard.
• Refresh token: Token obtained from the OAuth 2.0 Credentials Wizard.
• Client ID: Client identifier from the registered application.
• Client Secret: Client secret from the registered application.
• Azure directory ID: Directory (tenant) id.

After entering all the information click on Execute import. Under the virtual database that we have created, the data source to connect to MS Dynamics will be created and also the views to access the entities available in the MS Dynamics module that we are integrating.

Previous Denodo Versions

For Denodo versions where the Microsoft Dynamics 365 wizard is not available it is possible to to configure a data source using the OData Custom Wrapper.

Import 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 Denodo Web Design Studio go to: File > Extension management

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

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

Denodo Support Site.

Configure the 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 denodo-odata4-wrapper.
3. The Class name field must be filled with: com.denodo.connect.odata.wrapper.ODataWrapper
4. Click on Refresh Input Parameters.

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 from Step 4).
5. Refresh Token: Enter the Refresh Token (obtained with the OAuth 2.0 wizard 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. Grant Type: Refresh Token.
11. Click the Save button.

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. Open the Dynamics OData source and then click on Create base view.
2. Set as the Entity collection 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 on Create 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