Contents x
Dynamics CRM Query Authentication Guide
  • Dark
    Light
Contents

Dynamics CRM Query Authentication Guide

  • Dark
    Light

Overview

This is a step-by-step guide to creating an OAuth entry, acquiring credentials, and authorizing the Dynamics CRM Query connector for use in Matillion ETL.

Note

  • The Dynamics CRM Query connector uses an OAuth for third-party authentication.
  • While connector properties may differ between cloud data warehouses, the authentication process remains the same.
  • Most third-party apps and services that connect to Microsoft data can be set up for use in Matillion ETL via the Microsoft Azure Portal using much of the same process.

Creating an OAuth entry in Matillion ETL

1. In Matillion ETL, click Project → Manage OAuth. This will open the Manage OAuth dialog.

Note

If you add a Dynamics CRM Query component to an orchestration job, you can access the Manage OAuth dialog by:

  1. Clicking the component icon to open the Properties panel at the bottom of the UI.
  2. Clicking ... next to the Authentication property, and then clicking Manage.

2. Copy the Callback URL in the field at the top of the Manage OAuth dialog. Use the Callback URL in the step Acquiring third-party credentials.

Note

The callback URL must be a HTTPS URL, as Dynamics CRM won't authenticate with a HTTP URL.

3. Click at the bottom-left of the Manage OAuth dialog to open the Add OAuth Entry dialog.

New OAuth entry

4. Name the OAuth entry in the Name field, then click the Service dropdown menu and select Dynamics CRM. Then click OK.

Create OAuth entry dialog

5. On returning to the Manage OAuth dialog, review the list of OAuths to confirm the new entry.

Note

The status of this new entry is currently Not Configured.


Acquiring third-party credentials

1. Open the Microsoft Azure Portal, and enter valid login credentials to continue. On the Microsoft Azure dashboard, click App registrations on the Azure services menu at the top. If App registrations isn't visible, click More services, on the right of the menu, for a longer list of options.

Azure portal dashboard

2. On the App registrations page, click + New registration on the menu at the top of the screen.

3. On the Register an application page, give details for the following fields:

  • Name: name the app.
  • Supported account types: select Accounts in any organizational directory (Any Azure AD directory - Multitenant) and personal Microsoft accounts (for example, Skype, Xbox).
  • Redirect URI: Select Web in the dropdown field and paste the Callback URL (copied from the Manage OAuth window in Matillion ETL earlier). Note that although the page states this field is optional, you must complete it.

Click Register.

Register an application

4. Your browser should redirect to the Overview page on the app's newly created dashboard. From here, copy the credentials to the right of Application (client) ID and Directory (tenant) ID for use later >Authorizing for use in Matillion ETL.

Warning

When copying the credentials, some browsers may add a space to the end of the string. This can cause credentials to fail.

Application overview page

5. Click Certificates & secrets on the menu on the left, and on the Certificates & secrets page click + New client secret.

6. The Add a client secret page will appear to the right. Provide details for the following fields and then click Add:

  • Description: provide a description of the client secret.
  • Expires: use the Expires drop-down to select when the client secret should expire.

Add a client secret

7. You will automatically be returned to the Certificates & secrets page, where the new client secret now appears in the list in the Client secrets tab. Copy the client secret Value for Authorizing for use in Matillion ETL.

Warning

  • Make sure to copy the client secret immediately as it may appear only once.
  • When copying the client secret, some browsers may add a space to the end of the string. This can cause credentials to fail.

Copy client secret

8. Click API permissions on the menu on the left, then click + Add a permission to open the Request API permissions panel on the right of the screen.

9. In the Request API permissions panel, click Dynamics CRM in the list of Microsoft APIs.

Request API permissions

10. This will open the Dynamics CRM panel. Select Delegated permissions, and then select user_impersonation Access as the signed-in user. Then click Add permissions.

Select permissions

11. Click Expose an API in the menu on the left.

12. Before a scope can be added, an Application ID URI will need to be set. Click Set to the right of the Application ID URI field to edit it. Replace the suggested URI with the URI to be associated with the app, then click Save.

13. Click + Add a scope. The Add a scope panel will appear on the right. Provide details for the following required fields:

  • Scope name: a display name for the scope when access to the API is requested. Best practice dictates using a <resource.operation.consent> name structure.
  • Who can consent? select which users can consent to this scope in directories where user consent is enabled: Admins and users, or Admins only.
  • Admin consent display name: a name for the scope to be displayed on admin consent screens.
  • Admin consent description: a detailed description for the scope to be displayed on admin consent screens.

Then click Add scope.

Add a scope

15. Next, navigate to the Office 365 Home page, giving your sign-in credentials if requested. Click the Business Apps tab at the top. Click the app you have just created. This will open an app dashboard. The page's URL will contain your Dynamics CRM account URL. Copy everything before and including dynamics.com, for example:

https://companyname.crm11.dynamics.com/main.aspx#414717258

This copied URL will be required in Authorizing for use in Matillion ETL.


Authorizing for use in Matillion ETL

1. To complete the configuration of the OAuth entry in Matillion ETL, return to the Manage OAuth dialog, and click next to the OAuth entry. This opens the Configure OAuth dialog.

2. Using the credentials from Azure Portal, complete the following fields:

Configure OAuth

3. Click Next, and then click Authorization link to authorize Matillion ETL to use the acquired credentials.

4. The Microsoft Permissions requested page will open. Click Accept.

Permissions requested

5. If all is successful, the browser will return to Matillion ETL stating `Authorization successful`.

What's Next

Cookie consent

We use our own and third-party cookies to understand how you interact with our knowledge base. This helps us show you more relevant content based on your browsing and navigation history.