Connecting to hosted Dynamics 365 with OAuth2

Henry Simms

Data Studio’s Dynamics 365 connector can be used to read or write data from Microsoft Dynamics 365 apps.

• Click for a full list of supported Dynamics 365 ERP and CRM apps by the driver
• Click for a the driver's user documentation
• Click for basic config and troubleshooting steps in the Data Studio documentation

The driver supports OAuth 2.0 for accessing web instances of Dynamics 365 services. The following grant types are supported:

1. Access token flow (use access and refresh token in credentials)
2. Refresh token grant (use refresh token in credentials)
3. Client credentials grant (just use client ID and secret in credentials, which must be linked to an Application User)

The following guide is a brief description of how to set up an OAuth 2 connection to Dynamics 365 from Data Studio, using the native connector.

Setting up OAuth Access

There are a number of steps involved in setting up this access. For any of the grant types you will need to:

1.     Register your app

If it is not already registered, you will need to register your application as an Azure Active Directory application to implement an OAuth 2.0 grant flow and run an application against a Dynamics 365 web instance.

Create this in the Azure Portal by adding a new App registration:

You will need to define at least one redirect URI. The Application ID URI can be left blank.

2.     Get client information and endpoints

Client information and endpoints are used to retrieve the OAuth tokens necessary for connecting to a Dynamics 365 service. Now that you have registered the app, in the Azure portal:

• - Select the app and get the Application (client) Id, which will be used as the ClientID when connecting using Data Studio.
• - Select Certificates & secrets and create a new client secret. Record this to use as the ClientSecret.

Back on the app’s Overview page, click Endpoints:

Dynamics 365 supports two different APIs to manage user and application permissions: Azure Active Directory (v1) and Microsoft Identity Platform (v2). Authorization and token endpoints have different formats, depending on which API you are using.

Determine which API is to be used and record the authorization endpoint (to be used as the Auth URI) and token endpoint (to be used as the TokenURI).

3.     Determine the Scope

If using the Microsoft Identity Platform (v2) API to manage permissions, a scope must be specified using the Scope property when retrieving the OAuth tokens necessary for connecting to a Dynamics 365.

The value of the Scope property may consist of an OAuth scope or a space-separated list of OAuth scopes. The following syntax applies to each scope specified by the scope property.

resource_uri/scope_name offline_access

where:

resource_uri is the URI for your Dynamics 365 instance and is found at the start of the ServiceURL. For example, https://mywebinstance.api.crm.dynamics.com is the resource URI for the Service URL https://mywebinstance.api.crm.dynamics.com/api/data/v9.1/.

scope_name is the name of a scope being enforced against the Dynamics 365 service.

offline_access is a scope that enables prolonged access to resources on behalf of a user. This scope must be included if you are retrieving a refresh token.

Example, showing a scope for a Dynamics CRM instance with the user_impersonation and offline_access scopes.

• Scope=https://mywebinstance.api.crm.dynamics.com/user_impersonation offline_access

4.     Get the service URL

This is the REST endpoint of the Dynamics 365 service to which you are connecting. To find the Service URL logon to the instance of Dynamics 365 to which you are connecting.

From the application dropdown, select Settings > Customizations > Developer Resources and locate the “Service Root URL”.

Connecting with Access token flow

Now that you have done the necessary setup you can establish the OAuth connection. The access token flow method passes the access token directly from the client to the Dynamics 365 service for authentication.

When using access token flow, a Data Studio credential will need to include:

• Client ID
• Client Secret
• Access Token
• Refresh Token

You should use the driver’s Configuration Manager to generate the necessary OAuth tokens. You can access this by executing the driver jar file located in the Data Studio database's \drivers\jdbc directory, for example:

"C:\Program Files\Experian\Aperture Data Studio {version}\java64\bin\java.exe” -jar "C:\ApertureDataStudio\drivers\jdbc\DSdynamics365.jar"

This will open up a web application in your browser. If the install directory or repository are not in the default locations, modify the paths as required. 

In the application, populate the Service URL, Auth URI, Token URI, Client ID, Client Secret, and optional Scope, using the values you recorded in the precious steps. Click "Fetch Oauth Token" to generate Access and Refresh tokens.

Note: The Client Secret must be surrounded by single quotes when entered in the field for the Configuration Manager. This does not apply when using the client secret value in Data Studio.

You can view the values for these tokens by clicking the “edit” button on the generated connection string:

Connecting with Refresh token grant

The refresh token grant is used to replace expired access tokens with active ones by exchanging the refresh token at the endpoint specified by the TokenURI property.

Follow the same steps as for “Connecting with Access token flow”, but you do not need to specify the access token in the Data Studio credential you create. Leave the Access Token blank.

Connecting with Client credentials grant

The authentication flow for the client credentials grant exchanges client credentials for the access token at the location specified by the TokenURI. Web-based login and consent are not required.

For the client credentials grant, you must create an application user on your Dynamics 365 web instance.

To create an application user for the client credentials grant:

1. Logon to your Dynamics 365 instance.
2. From the application dropdown, select Settings, then got to Security > Users.
3. Switch to the Application User form by clicking the User dropdown and selecting Application User.
4. Click New to create a new user
5. Enter the following details:
   1. User name
   2. Application ID. This should be the Client ID of your application (from the registered app you created earlier in the Azure Portal)
   3. Full name and primary email in the fields provided.
6. Application ID URI and Azure AD Object ID fields should be left blank.

Now that you’ve created an application user you can configure the connection in Data Studio. To set up the Connection in Data Studio, create a new External system and enter:

1. Service Url, Authentication URL: The URLs recorded in “Setting up OAuth Access” steps
2. Token URI: The value of the TokenURI property must begin with the POST command followed by the token URI or the endpoint used to exchange authentication credentials for access tokens. For example: TokenURI=POST https://login.microsoftonline.com/common/oauth2/v2.0/token
3. Scope property if using the Microsoft Identity Platform (v2) to provision users and manage application access. For example: Scope=https://mywebinstance.api.crm.dynamics.com/.default
4. In the Test connection credentials, or connection credentials, just enter
   1. ClientID property to specify the client ID key for your application.
   2. ClientSecret property to specify client secret for your application

Charlie Chambers

I recently set up a connection to hosted Dynamics 365 with OAuth2, though rather than using the Configuration Manager I was able to generate the Access and Refresh Tokens using Postman.

The following steps describe how to obtain a refresh token by issuing a Postman request to the Azure endpoint for your Dynamics 365 application.

1. Open Postman.
2. Click Authorization.
3. Select OAuth 2.0 from the TYPE dropdown.
4. Enter the following information on the Current Token panel.

• Grant Type: Select Authorization Code.
• Callback URL: Enter the redirect URI. For Postman, this value is http://localhost.
• Auth URL: Enter the authorization URI.
• Access Token URL: Enter the token URI.
• Client ID: Enter the client ID.
• Client Secret: Enter the client secret.
• Scope: Enter scope.

1. Obtain the refresh token.
   1. Click Get New Access Token.
   2. If logon popup appears, enter Azure Active Directory credentials. (This popup may not appear if you previously logged on.)
   3. If consent popup appears, provide consent, allowing the Postman to retrieve the tokens. (This popup may not appear if you previously provided consent.)
   4. Note: Access and refresh tokens are returned in the MANAGE ACCESS TOKENS window.
   5. Copy the refresh token.