Follow

Microsoft Dynamics Business Central (NAV) Adapter

Microsoft Dynamics Business Central (NAV) Adapter

This adapter simplifies the extraction of data from Microsoft Dynamics Business Central (NAV).

If you connect to a Dynamics Business Central (NAV) database as a regular data source, you will have to apply and maintain selection rules on all tables because different companies are stored in separate tables. With Dynamics Business Central (NAV) adapter, you can select company accounts at a global level and apply only one set of selection rules. It is, however, also possible to overrule this behavior on a table by table basis.

Adding a Dynamics Business Central (NAV) Adapter

  1. Open a business unit, right click Data Sources, click Adapter Data Sources and then click Add Dynamics Business Central (NAV) Adapter.
  2. Click Wizard Setup and, in the Choose Dynamics Business Central (NAV) Provider window, select the provider you want to use:
    • Microsoft SQL Provider: Data is stored in SQL Server
    • Navision Native: Data is stored in the legacy native Navision format.
    • BC 365
  3. In the Name box, type a name for the adapter.
  4. [Optional] Select Read Aggregation Tables - SIFT if you need to include Sum Index Flow Technology (SIFT) tables,
  5. Click OK.
  6. The flow continues with setting up the provider you selected. Please refer to the sections below:
  7. A window appears where you can input the table that contains the company accounts if it is different from the defaults:
    • Schema: Default: "dbo"
    • Table: Default: "Company"
    • Field: Default: "Name"
  8. Click OK.
  9. The Adapter Settings window opens.
    • Select Enable Enhancements to enable enhancements and enable the following settings.
      • Display language
      • Option field text length
      • NAV 2013 or Later: Select this if the version of Dynamics Business Central (NAV) is 2013 or later.
    • In the Invalid Identifiers box, enter any invalid identifiers.
    • Select Consistent Read to only transfer records added before an execution is started, i.e. records added or updated during an execution will not be transferred. This setting only has effect if you use the SQL Server provider.
      Warning: Take care when using this option when records are likely to be updated or deleted. If a record is updated during execution, the timestamp will change and subsequently, the updated record will not be copied to the staging database during execution.

Setting Up a Microsoft SQL Server Provider

  1. Right-click the adapter, click Source Providers and then click Microsoft SQL Provider.
  2. In the Server name box, enter the location of the server.
  3. In the Authentication list, click the mode of authentication you want to use. You have the following options:
    • Windows Authentication: Use the logged-in Windows user's credentials for authentication.
    • SQL Server Authentication: Use a login set up on the SQL Server. Enter the username and password in the corresponding fields.
    • Azure AD Password Authentication: Use Azure AD credentials from a domain that is not federated with Azure AD. Enter the username and password in the corresponding fields.
    • Azure AD Integrated Authentication: Use the logged-in Windows user's credentials for authentication, provided that he is logged in using Azure AD credentials from a domain that is federated with Azure AD.
  4. In the Database field, enter the name of the database.
  5. In the Connection timeout box, enter the number of seconds to wait before terminating the attempt to connect to the server, and then click OK.
  6. In the Command timeout box, enter the number of seconds to wait before terminating the attempt to connect to the database.
  7. (Optional) In the Encrypt connection list, you can enable encryption of the connection, which is recommended when you are not in a private network (e.g. when your server is on Azure). You have the following options:
    • No: The communication is not encrypted (default).
    • Yes: The communication is encrypted. The server's certificate is verified by a certificate authority.
    • Yes, trust server certificate: The communication is encrypted. but the server's certificate is not verified. This setting is not recommended for use on public networks.
  8. In the Use SSIS for transfer list click on Yes to enable SQL Server Integration Services data transfer, No to disable it or leave it at As Parent to respect the project setting.
  9. Select Force codepage conversion to convert all fields to the collation of the data warehouse.
  10. Select Force Unicode conversion to declare all alphanumeric fields as nvarchar .
  11. Select Allow dirty reads to allow reading from the source without locking the table.
  12. If you want to add additional connection settings, click Additional Connection Properties . In the Connection String Properties window, enter the required connection strings, and then click OK .

Setting Up a Navision Native Provider

When you want to retrieve data from Navision databases hosted in a Native Navision server environment, you will have to use ODBC. The Navision ODBC driver must be installed and configured prior to adding the Native NAV data source.

  1. Expand the business unit, and then right-click Data Sources .
  2. Point to Adapter Data Sources , and select Add Dynamics Business Central (NAV) Adapter.
  3. Select Wizard Setup.
  4. Select Navision Native.
  5. In the Name field, type the name of the data source.
  6. In the DSN Name , select the ODBC connection that you have configured for the data source. In the Escape Character list, select the escape character specific to your ODBC driver. The Text Type Behavior fields are used to control how the ODBC driver handles text. These fields are optional. You have the following options:
    Option Definition
    Set Length Specifies an exact text string length
    Set Variable Length True, if you want a variable text string length
    Set Unicode True, if you want to use Unicode
  7. (Optional) In Set Number of Decimals , specify a fixed number of decimals.
    Note: "Convert Out of Range Dates to MS SQL min/max" is not available for Navision native databases.
  8. Select Use low compatibility mode if you have trouble retrieving data from the database.
  9. In the Command Timeout field, specify the number of seconds to wait before terminating the attempt to connect to the database.
  10. In the Connection Timeout field, specify the number of seconds to wait before terminating the attempt to connect to the server.
  11. If you want to add additional connection strings, click the Additional Connection Properties button. In the Connection String Properties window, type the preferred connection strings, and then click OK .

Setting Up a BC 365 Provider

  1. In the Wizard Setup, select BC 365.
  2. In the Name field, type the name of the data source.
  3. In the API URL enter https://api.businesscentral.dynamics.com/v2.0/{tenantId}. Replace {tenantId} with the actual Azure AD Tenant ID which can be found by navigating to the BC environment in https://businesscentral.dynamics.com/, selecting the “?” icon and selecting “Help & Support”. Enter the environment which can also be found in the same location.Picture1.pngPicture13.png
  4. Install "Jet Analytics" from the Extensions Marketplace in BC and enable the setting "Allow HttpClient Request", as shown in the following article. https://support.jetglobal.com/hc/en-us/articles/360051062673-Connecting-to-Business-Central-Version-16-or-Later
  5. If using OAuth 2.0 authentication, select this option in the Authentication dropdown and enter a legitimate file path (e.g. C:\oauth\oauth.txt) for the locally stored OAuth token to authenticate the connection. The text file does not have to be created beforehand, as TimeXtender will create it once you authorize OAuth.
  6. Ensure that the user that will be authorizing the connection has D365 AUTOMATION or SUPER access in BC, this can be achieved by navigating to the relevant environment in https://businesscentral.dynamics.com/, and then click on the search button in the top right, and search for users, and click on users. Select the relevant user, and add either SUPER permission set (with blank company name) under User Permission Sets, or D365 Automation.Picture3.pngPicture4.pngPicture5.png
  7. Click on authorize OAuth, and enter the user credentials with D365 AUTOMATION or SUPER access in BC.
  8. If authenticating using “Service to Service Authentication” then select this option in the Authentication dropdown.
  9. Follow Task 1 in the guide below to register an Azure AD application for authentication to BC, and under “API permissions” select “Add a permission”, and add the application permissions: https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/administration/automation-apis-using-s2s-authenticationPicture6.pngPicture7.png
  10. Follow Task 2 in the same guide above, by searching for Azure Active Directory Applications in BC and selecting new, and for the client ID enter the Application (client) ID for your app registration, which can be found in the Overview page in Azure, and enter a description. Give the Application the “D365 AUTOMATION” User Permission Set.Picture8.pngPicture9.pngPicture10.png
  11. In the TimeXtender BC 365 Provider window, enter the Client id which is the Application (client) ID for your app registration in Azure used earlier. Also enter the Client secret which is the Value under “Certificates & secrets” in your app registration in Azure. Click OK

 

Picture11.pngPicture12.png

 

Dynamics Business Central (NAV) Companies

Changing the Dynamics Business Central (NAV) Company Table

By default, when you add a Dynamics Business Central (NAV) adapter, the company account table is set to dbo.Company.

However, it is possible to change the account table. This is generally not recommended.

  1. Right-click the Dynamics Business Central (NAV) adapter, and then click Edit Account Table .
  2. In the Table list, select the account to table that you want to use.
  3. In the Name Field list, select the field that contains the account name, and then click OK .

Setting Up Dynamics Business Central (NAV) Companies

When you have added a Dynamics Business Central (NAV) adapter and specified a provider, you need to set up accounts representing the companies requiring the extracted data.

To set up accounts:

  1. Right-click the Dynamics Business Central (NAV) adapter, and then click Set Up Accounts . A window is displayed that shows all companies in the database.
  2. In the Template list, select the company account you want to use as template for the table and column structure. If you are only selecting one company, then the template company must match the company that is selected.
  3. Select Use to specify whether to retrieve data from the company.
  4. In the Default Table Usage list, specify the order in which tables are retrieved and read. You have the following options:
    Option Definition
    Primary Data from this company account is read and retrieved first
    Secondary Data from this company account is read and retrieved after the primary account if they have not already been retrieved from the primary account
    None Tables from this company are not retrieved, unless you specify at table level that you want to retrieve data from a specific table

Modifying Table Usage on Dynamics Business Central (NAV) Tables

When you set up accounts, you specify the default order in which data is retrieved from the individual accounts. However, it is possible to specify a different order of priority for individual tables.

  1. Expand Data Sources , right-click the NAV adapter that contains the tables whose priority you want to change, and then select Modify Table Usage . The company accounts and the usage of all tables will be displayed.
  2. Right-click the field that contains the setting for the table and the account for which you want to change priority of data retrieval. You have the following options:
    Priority Definition
    Default Data from this table is read and retrieved first
    Primary Data from this table is read and retrieved first
    Secondary Data from this table is read and retrieved after the primary table if they have not already been retrieved from the primary table
    None Data from this table is not retrieved
    1-9 Specify the order of priority in the range from 1-9.
    Enter priority If the order of priority exceeds the numbers 1-9, you can specify additional numbers.
  3. Click OK .

Modifying the Usage of a Single Dynamics Business Central (NAV) Table

You can change the order in which data is retrieved from individual tables.

  1. Right-click the Dynamics Business Central (NAV) adapter that contains the table whose priority you want to change, and then select the preferred table.
  2. Right-click the table and select Modify Single Table Usage . The company accounts and the usage specified in Setup Company Accounts will be displayed.
  3. Right-click the field containing the settings for the table, and then specify the table usage. You have the following options:
    Priority Definition
    Default Data is retrieved based on the settings specified when you set up the company accounts.
    Primary Data from this table is read and retrieved first
    Secondary Data from this table is read and retrieved after the primary table if they have not already been retrieved from the primary table
    None Specify the order of priority in the range from 1-9
    Enter priority If the order of priority exceeds the numbers 1-9 you can specify additional numbers here
  4. Click OK .

Changing Dynamics Business Central (NAV) Schemas

You can change the schema for the entire Dynamics Business Central (NAV) adapter or for individual tables that belong to the adapter.

  1. Right-click the Dynamics Business Central (NAV) adapter and then click Change Schema .
    - OR -
    Expand the Dynamics Business Central (NAV) adapter, and then select the table whose schema you want to change.
  2. In the Select Schema to change list, select the schema you want to change, and then select Change Schema .
  3. In the New Schema Name field, enter a name for the schema, and then click OK.
Was this article helpful?
0 out of 0 found this helpful

3 Comments

  • 0
    Avatar
    Rogier Helmus

    Can this be updated for OAuth authentication which requires an OAuth configuration file to be made/linked to?

    Edit: got this response from support: "You simply have to point to a text file path. e.g. C:\oauth.txt. There is no need to create a file beforehand."

    Edited by Rogier Helmus
  • 0
    Avatar
    Rogier Helmus

    A tip when you want to use the BC365 provider since this is not in the instructions -> the dropdown box for Environment has the options "Production" and "Sandbox". The environment names for your client can however be different. The input box also allows you to enter a custom value. You can check the environment name you need to configure by logging into BC with the account used for authorization. Then click on the question mark (top right) in BC and click on  "Help and support". When you scroll down on that page you can see the environment name after the Azure AD tennant. E.g. "Azure AD tenant: xxxxxxxxxxxxxxxxxxxxxxx, Environment: <EnvironmentName> (Production)". The environment <EnvironmentName> is in this case the production environment.

  • 0
    Avatar
    Rogier Helmus

    When choosing OAuth authentication in the BC365 provioder, the account used must have the SUPER user permission. If not, you will get messages that the connector can't reach certain tables (e.g. when trying to synch tables). You can set this permission by going to the users administration and search for the account. On the User Card under the section "User permission sets" fill in the field [Permission Set] with "SUPER" and leave the [Company] field empty. Field [Permission Scope] = "System". If you get an error this is because the BC admin needs to grant this permission.

Please sign in to leave a comment.