Dynamics 365 Business Central Query Authentication Guide
    • Dark
      Light

    Dynamics 365 Business Central Query Authentication Guide

    • Dark
      Light

    Article Summary

    Overview

    This is a step-by-step guide to acquiring credentials for authorizing the Dynamics 365 Business Central Query component for use in Matillion ETL.

    Note
    • The Dynamics 365 Query Business Central 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 through the Microsoft Azure Portal using much of the same process.
    • Older versions of Dynamics 365 Business Central used Web Service Access Key authentication. Matillion ETL still supports an older version of the Dynamics 365 Business Central Query connector that will use a Web Service Access Key, and is documented below. This method is deprecated, however, and replaced by the OAuth method described in this article.

    Prerequisites

    Begin by creating an OAuth entry in Matillion ETL, as described in Manage OAuth. You should then configure this OAuth entry using the Dynamics 365 credentials, obtained as described below.

    The OAuth entry requires four pieces of information: the Client ID, Client Secret, and Azure Tenant are obtained from the Azure Portal as described in Acquiring Dynamics 356 credentials, below; the Organization URL is determined as described in Organization URL, below.


    Acquiring Dynamics 365 credentials

    1. Log in to the Microsoft Azure Portal, and enter valid login credentials to continue. On the Microsoft Azure dashboard, click App registrations on the Azure services menu. If App registrations is not visible, click More services, on the right of the menu for a longer list of options.
    2. On the App registrations page, click + New registration.
    3. On the Register an application page, provide details for the following fields:
      • Name: A name for the app.
      • Supported account types: Select Accounts in any organizational directory (Any Azure AD directory - Multitenant).
      • Redirect URI: Select Web in the drop-down 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.
    4. Click Register.
    5. The browser will 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, as they will be required later in authorizing for use in Matillion ETL.
    Note

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

    1. On the menu on the left, click Authentication. Scroll down to the Implicit grant and hybrid flows section, and select the checkbox next to ID tokens (used for implicit and hybrid flows), then click Save.
    2. Click Certificates and secrets on the menu on the left, and on the Certificates and secrets page, click + New client secret.
    3. The Add a client secret page will appear to the right. Provide details for the following fields:
      • Description: Provide a description of the client secret.
      • Expires: Use the Expires drop-down to select when the client secret should expire, then click Add.
    4. You will automatically be returned to the Certificates and secrets page, where the new client secret will appear in the list in the Client secrets tab. Copy the client secret Value, as it will be required in authorizing for use in Matillion ETL.
    Note
    • Make sure to copy the client secret right away as it may appear only once.
    • When copying the client secret, some browsers may add a space to the end of the string. This will cause the credentials to fail.
    1. 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.
    2. In the Request API permissions panel, click Dynamics 365 Business Central in the list of Microsoft APIs.
    3. This will open the Dynamics 365 Business Central panel. Select Delegated permissions, and then select user_impersonation Access as the signed-in user. Then click Add permissions.
    4. Click Expose an API in the menu on the left.
    5. 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.
    6. 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.
    • User consent display name: A name for the scope to be displayed on user consent screens.
    • User consent description: A detailed description for the scope to be displayed on user consent screens.
    1. Click Add scope.

    Next, determine the Organization URL, as described in the following section.


    Organization URL

    The OAuth in Matillion ETL requires a Business Central Organization URL. There are two possible forms of this URL, depending on whether you are using the Dynamics 365 Business Central Query component to retrieve common service data (for example, accounts, sales orders, sales invoices) or custom objects and service data (KPIs, reports, individual entities, or other feeds). In Dynamics 365 Business Central, search for Web Services to get an idea of what data is exposed by these services.

    Organization URL for common service data

    1. Navigate to your Office 365 Home page, giving your sign-in credentials if requested.
    2. Search for and click the Business Central app. If not immediately visible, it should be located in the All apps section. This will open the Business Central Dynamics 365 dashboard.
    3. Copy the URL of this dashboard from the browser's address bar, including the unique identifier string after the domain name. For example:

    https://businesscentral.dynamics.com/09d19996-a185-4b6c-8332-37120f9bba10/

    This is the Organization URL you'll need when configuring the OAuth.

    Organization URL for custom service data

    Use the following Organization URL for custom service data:

    https://api.businesscentral.dynamics.com/v1.0/ODataV4/

    Note

    If you need both common and custom service data, you'll need to create two separate OAuths in Matillion ETL: both will use the same app created in Acquiring Third-Party Credentials, but one should have the common service URL and the other should have the custom service URL.

    Now return to the Manage OAuth dialog in Matillion ETL to complete the OAuth configuration with these details.


    Using Web Service Access Key authentication

    Note
    • This section is for customers using versions of Matillion ETL before 1.62.
    • Web Service Access Key authentication has been deprecated by Microsoft. We recommend that customers update their Matillion ETL instances to version 1.62 or later to use the OAuth method of authenticating the Dynamics 365 Business Central Query component.
    1. Navigate to the Dynamics 365 Business Central Portal and enter valid login credentials.
    2. On the Dynamics 365 Business Central dashboard, click the Search icon on the menu in the top right.
    3. In the Tell me what you want to do field, enter Users. A list of options will then appear below the search field. Click Users.
    4. On the Users page, click the User Name to be associated with the API.
    5. Copy the credential to the right of Web Service Access Key as it will be required when configuring the authorization in Matillion ETL.
    6. Create an orchestration job and add a Dynamics 365 Business Central Query component to the job canvas.
    7. In the Properties panel at the bottom of the screen, click ... next to the Access Key property.
    8. In the Access Key dialog, select the Store in component option, and paste the Web Service Access Key (copied from the Dynamics 365 Business Central Portal earlier) into the field. Then click OK.
    Note

    Alternatively to storing the access key in the component, you can use the Manage Passwords feature to store the access key behind a named entry.

    1. If the Access Key is entered correctly, the connector should be authenticated and the status of the property will be OK.