1. To-Increase Dynamics 365 for Finance & Operations
  2. Connectivity studio
  3. Concepts

Connect D365 FO on-premises database with external database
Concept

You can use a connector of type Database to directly connect to an external database via ODBC. Usually, this external database is an on-premises database. And often, D365 FO is run in the cloud. In this case an Azure Service Bus is required to connect to the external database.

However, you can also run D365 FO on-premises. If you do so, you can directly connect to an external database. You can connect in two ways:

  • Only without an Azure Service Bus.

  • Without an Azure Service Bus and using Direct SQL to export data.

Only without an Azure Service Bus

If you run D365 FO on-premises, and you want to connect to an external database, no Azure Service Bus is required to connect. You can use this way of connecting both for import and export of data.
To be able to connect to the external database:
  1. On the D365 FO server, create a data source name (DSN) .
  2. In D365 FO, set up an Database connector and an ODBC document. Depending on the data integration direction, on the message, use the Database connector and ODBC document as source or target.
To set up the connection, use a connector of type Database. For the connector, several specific settings are required:
 

Field group

Field

Explanation

Azure Service Bus

Windows Azure Service Bus namespace

Leave empty.

Service bus key

Leave empty.

ODBC data source

Data source name

Enter the data source name (DSN) as created on the external server.

Operator name

You can define the login ID to be used to connect to the data source name (DSN) as created on the external server.

Password

You can define the password to be used to connect to the data source name (DSN) as created on the external server.
 
Example:
Note:
  • The operator and password are only applied if no Login ID and Password are defined for the DSN.
  • If you do not define a login ID and password here, make sure the Login ID and password are defined on the DSN.

Without an Azure Service Bus and using Direct SQL

If you run D365 FO on-premises, you can directly connect to another on-premises SQL server database with direct SQL. So, no Azure Service Bus is required to connect. You can use this connection only to export data from a D365 FO (on-premises) database. Compared to the 'Only without an Azure Service Bus' connection, the direct SQL connection is faster, but has some limitations.
To be able to connect to the external database:
  1. On the external on-premises SQL server, create a linked server.
  2. In D365 FO, set up an Database connector and an ODBC document. On the message, use the Database connector and ODBC document as target.

Linked server

On the external on-premises SQL server, create a linked server . The linked server is used to link the external on-premises SQL database to the D365 FO database. Make sure, the linked server can read from the D365 FO database.
Note: If the external on-premises SQL database runs on the same server as the D365 FO database, no linked server is required.

Connector

To set up the connection, use a connector of type Database. For the connector, several specific settings are required:
 

Field group

Field

Explanation

Azure Service Bus

Windows Azure Service Bus namespace

Leave empty.

Service bus key

Leave empty.

ODBC data source

Operator name

You can define the login ID to be used to connect to the external database server. If not defined, the connection is automatically handled as trusted.

Password

You can define the password to be used to connect to the external database server. If not defined, the connection is automatically handled as trusted.

Direct SQL

Linked server instance

Enter the name of the linked server instance that is defined on the external SQL server to connect to the D365 FO database.

Database server name

Enter the SQL server instance name of the external on-premises SQL server where you created the linked server.
Instead of the Data source name, define the Database server name and Database name. You connect to the external database using the driver instead of the DSN.

Database name

Enter the name of the applicable database on the defined SQL server instance.

ODBC driver

If you use direct SQL, you connect using the driver. By default, the 'SQL Server Native Client 11.0' ODBC driver is used. Optionally, you can define another, ODBC driver. Example: 'ODBC Driver 13 for SQL Server'.
 
Example:      
Note: The blurred fields are optional.

Message header

To export data with direct SQL, some specific settings are required on the message header:
 

Section

Field

Explanation

General

Source connector

Must be of type 'D365 FO'.

Source document

Must be of type 'D365 FO'.

Target connector

Must be of type 'Database'.

Target document

Must be of type 'ODBC'.
Make sure, the target document only has one record. This is because the 'Insert into' command only supports one table at a time.

Custom

Message type

Select ‘SQL’.

Handler

Select ‘BisMessageRunDirectSQL’. This handler class gets the data from the D365 FO database and directly enters it into the external on/premises SQL database.
 
Example:

Message mapping

If you use direct SQL, the standard D365 FO logic cannot be applied. The field mapping is handled by the SQL statement which is executed on the external SQL server. Therefore, not all message mapping options are supported.
 

Supported message mapping options:

  • Record action: Update or insert. The other record actions are not possible for direct SQL.
  • Key field
  • Constant values
  • Value transformations
  • Custom handler: Custom handlers are executed only once when the message starts. The result is used in the mapping.
  • Dimension set
  • Ledger

Unsupported message mapping options:

If you use direct SQL, these message mapping options are not supported:

  • Record action: Except for 'Update or insert', no record actions are supported.
  • Target mapping: On the Field mapping, in the Source record field, on the lookup, you can change the Direction to Target to create a target mapping. For direct SQL, this is not supported in the execution of the message.
  • Default value
  • Range transformations
  • Type conversion
  • Display method
  • Edit method
  • Company-specific field mapping

Run direct SQL message

Each time, the message is run, an SQL statement is created and run on the external SQL server. This SQL statement reads the data from D365 FO and writes it to the external on-premises database.
If, on the message field mapping, you have:
  • Defined key fields, two SQL statements are run: One to update existing records and one to insert records.
  • Not defined key fields, only the SQL statement to insert records is run.

Example of direct SQL message

As an example, three field values are exported from the D365 FO CustTable to the customers table in the external SQL database. The applicable message has this record and field mapping:
In the example, a key field is used: AccountNum. As a result, when the message is run, two SQL statements are created and run on the external SQL database:
  • SQL statement to update existing records:
    UPDATE TrgCustomer
     SET TrgCustomer.customergroup = custtable.CustGroup ,
       TrgCustomer.currency = custtable.Currency
     FROM [MyDatabase].[dbo].[TrgCustomer] TrgCustomer
     join [LinkedServerToD365Fo].[AxDB].[dbo].[CUSTTABLE] [custtable] on
    custtable.DataAreaId = 'usmf' and
    custtable.Partition = 5637144576
    AND TrgCustomer.Id = custtable.AccountNum 
  • SQL statement to insert new records:
    INSERT INTO [MyDatabase].[dbo].[TrgCustomer] (Id ,customergroup ,currency )
     Select custtable.AccountNum ,custtable.CustGroup ,custtable.Currency
     FROM [LinkedServerToD365Fo].[AxDB].[dbo].[CUSTTABLE] [custtable]
     Where custtable.DataAreaId = 'usmf' and
    custtable.Partition = 5637144576 AND
    NOT EXISTS ( select * from [MyDatabase].[dbo].[TrgCustomer]
    where TrgCustomer.Id = custtable.AccountNum )
Explanation of several parts in these statements:
 

Statement line

Element

Explanation

[MyDatabase].[dbo].[TrgCustomer]

 

[MyDatabase]

The database as defined in the Database name field on the target connector.

 

[dbo]

The owner schema as defined in the SQL owner schema field on the target document record details.

 

[TrgCustomer]

 The applicable record of the target document.

[LinkedServerToD365Fo].[AxDB].[dbo].[CUSTTABLE] [custtable]

 

[LinkedServerToD365Fo]

The linked server the linked server instance of the external SQL server, as defined in the Linked server instance field on the target connector.

 

[AxDB]

The standard database name for the D365 FO database.

 

[dbo]

The standard SQL owner schema of D365 FO.

 

[CUSTTABLE]

The applicable record of the source document.

Related to Notes

Set up Database connector

 		 

Set up Azure Service Bus for ODBC connection

 		 

Set up Database connector

 		 

Set up Azure Service Bus for ODBC connection

 		 

Provide feedback