Dynamics 365 Sales Query Authentication Guide

Dynamics 365 Sales Query Authentication Guide


Overview

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

Important Information

  • The Dynamics 365 Sales 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 setup for use in Matillion ETL through the Microsoft Azure Portal using much of the same process.

Creating an OAuth Entry in Matillion ETL

  1. In Matillion ETL, on the top left of the screen, click ProjectManage OAuth.

    Please Note

    If a Dynamics 365 Sales Query connector has already been added to an Orchestration Job, the Manage OAuth window may also be accessed using the following method:

    1. Click the connector icon to open the Properties panel at the bottom of the screen.
    2. Click ... next to the Authentication input, and finally click Manage in the pop-up window.
    Project dropdown menu

    Project dropdown menu

  2. Copy the Callback URL in the field at the top of the window as this will be required in Acquiring Third-Party Credentials.

  3. Click + in the bottom left of the window to open the Create OAuth Entry window.

    New OAuth entry

    New OAuth Entry

  4. Click the Service dropdown menu and select Dynamics 365 Sales. Then, provide a name for the OAuth in the Name field and click OK.

    Create OAuth Entry window

    Create OAuth Entry window

  5. On returning to the Manage OAuth window, check the list of OAuths to ensure the new entry is listed.

    Please Note

    This entry is Not Configured. Configuration of the OAuth entry will be discussed in Authorising for use in Matillion ETL.

    OAuth Entry list

    New Entry listed on Manage OAuth window


Acquiring Third-Party Credentials

  1. Navigate to the Microsoft Azure Portal. The Microsoft Azure login screen will appear immediately. Enter valid login credentials to continue. The browser will then redirect to the Microsoft Azure dashboard. Click App registrations on the Azure services menu at the top of the screen.

    Please Note

    If App registrations is not available on the Azure services menu, simply click More services, on the right of the menu, for a longer list of options.

    Microsoft Azure dashboard

    Microsoft Azure dashboard

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

    App registrations

    App registrations

  3. Now, in the Register an application window, provide details for the following fields:

    • Name – provide a name for the app
    • Supported account types – tick the checkbox next to Accounts in any organizational directory (Any Azure AD directory – Multitenant) and personal Microsoft accounts
    • Redirect URI (optional) – paste the Callback URL (copied from the Manage OAuth window in Matillion ETL earlier), then click Register

    Register App

    Register App

  4. The browser will then redirect to the Overview window on the app's newly created dashboard. From here, copy the code to the right of Application (client) ID as it will be required in Authorising for use in Matillion ETL.

    Please Note

    When copying the code, some browsers may add a space to the end of the code. Watch out for this as it will cause the credentials to fail.

    App Overview

    App Overview

  5. On the sidebar menu on the left of the screen, click Authentication. Scroll down the window to the Advanced settings section and tick the checkbox next to ID tokens, then click Save at the top of the window.

    Activate ID tokens for authentication

    Activate ID tokens for authentication

  6. Next, click Certificates & secrets on the sidebar on the left. Then, in the Certificates & secrets window, click + New client secret.

    New client secret

    New client secret

  7. The Add a client secret pop-up window will then appear. Provide details for the following fields:

    • Description – provide a description of the client secret
    • Expires – tick the checkbox next to when the client secret should expire, then click Add

    Add client secret

    Add client secret

  8. Returning to the Certificates & secrets window, the new client secret will appear on the list in the Client secrets section. Copy the relevant client secret as it will be required in Authorising for use in Matillion ETL.

    Please Note

    • Make sure to copy the client secret right away as it may appear only once.
    • Additionally, when copying the client secret, some browsers may add a space to the end of the code. Watch out for this as it will cause the credentials to fail.
    Copy client secret

    Copy client secret

  9. Next, click API Permissions on the sidebar on the left and then, click + Add a permission.

    API Permissions

    API Permissions

  10. The Request API permissions window will appear on the right. Click Dynamics CRM in the list of Microsoft APIs. The Dynamics CRM window will open, from here select Delegated permissions and tick the checkbox next to user_impersonation in the Select permissions section. Then, click Add permissions.

    Add API Permissions

    Add API Permissions

  11. Next, click Expose an API on the sidebar on the left and then, in the Expose an API window click + Add a Scope.

    Expose an API

    Expose an API

  12. The Add a Scope window will appear on the right. Before a scope can be added, an Application ID URI will need to be set. In the Application ID URI field, replace the api:// with the URL to be associated with the app. Then, click Save and Continue.

    Example

    https://www.companyname.com/Application ID URI
    Application ID URI

    Application ID URI

  13. Now, on the Add a scope page, provide details for the following required (*) fields:

    • Scope Name – provide 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 enable
    • Admin consent display name – provide a name for the scope to be displayed on admin consent screens
    • Admin consent description – provide a detailed description for the scope to be displayed on admin consent screens

    Add a scope

    Add a scope

  14. Next, navigate to the Dynamics 365 Home. In the My apps window, either type "Dynamics 365" into the search field at the top of the window or scroll down and click the Dynamics 365 – custom app block for the associated organisation.

    Dynamics 365 Home

    Dynamics 365 Home

  15. The browser will then redirect to one of the organisation's Dynamics 365 dashboards. The page's URL will contain the organisation's Dynamics 365 account URL (everything before and including dynamics.com). Copy this URL as it will be required in Authorising for use in Matillion ETL.

    Example

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

Authorising for Use in Matillion ETL

  1. Return to the Manage OAuth window in Matillion ETL and click next to the previously created OAuth entry. This will open the Configure OAuth window.

    Manage OAuth

    Manage OAuth

  2. Using the codes copied from the Microsoft Azure Portal earlier, provide details for the following fields:

    Please Note

    The Microsoft Online tenant being used to access data. For instance, contoso.onmicrosoft.com. Alternatively, specify the tenant ID. This value is the Directory ID in the Azure Portal > Azure Active Directory > Properties.

    Configure OAuth settings

    Configure OAuth settings

  3. The next window will have an Authorization link. Click the link to authorise Matillion ETL to use the acquired credentials.

    Authorization link

    Authorization link

  4. The browser will then redirect to the Microsoft permissions page, click Accept.

    Microsoft Permissions

    Microsoft Permissions

  5. If all is successful, the browser will return to Matillion ETL with a window stating, "Authorization Successful".

    OAuth Authorization successful

    OAuth Authorization successful