1. Data Management
  2. Connectivity Studio
  3. Activities

Set up Database connector
Application task

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.


Standard 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.
  9.1 Expand the Properties of Test section.
  9.2 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.
  9.3 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.
  9.4 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.
  9.5 Define how you want to connect to an external database:
- ODBC connection: Connects to an external database via ODBC, using an Azure Service Bus.
- SQL connection: Connects to an external Azure SQL database.
  In the Database connection field, select an option.
  9.6 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.
  9.7 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.
  9.8 Define the password or the password reference to connect to:
- For ODBC, the data source name (DSN) as created on the on-premise server.
- For SQL, the Azure SQL database.
Whether the password or password 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 Password field. The password is specific for this connector.
- Secret reference, fill in the Password reference field. The password reference refers to a centrally stored secret which makes updating secrets easier.
- Both, you can fill in either the Password field or the Password reference field.
  In the Password field, or in the Database secret reference field, type a value.
  9.9 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.
  9.10 If you use an ODBC connection with direct SQL, instead of the Data source name, you can define the:
- Database server name
- Database name
In this case, you connect to the external database using the driver instead of the DSN. See also the connection string.
If you use an SQL connection, always define the Database server name and Database name.
  In the Database server name field, enter the SQL server instance name.
  9.11 In the Database name field, enter the name of the applicable database on the defined SQL server instance.
  9.12 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.
10. Sub-task: Select custom handler.
  10.1 Expand the Custom section.
  10.2 For a Database type connector, the standard handler class is BisConnectorODBC. This handler class exports data to or imports data from an external database.
You can use a customized handler class. To do so, extend the standard handler class.
  In the Handler field, you can enter or select a custom handler class.
11. Sub-task: Define company range.
  11.1 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'.
  11.2 In the Field name field, enter the name of the company field in the on-premises server database.
  11.3 If the company names in the on-premises server database are different from the D365 FO company names, define the applicable transformations. In a transformation, you can define which on-premises company name corresponds with which D365 FO company name.
  In the Transformation field, enter or select a value.
  11.4 Define the field type of the company field in the external database.
  In the Type field, select an option.
12. Sub-task: Set advanced options.
  12.1 Expand the Advanced options section.
  12.2 A document can have a query for which cross-company is enabled. If a user, with access to a restricted set of companies, runs a message that uses the document, the cross-company option does not work. If you have this scenario, make sure the cross-company option is enabled for the used connector. Otherwise, you can disable the cross-company option.
  Select Yes in the Cross company disabled field.
13. 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.

Related to Notes

Set up Azure Service Bus for Database connection

 		 

Set up Database connector

 		 

See also

Provide feedback