1. Data Management
  2. Connectivity Studio
  3. Design
  4. Set up connectors

Set up Database connector

Set up a connector of type Database. Use this type to directly connect to an external database. This external database can be an on-premises database or a cloud database.
If you want to connect to an external database via ODBC and you run D365 FO:

  • In the cloud, use an Azure Service Bus to connect to the external database.
  • On-premises, you can connect to the external database without an Azure Service Bus. 
  • On-premises, you can also connect without an Azure Service Bus and additionally use Direct SQL to export data to the external database.

You can also directly connect to an Azure SQL database.

 


Application Consultant Application Consultant Start Start What is your type  of database? What is your type  of database? Set up Azure Service Bus for ODBC connection

Set up Azure Service Bus for Database connection

You can connect D365 FO to an external on-premises database. If you connect to an external on-premises database, you must connect through firewalls. With Connectivity studio, you can connect to an external on-premises database using an Azure Service Bus.
To establish a connection between an external on-premises database and D365 FO with Connectivity studio, these elements must be in place:
  1. Azure Service Bus namespace.
  2. BIS Azure Service Bus client installed on the external on-premises server.
  3. Data Source Name (DSN) on external on-premises server.
  4. Database connector and ODBC document in Connectivity studio on D365 FO.

This picture gives an architectural overview of the Azure Service Bus solution in Connectivity studio with the required elements. (Note: The numbers in the picture correspond with the before-mentioned element numbers.):

Prerequisites to establish this connection:
  • Azure subscription.
  • D365 FO with Connectivity studio installed.
  • External on-premises server and database.

 Create Azure SQL database Create Azure SQL database You can use a connector of type Database to connect to an Azure SQL database. For more information on Azure SQL databases, refer to Azure SQL Database. Set up Database connector Set up Database connector Set up a connector of type Database. Use this type to directly connect to an external database. This external database can be an on-premises database or a cloud database. You can connect to an external database with an: ODBC connection: Connects to an external database via ODBC, using an Azure Service Bus. SQL connection: Connects to an external Azure SQL database. Procedure 1. Click Connectivity studio Integration Design. 2. Click the Connectors tab. 3. Click New. 4. In the Connector field, type a value. Note: You are advised to not include, in the connector name, the name of the application to which you connect. Use the Application field to define the applicable application for the connector. 5. In the Project field, enter or select a value. 6. Define the applicable application for the connector. For a Database connector, for example, select an 'ODBC', 'SQL', or 'ERP' application. In the Application field, enter or select a value. Note: - You can only select an application that is defined for the applicable project. You can define project applications on the Projects page. - You can type any existing application name, whether it is defined as project application or not. If the typed application is not defined as project application, it is automatically added to the project applications. 7. In the Connector type field, select 'Database'. 8. Click Save. 9. Sub-task: Set properties. 10. Expand the Properties of Test section. 11. You can export connectivity setup and import it in another D365 FO environment. For example, you first set up and test the connectivity setup in a development environment. When finished, you deploy the setup in your production environment. To prevent messing up your production data, use a different Azure Service Bus and on-premises database for development or testing purposes. To strictly distinguish between different environments, you can define unique connector properties for each of your environment types. Which properties are applicable to the current environment is defined in the Environment type field on the Connectivity studio parameters. If set to Development, the connector properties, as defined for entity type Development are applicable. In the Environment types field, select an option. Note: The environment types only apply to connectors of type Database, Azure file storage, Blob storage, SharePoint, or Service Bus queue. 12. In the Windows Azure Service Bus namespace field, enter the primary connection string of the created Azure Service Bus namespace. Note: This field is only applicable if you run D365 FO in the cloud and you use an ODBC connection. If you use Connectivity studio on a D365 FO (on-premises) environment, you can directly connect to another on-premises SQL server database. In this case, you do not need the Azure Service Bus for Database connector setup. 13. Define the primary key or the primary key reference to access the Azure Service Bus namespace. Whether the primary key or primary key reference applies is defined by the Display secret field in the Connectivity studio parameters. If the Display secret parameter is set to: - Secret, fill in the Service Bus key field with the desired primary key. The primary key is specific for the current connector. - Secret reference, fill in the Service Bus key reference field with the desired primary key reference. The primary key reference refers to a centrally stored primary key which makes updating secrets easier. - Both, fill in either the Service Bus key field or the Service Bus key reference field. In the Service Bus key field, or in the Service Bus key reference field, type a value. Note: This field is only applicable if you run D365 FO in the cloud and you use an ODBC connection. 14. In the Database connection field, select an option. 15. In the Data source name field, enter the data source name (DSN) as created on the external on-premise server. Note: - This field is only required for an ODBC connection. - The ODBC connection string, that is used to connect to the ODBC database, is shown in the Connection string field. The connection string is created based on the settings in the ODBC data source and Direct SQL properties. 16. If the connection type is: - ODBC, you can define the login ID to be used to connect to the data source name (DSN) as created on the on-premises server. - SQL, define the login ID to be used to connect to the Azure SQL database, In the Operator name field, type a value. Note: For an ODBC connection: - This 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. 17. In the Password field, or in the Database secret reference field, type a value. 18. If you use Connectivity studio on a D365 FO (on-premises) environment, you can directly connect to another on-premises SQL server database with direct SQL. You can use this connection only to export data from a D365 FO (on-premises) database. If you use direct SQL, you must define the linked server instance. In the Linked server instance field, enter the name of the linked server instance that is defined on the SQL server instance to connect to the D365 FO database. Note: This field is only required for an ODBC connection. 19. In the Database server name field, enter the SQL server instance name. 20. In the Database name field, enter the name of the applicable database on the defined SQL server instance. 21. Usually, the data source name (DSN) is used to connect to the external database. However, if you use direct SQL, you can connect using the driver. By default the 'SQL Server Native Client 11.0' ODBC driver is used. You can define another, ODBC driver. Example: 'ODBC Driver 13 for SQL Server'. In the ODBC driver field, type a value. Note: This field is only required for an ODBC connection. 22. Sub-task: Select custom handler. 23. Expand the Custom section. 24. In the Handler field, you can enter or select a custom handler class. 25. Sub-task: Define company range. 26. You can import or export data for the current D365 FO company only. To do so, define the company range. In import or export of data, the current company in D365 FO is used. If you do not define the company range, data is imported or exported for all companies in the D365 FO environment. Expand the Company range section. Note: The field name of the company field in D365 FO is 'DataAreaId'. 27. In the Field name field, enter the name of the company field in the on-premises server database. 28. In the Transformation field, enter or select a value. 29. In the Type field, select an option. 30. Sub-task: Set advanced options. 31. Expand the Advanced options section. 32. Select Yes in the Cross company disabled field. 33. Close the page. Notes You can test the connection. To do so, on the Connectors page, in the Action Pane, on the Development tab, click Test connection. You can change the type of an existing connector. If you do so, also fill in the applicable fields. For more information, refer to the topic about the setup of the newly chosen connector type. For both the ODBC connection and the SQL connection, these data type are not supported: time, timestamp, binary, varbinary, sql_variant, datatimeoffset. End End ODBC Azure SQL D365 FO on-premises

Activities

Name Responsible Description

Set up Azure Service Bus for ODBC connection

Application Consultant
You can connect D365 FO to an external on-premises database. If you connect to an external on-premises database, you must connect through firewalls. With Connectivity studio, you can connect to an external on-premises database using an Azure Service Bus.
To establish a connection between an external on-premises database and D365 FO with Connectivity studio, these elements must be in place:
  1. Azure Service Bus namespace.
  2. BIS Azure Service Bus client installed on the external on-premises server.
  3. Data Source Name (DSN) on external on-premises server.
  4. Database connector and ODBC document in Connectivity studio on D365 FO.

This picture gives an architectural overview of the Azure Service Bus solution in Connectivity studio with the required elements. (Note: The numbers in the picture correspond with the before-mentioned element numbers.):

Prerequisites to establish this connection:
  • Azure subscription.
  • D365 FO with Connectivity studio installed.
  • External on-premises server and database.

Create Azure SQL database

Application Consultant

You can use a connector of type Database to connect to an Azure SQL database.

For more information on Azure SQL databases, refer to Azure SQL Database.

Set up Database connector

Application Consultant

Set up a connector of type Database. Use this type to directly connect to an external database. This external database can be an on-premises database or a cloud database.

You can connect to an external database with an:

  • ODBC connection: Connects to an external database via ODBC, using an Azure Service Bus.
  • SQL connection: Connects to an external Azure SQL database.

Set up Azure Service Bus for Database connection

See also

Provide feedback