Dynamics 365 Business Central Query Authentication Guide
  • Dark
    Light

Dynamics 365 Business Central Query Authentication Guide

  • Dark
    Light

Overview

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

Note

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 this is documented here. This method is deprecated, however, and replaced by the OAuth method described in this article.


Creating an OAuth entry in Matillion ETL

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

Note

If a Dynamics 365 Business Central Query component has already been added to an orchestration job, the Manage OAuth dialog may also be accessed using the following method:

  1. Click the component icon to open the Properties panel at the bottom of the screen.
  2. Click ... next to the Authentication property, and then click Manage.

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

Note

The callback URL must be a HTTPS URL, as Dynamics 365 will not 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. Provide a name for the OAuth entry in the Name field, then click the Service dropdown menu and select Dynamics 365 Business Central. Then click OK.

Create OAuth entry dialog

5. On returning to the Manage OAuth dialog, confirm the creation of the new entry.

Note

The status of this new entry is currently Not Configured. Configuration of the OAuth entry will be discussed in Authorizing for use in Matillion ETL.



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 is not 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, provide details for the following fields:

  • Name: provide a name for the app.
  • Supported account types: select Accounts in any organizational directory (Any Azure AD directory - Multitenant) and personal Microsoft accounts (e.g. Skype, Xbox).
  • 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.

Click Register.

Register an application

4. The browser will then 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.

Warning

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

Application overview page

5. 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 at the top of the page.

Activate ID tokens for authentication

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

7. 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.

Add a client secret

8. You will automatically be returned to the Certificates & 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.

Warning

  • 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 string. Watch out for this as it will cause the credentials to fail.

Copy client secret

9. 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.

10. In the Request API permissions panel, click Dynamics 365 Business Central in the list of Microsoft APIs.

Request API permissions

11. 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.

Select permissions

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

13. Before a scope can be added, an Application ID URI will need to be set. Click the pencil (edit) icon 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.

Enter the application ID URL

14. 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.

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 Dynamics 365 - custom app. This will open a Dynamics 365 dashboard. The page's URL will contain your Dynamics 365 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 previously created OAuth entry in Matillion ETL, return to the Manage OAuth dialog, and click next to the OAuth entry. This will open the Configure OAuth dialog.

2. Using the credentials copied previously from the Microsoft Azure Portal, provide details for 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".



Using Web Service Access Key authentication (DEPRECATED)

Caution

  • This section is for customers using versions of Matillion 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 Search icon on the menu in the top right.

Dynamics Business Central dashboard

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.

Search for users

4. On the Users page, click the User Name to be associated with the API.

Select a user

5. Copy the credential to the right of Web Service Access Key as it will be required when configuring the authorization in Matillion ETL.

Copy the Web Service Access Key

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.

Paste the Access Key

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