This page provides a guide to Establishing a Connection to Microsoft Dynamics 365 Business Central in CData Cloud, as well as information on the available resources, and a reference to the available connection properties.

Connecting to Microsoft Dynamics 365 Business Central

Establishing a Connection shows how to authenticate to Microsoft Dynamics 365 Business Central and configure any necessary connection properties to create a database in CData Cloud

Accessing Data from CData Cloud Services

Accessing data from Microsoft Dynamics 365 Business Central through the available standard services and CData Cloud administration is documented in further details in the CData Cloud Documentation.

Connect to Microsoft Dynamics 365 Business Central by selecting the corresponding icon in the Database tab. Required properties are listed under Settings. The Advanced tab lists connection properties that are not typically required.

Cloud Endpoints

To connect to data, specify OrganizationUrl. The OrganizationUrl should be set to one of the following:

• The endpoint to your business central account, such as https://businesscentral.dynamics.com/abc123/.
• The web services root.
• The custom API base url.

On-Prem Endpoints

The following are examples of on-prem endpoints:

https://base URL:port/serverinstance/api/API publisher/API group/API version/
https://base URL:port//serverinstance/ODataV4  
https://myInstance/.local:7048/BC220/ODataV4

The URL is blocked by default; your administrator must enable access to it.

For more details on how to specify the OrganizationUrl and which endpoints are available, see Business Central Endpoints.

If you have multiple companies in your organization, you can specify the Company to indicate which company you would like to connect to. If you leave Company blank, the Cloud retrieves all companies as separate schemas.

User and Access Key

Note: User and Access key Authentication will not be supported for cloud version starting in Apr 2021. For on-premises, Web Service Access Key (Basic Auth) will remain an option for the time being.

To use Access Key auth, set the AuthScheme to Access Key. To authenticate to Microsoft Dynamics 365 Business Central, you may provide the User and AccessKey connection properties. Microsoft recommends these for testing and development purposes, but discourages their use for production environments.

To obtain the User and AccessKey values, navigate to the Users page in Microsoft Dynamics 365 Business Central and then click on Edit. The User Name and Web Service Access Key values are what you will enter as the User and AccessKey connection string properties. Note that the User Name is not your email address. It is a shortened user name.

Connecting to Microsoft Dynamics 365 Business Central

Before you can authenticate to the Microsoft Dynamics 365 Business Central source, set the OrganizationUrl to the URL of the organization you are connecting to. Depending on whether you are using v1 or v2, this could look quite different. For a discussion of the various possible formats for OrganizationUrl see Business Central Endpoints.

Access Key

Set the User along with the AccessKey to authenticate to the Microsoft Dynamics 365 Business Central source.

Azure AD

Azure AD is Microsoft’s multi-tenant, cloud-based directory and identity management service. It is user-based authentication that requires that you set AuthScheme to AzureAD.

Azure Service Principal

Azure Service Principal is role-based application-based authentication. This means that authentication is done per application, rather than per user. All tasks taken on by the application are executed without a default user context, but based on the assigned roles. The application access to the resources is controlled through the assigned roles' permissions.

For information about how to set up Azure Service Principal authentication, see

Creating a Custom OAuth Application

Microsoft Dynamics 365 Business Central supports authentication using Azure AD and Azure Service Principal, both of which are OAuth-based.

This topic describes how to:

• create and register custom OAuth application for Azure AD or Azure Service Principal
• provide Admin Consent to a custom OAuth application

Azure AD

In portal.azure.com:

1. Log in to https://portal.azure.com.
2. In the left-hand navigation pane, select Azure Active Directory, then applicationRegistrations.
3. Click New registration.
4. Enter a name for the application.
5. Select the desired tenant setup: single- or multi-tenant, and public or private use.
   • If you select the default option, "Accounts in this organizational directory only", you must set the AzureTenant connection property to the Id of the Azure AD Tenant when establishing a connection with the CData Cloud. Otherwise, the authentication attempt fails with an error.
   • If your application is for private use only, specify Accounts in this organization directory only.
   • If you want to distribute your application, choose one of the multi-tenant options.

6. Set the redirect url to http://localhost:33333 (the Cloud's default) OR specify a different port and set CallbackURL to the exact reply URL you defined.
7. Click Register to register the new application. An application management screen displays.
   Note the value in Application (client) ID as the OAuthClientId and the Directory (tenant) ID as the AzureTenant.
8. Navigate to Certificates & Secrets and define the application authentication type. There are two types of authentication available: certificate (recommended) or client secret.
   • For certificate authentication: In Certificates & Secrets, select Upload certificate, then upload the certificate from your local machine.
   • For creating a new client secret: In Certificates & Secrets, select New Client Secret for the application and specify its duration. After the client secret is saved, Microsoft Dynamics 365 Business Central displays the key value. Copy this value, as it is displayed only once. This value becomes the OAuthClientSecret.

9. Select API Permissions > Add > Delegated permissions.
10. Save your changes.
11. If you have specified the use of permissions that require admin consent, you can grant them from the current tenant on the API Permissions page.

Azure Service Principal

To use Azure Service Principal authentication, you must set up the ability to assign a role to the authentication application register an application with the Azure AD tenant to create a new Service Principal, and create an external application account in Business Central. That new Service Principal can then leverage the assigned role-based access control to access resources in your subscription.

In portal.azure.com:

1. Create a custom OAuth AD application, as described above.
2. Use the search bar to search for the Subscriptions service.
3. Open the Subscriptions page.
4. Select the subscription to which to assign the application.
5. Open the Access control (IAM).
6. Select Add > Add role assignment. Microsoft Dynamics 365 Business Central opens the Add role assignment page.
7. Assign your custom Azure AD application the role of Owner.
8. Create an external application account in Business Central for your custom application.

Admin Consent

Some custom applications require administrative permissions to operate within an Azure Active Directory tenant. Admin consent can be granted when creating a new custom OAuth application, by adding relevant permissions that are already marked with "Admin Consent Required". Admin consent is also required to use Client Credentials in the OAuth flow.

To grant admin consent:

1. Have an admin log in to portal.azure.com.
2. Navigate to App Registrations and find the custom OAuth application you created.
3. Under API Permissions, click Grant Consent.

This gives your application permissions on the tenant under which it was created.

.

Managed Service Identity (MSI)

If you are running Microsoft Dynamics 365 Business Central on an Azure VM and want to leverage MSI to connect, set AuthScheme to AzureMSI.

User-Managed Identities

To obtain a token for a managed identity, use the OAuthClientId property to specify the managed identity's "client_id".

When your VM has multiple user-assigned managed identities, you must also specify OAuthClientId.

Kerberos

Authenticating to Microsoft Exchange via Kerberos requires you to define authentication properties and to choose how Kerberos should retrieve authentication tickets.

To authenticate to Microsoft Exchange using Kerberos, set these properties:

• hive.server2.authentication: Kerberos.
• AuthScheme: NEGOTIATE.
• KerberosKDC: The host name or IP Address of your Kerberos KDC machine.
• KerberosRealm: The realm of the Microsoft Exchange Kerberos principal. Find this value immediately after the '@' symbol of the principal value.
• KerberosSPN: The service and host of the Microsoft Exchange Kerberos Principal. Find this value just before the '@' symbol of the principal value.

In addition to the authentication values, set:

• Server: The address of the Exchange server you are connecting to.
• Platform: The Microsoft Exchange version.
• Schema: EWS.A.

Microsoft Dynamics 365 Business Central supports several different endpoints to connect to data, with the two main categories being the Common Service endpoints, and the Web Services endpoints. Beyond that, each set of endpoints are split into Production and Testing endpoints. Production endpoints require an OAuth connection, whereas Testing endpoints may be connected to via User and AccessKey. Note that both Production and Testing endpoints access and interact with the same data - they just have different restrictions on what authentication methods are allowed.

Note: Basic authentication will not be supported for cloud version starting in Apr 2021.

The OrganizationUrl by default will attempt to resolve to a Common Service Production endpoint if any https://businesscentral.dynamics.com/ URL is specified. However, this may be configured by modifying the EndpointType. In addition, specifying any https://api.businesscentral.dynamics.com/ will override both EndpointType and UseSandbox as the specified OrganizationUrl supplied will be used without alteration.

Common Service Endpoints

Common Service endpoints contain the business data associated with Microsoft Dynamics 365 Business Central. For instance, accounts, sales orders, and sales invoices will all be found on the Common Service.

Common Service Endpoints - Production

• https://api.businesscentral.dynamics.com/v2.0/{environment name}/api/v2.0
• https://api.businesscentral.dynamics.com/v1.0/api/v2.0
• https://api.businesscentral.dynamics.com/v2.0/sandbox/api/v2.0

Common Service Endpoints - Testing

• https://api.businesscentral.dynamics.com/v2.0/{tenant id}/{environment name}/api/v2.0
• https://api.businesscentral.dynamics.com/v1.0/{tenant id}/api/v2.0
• https://api.businesscentral.dynamics.com/v2.0/{tenant id}/sandbox/api/v2.0

Note: The Tenant Id is the id of your tenant in Azure that the Business Central app is available on. This may be obtained by logging into portal.azure.com, selecting your user name, and going to Switch Directory. It may also be found under portal.azure.com, going to Active Directory, and Overview. Environment names may be found under https://businesscentral.dynamics.com/{tenant id}/admin.

Web Services Endpoints

Web Service endpoints include anything that has been custom exposed in Microsoft Dynamics 365 Business Central. This may include KPIs, reports, individual entites, or other feeds. In Microsoft Dynamics 365 Business Central, click the magnifying glass and search for Web Services to get an idea of what all are exposed by these endpoints.

Web Services Endpoint - Production

• https://api.businesscentral.dynamics.com/v1.0/ODataV4/

Web Services Endpoint - Testing

• https://api.businesscentral.dynamics.com/v1.0/{tenant id}/ODataV4/

Custom API Endpoints

To connect to custom API endpoints you need to set the OrganizationUrl property to your custom base url which is in the format: https://api.businesscentral.dynamics.com/v2.0/{tenant}/api/{API publisher}/{API group}/{API version}

Endpoint Default Changes from 2019

In the 2019 version, the default endpoint resolved to was the Web Services testing endpoint if a https://businesscentral.dynamics.com/ url was supplied to OrganizationUrl. The new default is the Common Service production endpoint. To continue using the Web Services testing endpoint, either set it directly or change the EndpointType to WSTesting.

Kerberos

To authenticate to Microsoft Dynamics 365 Business Central with Kerberos, set AuthScheme to NEGOTIATE.

Authenticating to Microsoft Dynamics 365 Business Central via Kerberos requires you to define authentication properties and to choose how Kerberos should retrieve authentication tickets.

Retrieve Kerberos Tickets

Kerberos tickets are used to authenticate the requester's identity. The use of tickets instead of formal logins/passwords eliminates the need to store passwords locally or send them over a network. Users are reauthenticated (tickets are refreshed) whenever they log in at their local computer or enter kinit USER at the command prompt.

The Cloud provides three ways to retrieve the required Kerberos ticket, depending on whether or not the KRB5CCNAME and/or KerberosKeytabFile variables exist in your environment.

MIT Kerberos Credential Cache File

This option enables you to use the MIT Kerberos Ticket Manager or kinit command to get tickets. With this option there is no need to set the User or Password connection properties.

This option requires that KRB5CCNAME has been created in your system.

To enable ticket retrieval via MIT Cerberos Credential Cache Files:

1. Ensure that the KRB5CCNAME variable is present in your environment.
2. Set KRB5CCNAME to a path that points to your credential cache file. (For example, C:\krb_cache\krb5cc_0 or /tmp/krb5cc_0.) The credential cache file is created when you use the MIT Kerberos Ticket Manager to generate your ticket.
3. To obtain a ticket:
   1. Open the MIT Kerberos Ticket Manager application.
   2. Click Get Ticket.
   3. Enter your principal name and password.
   4. Click OK.

   If the ticket is successfully obtained, the ticket information appears in Kerberos Ticket Manager and is stored in the credential cache file.

The Cloud uses the cache file to obtain the Kerberos ticket to connect to Microsoft Dynamics 365 Business Central.

Note: If you would prefer not to edit KRB5CCNAME, you can use the KerberosTicketCache property to set the file path manually. After this is set, the Cloud uses the specified cache file to obtain the Kerberos ticket to connect to Microsoft Dynamics 365 Business Central.

Keytab File

If your environment lacks the KRB5CCNAME environment variable, you can retrieve a Kerberos ticket using a Keytab File.

To use this method, set the User property to the desired username, and set the KerberosKeytabFile property to a file path pointing to the keytab file associated with the user.

User and Password

If your environment lacks the KRB5CCNAME environment variable and the KerberosKeytabFile property has not been set, you can retrieve a ticket using a user and password combination.

To use this method, set the User and Password properties to the user/password combination that you use to authenticate with Microsoft Dynamics 365 Business Central.

Enabling Cross-Realm Authentication

More complex Kerberos environments can require cross-realm authentication where multiple realms and KDC servers are used. For example, they might use one realm/KDC for user authentication, and another realm/KDC for obtaining the service ticket.

To enable this kind of cross-realm authentication, set the KerberosRealm and KerberosKDC properties to the values required for user authentication. Also, set the KerberosServiceRealm and KerberosServiceKDC properties to the values required to obtain the service ticket.

Customizing the SSL Configuration

By default, the Cloud attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Connecting Through a Firewall or Proxy

HTTP Proxies

To connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false.

In addition, to authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.

Other Proxies

Set the following properties:

• To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
• To tunnel the connection, set FirewallType to TUNNEL.
• To authenticate, specify FirewallUser and FirewallPassword.
• To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

The CData Cloud models Microsoft Dynamics 365 Business Central entities in relational Tables, Views, and Stored Procedures. The table definitions are dynamically obtained from the OData service you connect to. Any changes in the metadata, such as added or removed columns or changes in data type, can be loaded by reconnecting.

Tables

The Cloud models the writable entities described in the service metadata document as bidirectional Tables.

Stored Procedures

Stored Procedures are function-like interfaces to the data source. They can be used to perform additional operations that fall outside of the standard CRUD model.

Miscellaneous information

Be aware that INSERT statements on table SalesInvoiceLines work only when the status of the sales invoice is 'DRAFT'. If the status is 'PAID' or 'OPEN' then the INSERT statement will not complete successfully. This is an API limitation.

Default Table Display

The Microsoft Dynamics 365 Business Central Cloud only exposes tables configured as Web Services in your account. It uses the Odata services of Microsoft Dynamics 365 Business Central to dynamically obtain and display the list of tables after you connect. You can display additional tables; the next section describes the procedure.

Displaying Additional Tables

If you want to display tables (endpoints) other than the ones the Microsoft Dynamics 365 Business Central Cloud has displayed, first configure the tables you want as a Web Service from your account. The Cloud then detects the exposed endpoints and displays them. To do this, follow these steps:

1. Log in to your Dynamics 365 Business Central account.
2. Navige to Web Services and click New.
3. Under Object Type, select Page or Query. The Codeunit option is available in Dynamics 365 Business Central version 16.3 or later.
4. In the Object ID open the dropdown list and click Select from full list.
5. Select the tables you want to display from the Object Name column.
6. Specify the Service Name to define how the service is displayed.
7. Select the Published check box and wait until the OData V4 URL field is populated.
8. Inspect the upper right corner of the page to make sure the changes are saved.
9. Repeat this procedure for any other tables (configured as web services) that you want to add.

Filtering with AL Enum objects

Some Business Central API pages, including most API v2.0 pages, have fields based on AL Enum objects.

Fields based on AL enum objects must have names that are consistent and always the same so that filters on the report always work no matter the language or OS you are using. For this reason, the fields based on AL enums are not translated and Buisness Central API requires them to be encoded to avoid any special character.

To be able to filter with an Enum value, you need to encode the value by refering to https://www.w3.org/Style/XSL/TestSuite/results/4/XEP/symbol.pdf documentation and appending _(underscore) before and after the symbol.

In particual, for space this would be "_x0020_". e.g: SELECT * FROM DimensionSetLines WHERE parentid={id} and parentType='Sales_x0020_Order'

To check if the field is an enum object you can refer to the IsEnumeration column of the sys_tablecolumns table.

Stored procedures are function-like interfaces that extend the functionality of the Cloud beyond simple SELECT/INSERT/UPDATE/DELETE operations with Microsoft Dynamics 365 Business Central.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from Microsoft Dynamics 365 Business Central, along with an indication of whether the procedure succeeded or failed.

CData Cloud - Microsoft Dynamics 365 Business Central Stored Procedures

Name	Description
GetAdminConsentURL	Gets the admin consent URL that must be opened separately by an admin of a given domain to grant access to your application. Only needed when using custom OAuth credentials.

Gets the admin consent URL that must be opened separately by an admin of a given domain to grant access to your application. Only needed when using custom OAuth credentials.

Input

Name	Type	Description
CallbackUrl	String	The URL the user will be redirected to after authorizing your application. This value must match the Reply URL in the Azure AD app settings.
State	String	The same value for state that you sent when you requested the authorization code.

Result Set Columns

Name	Type	Description
URL	String	The authorization URL, entered into a Web browser to obtain the verifier token and authorize your app.

You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.

Schema Tables

The following tables return database metadata for Microsoft Dynamics 365 Business Central:

• sys_catalogs: Lists the available databases.
• sys_schemas: Lists the available schemas.
• sys_tables: Lists the available tables and views.
• sys_tablecolumns: Describes the columns of the available tables and views.
• sys_procedures: Describes the available stored procedures.
• sys_procedureparameters: Describes stored procedure parameters.
• sys_keycolumns: Describes the primary and foreign keys.
• sys_indexes: Describes the available indexes.

Data Source Tables

The following tables return information about how to connect to and query the data source:

• sys_connection_props: Returns information on the available connection properties.
• sys_sqlinfo: Describes the SELECT queries that the Cloud can offload to the data source.

Query Information Tables

The following table returns query statistics for data modification queries, including batch operations::

• sys_identity: Returns information about batch operations or single updates.

Lists the available databases.

The following query retrieves all databases determined by the connection string:

SELECT * FROM sys_catalogs

Columns

Name	Type	Description
CatalogName	String	The database name.

Lists the available schemas.

The following query retrieves all available schemas:

          SELECT * FROM sys_schemas
          

Columns

Name	Type	Description
CatalogName	String	The database name.
SchemaName	String	The schema name.

Lists the available tables.

The following query retrieves the available tables and views:

          SELECT * FROM sys_tables
          

Columns

Name	Type	Description
CatalogName	String	The database containing the table or view.
SchemaName	String	The schema containing the table or view.
TableName	String	The name of the table or view.
TableType	String	The table type (table or view).
Description	String	A description of the table or view.
IsUpdateable	Boolean	Whether the table can be updated.

Describes the columns of the available tables and views.

The following query returns the columns and data types for the Accounts table:

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Accounts' 

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the table or view.
SchemaName	String	The schema containing the table or view.
TableName	String	The name of the table or view containing the column.
ColumnName	String	The column name.
DataTypeName	String	The data type name.
DataType	Int32	An integer indicating the data type. This value is determined at run time based on the environment.
Length	Int32	The storage size of the column.
DisplaySize	Int32	The designated column's normal maximum width in characters.
NumericPrecision	Int32	The maximum number of digits in numeric data. The column length in characters for character and date-time data.
NumericScale	Int32	The column scale or number of digits to the right of the decimal point.
IsNullable	Boolean	Whether the column can contain null.
Description	String	A brief description of the column.
Ordinal	Int32	The sequence number of the column.
IsAutoIncrement	String	Whether the column value is assigned in fixed increments.
IsGeneratedColumn	String	Whether the column is generated.
IsHidden	Boolean	Whether the column is hidden.
IsArray	Boolean	Whether the column is an array.
IsReadOnly	Boolean	Whether the column is read-only.
IsKey	Boolean	Indicates whether a field returned from sys_tablecolumns is the primary key of the table.

Lists the available stored procedures.

The following query retrieves the available stored procedures:

          SELECT * FROM sys_procedures
          

Columns

Name	Type	Description
CatalogName	String	The database containing the stored procedure.
SchemaName	String	The schema containing the stored procedure.
ProcedureName	String	The name of the stored procedure.
Description	String	A description of the stored procedure.
ProcedureType	String	The type of the procedure, such as PROCEDURE or FUNCTION.

Describes stored procedure parameters.

The following query returns information about all of the input parameters for the GetOAuthAccessToken stored procedure:

SELECT * FROM sys_procedureparameters WHERE ProcedureName='GetOAuthAccessToken' AND Direction=1 OR Direction=2

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the stored procedure.
SchemaName	String	The name of the schema containing the stored procedure.
ProcedureName	String	The name of the stored procedure containing the parameter.
ColumnName	String	The name of the stored procedure parameter.
Direction	Int32	An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters.
DataTypeName	String	The name of the data type.
DataType	Int32	An integer indicating the data type. This value is determined at run time based on the environment.
Length	Int32	The number of characters allowed for character data. The number of digits allowed for numeric data.
NumericPrecision	Int32	The maximum precision for numeric data. The column length in characters for character and date-time data.
NumericScale	Int32	The number of digits to the right of the decimal point in numeric data.
IsNullable	Boolean	Whether the parameter can contain null.
IsRequired	Boolean	Whether the parameter is required for execution of the procedure.
IsArray	Boolean	Whether the parameter is an array.
Description	String	The description of the parameter.
Ordinal	Int32	The index of the parameter.

Describes the primary and foreign keys.

The following query retrieves the primary key for the Accounts table:

         SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Accounts' 
          

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the key.
SchemaName	String	The name of the schema containing the key.
TableName	String	The name of the table containing the key.
ColumnName	String	The name of the key column.
IsKey	Boolean	Whether the column is a primary key in the table referenced in the TableName field.
IsForeignKey	Boolean	Whether the column is a foreign key referenced in the TableName field.
PrimaryKeyName	String	The name of the primary key.
ForeignKeyName	String	The name of the foreign key.
ReferencedCatalogName	String	The database containing the primary key.
ReferencedSchemaName	String	The schema containing the primary key.
ReferencedTableName	String	The table containing the primary key.
ReferencedColumnName	String	The column name of the primary key.

Describes the foreign keys.

The following query retrieves all foreign keys which refer to other tables:

         SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'
          

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the key.
SchemaName	String	The name of the schema containing the key.
TableName	String	The name of the table containing the key.
ColumnName	String	The name of the key column.
PrimaryKeyName	String	The name of the primary key.
ForeignKeyName	String	The name of the foreign key.
ReferencedCatalogName	String	The database containing the primary key.
ReferencedSchemaName	String	The schema containing the primary key.
ReferencedTableName	String	The table containing the primary key.
ReferencedColumnName	String	The column name of the primary key.
ForeignKeyType	String	Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key.

Describes the primary keys.

The following query retrieves the primary keys from all tables and views:

         SELECT * FROM sys_primarykeys
          

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the key.
SchemaName	String	The name of the schema containing the key.
TableName	String	The name of the table containing the key.
ColumnName	String	The name of the key column.
KeySeq	String	The sequence number of the primary key.
KeyName	String	The name of the primary key.

Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.

The following query retrieves all indexes that are not primary keys:

          SELECT * FROM sys_indexes WHERE IsPrimary='false'
          

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the index.
SchemaName	String	The name of the schema containing the index.
TableName	String	The name of the table containing the index.
IndexName	String	The index name.
ColumnName	String	The name of the column associated with the index.
IsUnique	Boolean	True if the index is unique. False otherwise.
IsPrimary	Boolean	True if the index is a primary key. False otherwise.
Type	Int16	An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3).
SortOrder	String	The sort order: A for ascending or D for descending.
OrdinalPosition	Int16	The sequence number of the column in the index.

Returns information on the available connection properties and those set in the connection string.

When querying this table, the config connection string should be used:

jdbc:cdata:d365businesscentral:config:

This connection string enables you to query this table without a valid connection.

The following query retrieves all connection properties that have been set in the connection string or set through a default value:

SELECT * FROM sys_connection_props WHERE Value <> ''

Columns

Name	Type	Description
Name	String	The name of the connection property.
ShortDescription	String	A brief description.
Type	String	The data type of the connection property.
Default	String	The default value if one is not explicitly set.
Values	String	A comma-separated list of possible values. A validation error is thrown if another value is specified.
Value	String	The value you set or a preconfigured default.
Required	Boolean	Whether the property is required to connect.
Category	String	The category of the connection property.
IsSessionProperty	String	Whether the property is a session property, used to save information about the current connection.
Sensitivity	String	The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms.
PropertyName	String	A camel-cased truncated form of the connection property name.
Ordinal	Int32	The index of the parameter.
CatOrdinal	Int32	The index of the parameter category.
Hierarchy	String	Shows dependent properties associated that need to be set alongside this one.
Visible	Boolean	Informs whether the property is visible in the connection UI.
ETC	String	Various miscellaneous information about the property.

Describes the SELECT query processing that the Cloud can offload to the data source.

See SQL Compliance for SQL syntax details.

Discovering the Data Source's SELECT Capabilities

Below is an example data set of SQL capabilities. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.

Name	Description	Possible Values
AGGREGATE_FUNCTIONS	Supported aggregation functions.	AVG, COUNT, MAX, MIN, SUM, DISTINCT
COUNT	Whether COUNT function is supported.	YES, NO
IDENTIFIER_QUOTE_OPEN_CHAR	The opening character used to escape an identifier.	[
IDENTIFIER_QUOTE_CLOSE_CHAR	The closing character used to escape an identifier.	]
SUPPORTED_OPERATORS	A list of supported SQL operators.	=, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR
GROUP_BY	Whether GROUP BY is supported, and, if so, the degree of support.	NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE
OJ_CAPABILITIES	The supported varieties of outer joins supported.	NO, LEFT, RIGHT, FULL, INNER, NOT_ORDERED, ALL_COMPARISON_OPS
OUTER_JOINS	Whether outer joins are supported.	YES, NO
SUBQUERIES	Whether subqueries are supported, and, if so, the degree of support.	NO, COMPARISON, EXISTS, IN, CORRELATED_SUBQUERIES, QUANTIFIED
STRING_FUNCTIONS	Supported string functions.	LENGTH, CHAR, LOCATE, REPLACE, SUBSTRING, RTRIM, LTRIM, RIGHT, LEFT, UCASE, SPACE, SOUNDEX, LCASE, CONCAT, ASCII, REPEAT, OCTET, BIT, POSITION, INSERT, TRIM, UPPER, REGEXP, LOWER, DIFFERENCE, CHARACTER, SUBSTR, STR, REVERSE, PLAN, UUIDTOSTR, TRANSLATE, TRAILING, TO, STUFF, STRTOUUID, STRING, SPLIT, SORTKEY, SIMILAR, REPLICATE, PATINDEX, LPAD, LEN, LEADING, KEY, INSTR, INSERTSTR, HTML, GRAPHICAL, CONVERT, COLLATION, CHARINDEX, BYTE
NUMERIC_FUNCTIONS	Supported numeric functions.	ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT, EXP, FLOOR, LOG, MOD, SIGN, SIN, SQRT, TAN, PI, RAND, DEGREES, LOG10, POWER, RADIANS, ROUND, TRUNCATE
TIMEDATE_FUNCTIONS	Supported date/time functions.	NOW, CURDATE, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, MONTH, QUARTER, WEEK, YEAR, CURTIME, HOUR, MINUTE, SECOND, TIMESTAMPADD, TIMESTAMPDIFF, DAYNAME, MONTHNAME, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT
REPLICATION_SKIP_TABLES	Indicates tables skipped during replication.
REPLICATION_TIMECHECK_COLUMNS	A string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication.
IDENTIFIER_PATTERN	String value indicating what string is valid for an identifier.
SUPPORT_TRANSACTION	Indicates if the provider supports transactions such as commit and rollback.	YES, NO
DIALECT	Indicates the SQL dialect to use.
KEY_PROPERTIES	Indicates the properties which identify the uniform database.
SUPPORTS_MULTIPLE_SCHEMAS	Indicates if multiple schemas may exist for the provider.	YES, NO
SUPPORTS_MULTIPLE_CATALOGS	Indicates if multiple catalogs may exist for the provider.	YES, NO
DATASYNCVERSION	The CData Data Sync version needed to access this driver.	Standard, Starter, Professional, Enterprise
DATASYNCCATEGORY	The CData Data Sync category of this driver.	Source, Destination, Cloud Destination
SUPPORTSENHANCEDSQL	Whether enhanced SQL functionality beyond what is offered by the API is supported.	TRUE, FALSE
SUPPORTS_BATCH_OPERATIONS	Whether batch operations are supported.	YES, NO
SQL_CAP	All supported SQL capabilities for this driver.	SELECT, INSERT, DELETE, UPDATE, TRANSACTIONS, ORDERBY, OAUTH, ASSIGNEDID, LIMIT, LIKE, BULKINSERT, COUNT, BULKDELETE, BULKUPDATE, GROUPBY, HAVING, AGGS, OFFSET, REPLICATE, COUNTDISTINCT, JOINS, DROP, CREATE, DISTINCT, INNERJOINS, SUBQUERIES, ALTER, MULTIPLESCHEMAS, GROUPBYNORELATION, OUTERJOINS, UNIONALL, UNION, UPSERT, GETDELETED, CROSSJOINS, GROUPBYCOLLATE, MULTIPLECATS, FULLOUTERJOIN, MERGE, JSONEXTRACT, BULKUPSERT, SUM, SUBQUERIESFULL, MIN, MAX, JOINSFULL, XMLEXTRACT, AVG, MULTISTATEMENTS, FOREIGNKEYS, CASE, LEFTJOINS, COMMAJOINS, WITH, LITERALS, RENAME, NESTEDTABLES, EXECUTE, BATCH, BASIC, INDEX
PREFERRED_CACHE_OPTIONS	A string value specifies the preferred cacheOptions.
ENABLE_EF_ADVANCED_QUERY	Indicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side.	YES, NO
PSEUDO_COLUMNS	A string array indicating the available pseudo columns.
MERGE_ALWAYS	If the value is true, The Merge Mode is forcibly executed in Data Sync.	TRUE, FALSE
REPLICATION_MIN_DATE_QUERY	A select query to return the replicate start datetime.
REPLICATION_MIN_FUNCTION	Allows a provider to specify the formula name to use for executing a server side min.
REPLICATION_START_DATE	Allows a provider to specify a replicate startdate.
REPLICATION_MAX_DATE_QUERY	A select query to return the replicate end datetime.
REPLICATION_MAX_FUNCTION	Allows a provider to specify the formula name to use for executing a server side max.
IGNORE_INTERVALS_ON_INITIAL_REPLICATE	A list of tables which will skip dividing the replicate into chunks on the initial replicate.
CHECKCACHE_USE_PARENTID	Indicates whether the CheckCache statement should be done against the parent key column.	TRUE, FALSE
CREATE_SCHEMA_PROCEDURES	Indicates stored procedures that can be used for generating schema files.

The following query retrieves the operators that can be used in the WHERE clause:

SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'

Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the Data Model section for more information.

Columns

Name	Type	Description
NAME	String	A component of SQL syntax, or a capability that can be processed on the server.
VALUE	String	Detail on the supported SQL or SQL syntax.

Returns information about attempted modifications.

The following query retrieves the Ids of the modified rows in a batch operation:

         SELECT * FROM sys_identity
          

Columns

Name	Type	Description
Id	String	The database-generated Id returned from a data modification operation.
Batch	String	An identifier for the batch. 1 for a single operation.
Operation	String	The result of the operation in the batch: INSERTED, UPDATED, or DELETED.
Message	String	SUCCESS or an error message if the update in the batch failed.

Data Type Mappings

The Cloud maps types from the data source to the corresponding data type available in the schema. The table below documents these mappings.

Microsoft Dynamics 365 Business Central (OData V4)	CData Schema
Edm.Binary	binary
Edm.Boolean	bool
Edm.Date	datetime
Edm.DateTimeOffset	datetime
Edm.Decimal	decimal
Edm.Double	double
Edm.Guid	guid
Edm.Int32	int
Edm.String	string
Edm.TimeOfDay	time

The connection string properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure in the connection string for this provider. Click the links for further details.

For more information on establishing a connection, see Establishing a Connection.

Authentication

---

Property	Description
AuthScheme	Whether to use OAuth Authentication or Access Key Authentication when connecting to Business Central.
OrganizationURL	The URL to your Dynamics 365 organization. For instance, https://businesscentral.dynamics.com/abc123/.
User	The username of the Dynamics 365 OnPremise account used to authenticate to the Microsoft Dynamics 365 Business Central server.
Password	The password used to authenticate the user.
AccessKey	The access key used to authenticate to the Microsoft Dynamics 365 Business Central.
Company	The name of the Microsoft Dynamics 365 Business Central company.
UseSandbox	A boolean indicating if a sandbox is being used. Set this to true if you have a sandbox name other than simply 'sandbox'.

Azure Authentication

---

Property	Description
AzureTenant	The Microsoft Online tenant being used to access data. If not specified, your default tenant is used.
AzureEnvironment	The Azure Environment to use when establishing a connection.

OAuth

---

Property	Description
OAuthClientId	The client Id assigned when you register your application with an OAuth authorization server.
OAuthClientSecret	The client secret assigned when you register your application with an OAuth authorization server.

JWT OAuth

---

Property	Description
OAuthJWTCert	The JWT Certificate store.
OAuthJWTCertType	The type of key store containing the JWT Certificate.
OAuthJWTCertPassword	The password for the OAuth JWT certificate.
OAuthJWTCertSubject	The subject of the OAuth JWT certificate.

SSL

---

Property	Description
SSLServerCert	The certificate to be accepted from the server when connecting using TLS/SSL.

Logging

---

Property	Description
Verbosity	The verbosity level that determines the amount of detail included in the log file.

Schema

---

Property	Description
BrowsableSchemas	This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Miscellaneous

---

Property	Description
EndpointType	The type of endpoint the OrganizationUrl should resolve to.
IncludeNavigationProperties	A boolean indicating if navigation properties should be included in the column listing.
MaxRows	Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
PseudoColumns	This property indicates whether or not to include pseudo columns as columns to the table.
Timeout	The value in seconds until the timeout error is thrown, canceling the operation.
UseSimpleNames	Boolean determining if simple names should be used for the schema, table and column names.

This section provides a complete list of the Authentication properties you can configure in the connection string for this provider.

---

Property	Description
AuthScheme	Whether to use OAuth Authentication or Access Key Authentication when connecting to Business Central.
OrganizationURL	The URL to your Dynamics 365 organization. For instance, https://businesscentral.dynamics.com/abc123/.
User	The username of the Dynamics 365 OnPremise account used to authenticate to the Microsoft Dynamics 365 Business Central server.
Password	The password used to authenticate the user.
AccessKey	The access key used to authenticate to the Microsoft Dynamics 365 Business Central.
Company	The name of the Microsoft Dynamics 365 Business Central company.
UseSandbox	A boolean indicating if a sandbox is being used. Set this to true if you have a sandbox name other than simply 'sandbox'.

Whether to use OAuth Authentication or Access Key Authentication when connecting to Business Central.

Possible Values

Negotiate, AzureAD, AzureServicePrincipal, AzureServicePrincipalCert, NTLM

Data Type

string

Default Value

"Auto"

Remarks

Whether to use OAuth Authentication or Access Key Authentication when connecting to Business Central.

Note: OAuth Authentication is for cloud version only, but is expected to be supported for OnPrem in August 2021.

• Auto: Automatically try to determine an appropriate auth scheme to use based on other connection properties.
• AccessKey: Set this to perform authenticating using an Access Key. Only works on endpoints designed for testing the service.
• AzureAD: Set this to perform Azure Active Directory OAuth authentication.
• AzureServicePrincipal: Set this to authenticate as an Azure Service Principal using a Client Secret.
• AzureServicePrincipalCert: Set this to authenticate as an Azure Service Principal using a Certificate.
• AzureMSI: Set this to automatically obtain Managed Service Identity credentials when running on an Azure VM.
• NTLM: Set this to use your Windows credentials for authentication.
• Negotiate: If AuthScheme is set to NEGOTIATE, the Cloud will negotiate an authentication mechanism with the server. Set AuthScheme to NEGOTIATE if you want to use Kerberos authentication.

The URL to your Dynamics 365 organization. For instance, https://businesscentral.dynamics.com/abc123/.

Data Type

string

Default Value

""

Remarks

The URL you use to log into your Dynamics 365 organization; sometimes also called the resource. For instance, https://businesscentral.dynamics.com/abc123/.

To find the correct URL for your Microsoft Dynamics 365 Business Central instance, This may also be known as the resource. To find this click the Search icon and enter "Web Services". Monitor the OData v4 URL. The end value should be the web service endpoint (minus the Company).

For example if the OData v4 URL is https://api.businesscentral.dynamics.com/v1.0/businesscentralid/ODataV4/, the web service endpoint correct URL is https://api.businesscentral.dynamics.com/v1.0.

To connect to custom APIs set this property to the base URL of your Dynamics 365 custom API. For instance, https://api.businesscentral.dynamics.com/v2.0/{environmentName}/api/{API publisher}/{API group}/{API version} or https://api.businesscentral.dynamics.com/v2.0/{tenant}/{environmentName}/api/{API publisher}/{API group}/{API version}.

For more details on how to specify the OrganizationUrl and which endpoints are available, see Business Central Endpoints.

The username of the Dynamics 365 OnPremise account used to authenticate to the Microsoft Dynamics 365 Business Central server.

Data Type

string

Default Value

""

Remarks

Together with AccessKey, this field is used to authenticate to the Microsoft Dynamics 365 Business Central server.

Note: Basic authentication will not be supported for cloud version starting in Apr 2021.For on-premises, Web Service Access Key (Basic Auth) will remain an option for the time being.

The password used to authenticate the user.

Data Type

string

Default Value

""

Remarks

The User and Password are together used to authenticate with the server.

The access key used to authenticate to the Microsoft Dynamics 365 Business Central.

Data Type

string

Default Value

""

Remarks

Together with User, this field is used to authenticate against the Microsoft Dynamics 365 Business Central.

Note: Basic authentication will not be supported for cloud version starting in Apr 2021.For on-premises, Web Service Access Key (Basic Auth) will remain an option for the time being.

The name of the Microsoft Dynamics 365 Business Central company.

Data Type

string

Default Value

""

Remarks

This can be found in the Company Information page.

Leave this empty to retrieve all companies as separate schemas.

A boolean indicating if a sandbox is being used. Set this to true if you have a sandbox name other than simply 'sandbox'.

Data Type

bool

Default Value

false

Remarks

A boolean indicating if a sandbox is being used. Set this to true if you have a sandbox name other than simply 'sandbox'.

This section provides a complete list of the Azure Authentication properties you can configure in the connection string for this provider.

---

Property	Description
AzureTenant	The Microsoft Online tenant being used to access data. If not specified, your default tenant is used.
AzureEnvironment	The Azure Environment to use when establishing a connection.

The Microsoft Online tenant being used to access data. If not specified, your default tenant is used.

Data Type

string

Default Value

""

Remarks

The Microsoft Online tenant being used to access data. For instance, contoso.onmicrosoft.com. Alternatively, specify the tenant Id. This value is the directory Id in the Azure Portal > Azure Active Directory > Properties.

Typically it is not necessary to specify the Tenant. This can be automatically determined by Microsoft when using the OAuthGrantType set to CODE (default). However, it may fail in the case that the user belongs to multiple tenants. For instance, if an Admin of domain A invites a user of domain B to be a guest user. The user will now belong to both tenants. It is a good practice to specify the Tenant, although in general things should normally work without having to specify it.

The AzureTenant is required when setting OAuthGrantType to CLIENT. When using client credentials, there is no user context. The credentials are taken from the context of the app itself. While Microsoft still allows client credentials to be obtained without specifying which Tenant, it has a much lower probability of picking the specific tenant you want to work with. For this reason, we require AzureTenant to be explicitly stated for all client credentials connections to ensure you get credentials that are applicable for the domain you intend to connect to.

The Azure Environment to use when establishing a connection.

Possible Values

GLOBAL, CHINA, USGOVT, USGOVTDOD

Data Type

string

Default Value

"GLOBAL"

Remarks

In most cases, leaving the environment set to global will work. However, if your Azure Account has been added to a different environment, the AzureEnvironment may be used to specify which environment. The available values are GLOBAL, CHINA, USGOVT, USGOVTDOD.

This section provides a complete list of the OAuth properties you can configure in the connection string for this provider.

---

Property	Description
OAuthClientId	The client Id assigned when you register your application with an OAuth authorization server.
OAuthClientSecret	The client secret assigned when you register your application with an OAuth authorization server.

The client Id assigned when you register your application with an OAuth authorization server.

Data Type

string

Default Value

""

Remarks

As part of registering an OAuth application, you will receive the OAuthClientId value, sometimes also called a consumer key, and a client secret, the OAuthClientSecret.

The client secret assigned when you register your application with an OAuth authorization server.

Data Type

string

Default Value

""

Remarks

As part of registering an OAuth application, you will receive the OAuthClientId, also called a consumer key. You will also receive a client secret, also called a consumer secret. Set the client secret in the OAuthClientSecret property.

This section provides a complete list of the JWT OAuth properties you can configure in the connection string for this provider.

---

Property	Description
OAuthJWTCert	The JWT Certificate store.
OAuthJWTCertType	The type of key store containing the JWT Certificate.
OAuthJWTCertPassword	The password for the OAuth JWT certificate.
OAuthJWTCertSubject	The subject of the OAuth JWT certificate.

The JWT Certificate store.

Data Type

string

Default Value

""

Remarks

The name of the certificate store for the client certificate.

The OAuthJWTCertType field specifies the type of the certificate store specified by OAuthJWTCert. If the store is password protected, specify the password in OAuthJWTCertPassword.

OAuthJWTCert is used in conjunction with the OAuthJWTCertSubject field in order to specify client certificates. If OAuthJWTCert has a value, and OAuthJWTCertSubject is set, a search for a certificate is initiated. Please refer to the OAuthJWTCertSubject field for details.

Designations of certificate stores are platform-dependent.

The following are designations of the most common User and Machine certificate stores in Windows:

MY	A certificate store holding personal certificates with their associated private keys.
CA	Certifying authority certificates.
ROOT	Root certificates.
SPC	Software publisher certificates.

In Java, the certificate store normally is a file containing certificates and optional private keys.

When the certificate store type is PFXFile, this property must be set to the name of the file. When the type is PFXBlob, the property must be set to the binary contents of a PFX file (i.e. PKCS12 certificate store).

The type of key store containing the JWT Certificate.

Data Type

string

Default Value

"PEMKEY_BLOB"

Remarks

This property can take one of the following values:

USER	For Windows, this specifies that the certificate store is a certificate store owned by the current user. Note: This store type is not available in Java.
MACHINE	For Windows, this specifies that the certificate store is a machine store. Note: this store type is not available in Java.
PFXFILE	The certificate store is the name of a PFX (PKCS12) file containing certificates.
PFXBLOB	The certificate store is a string (base-64-encoded) representing a certificate store in PFX (PKCS12) format.
JKSFILE	The certificate store is the name of a Java key store (JKS) file containing certificates. Note: this store type is only available in Java.
JKSBLOB	The certificate store is a string (base-64-encoded) representing a certificate store in Java key store (JKS) format. Note: this store type is only available in Java.
PEMKEY_FILE	The certificate store is the name of a PEM-encoded file that contains a private key and an optional certificate.
PEMKEY_BLOB	The certificate store is a string (base64-encoded) that contains a private key and an optional certificate.
PUBLIC_KEY_FILE	The certificate store is the name of a file that contains a PEM- or DER-encoded public key certificate.
PUBLIC_KEY_BLOB	The certificate store is a string (base-64-encoded) that contains a PEM- or DER-encoded public key certificate.
SSHPUBLIC_KEY_FILE	The certificate store is the name of a file that contains an SSH-style public key.
SSHPUBLIC_KEY_BLOB	The certificate store is a string (base-64-encoded) that contains an SSH-style public key.
P7BFILE	The certificate store is the name of a PKCS7 file containing certificates.
PPKFILE	The certificate store is the name of a file that contains a PPK (PuTTY Private Key).
XMLFILE	The certificate store is the name of a file that contains a certificate in XML format.
XMLBLOB	The certificate store is a string that contains a certificate in XML format.

The password for the OAuth JWT certificate.

Data Type

string

Default Value

""

Remarks

If the certificate store is of a type that requires a password, this property is used to specify that password in order to open the certificate store.

The subject of the OAuth JWT certificate.

Data Type

string

Default Value

"*"

Remarks

When loading a certificate the subject is used to locate the certificate in the store.

If an exact match is not found, the store is searched for subjects containing the value of the property.

If a match is still not found, the property is set to an empty string, and no certificate is selected.

The special value "*" picks the first certificate in the certificate store.

The certificate subject is a comma separated list of distinguished name fields and values. For instance "CN=www.server.com, OU=test, C=US, [email protected]". Common fields and their meanings are displayed below.

Field	Meaning
CN	Common Name. This is commonly a host name like www.server.com.
O	Organization
OU	Organizational Unit
L	Locality
S	State
C	Country
E	Email Address

If a field value contains a comma it must be quoted.

This section provides a complete list of the SSL properties you can configure in the connection string for this provider.

---

Property	Description
SSLServerCert	The certificate to be accepted from the server when connecting using TLS/SSL.

The certificate to be accepted from the server when connecting using TLS/SSL.

Data Type

string

Default Value

""

Remarks

If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.

This property can take the following forms:

Description	Example
A full PEM Certificate (example shortened for brevity)	-----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE-----
A path to a local file containing the certificate	C:\cert.cer
The public key (example shortened for brevity)	-----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY-----
The MD5 Thumbprint (hex values can also be either space or colon separated)	ecadbdda5a1529c58a1e9e09828d70e4
The SHA1 Thumbprint (hex values can also be either space or colon separated)	34a929226ae0819f2ec14b4a3d904f801cbb150d

If not specified, any certificate trusted by the machine is accepted.

Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.

This section provides a complete list of the Logging properties you can configure in the connection string for this provider.

---

Property	Description
Verbosity	The verbosity level that determines the amount of detail included in the log file.

The verbosity level that determines the amount of detail included in the log file.

Data Type

string

Default Value

"1"

Remarks

The verbosity level determines the amount of detail that the Cloud reports to the Logfile. Verbosity levels from 1 to 5 are supported. These are detailed in the Logging page.

This section provides a complete list of the Schema properties you can configure in the connection string for this provider.

---

Property	Description
BrowsableSchemas	This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Data Type

string

Default Value

""

Remarks

Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.

This section provides a complete list of the Miscellaneous properties you can configure in the connection string for this provider.

---

Property	Description
EndpointType	The type of endpoint the OrganizationUrl should resolve to.
IncludeNavigationProperties	A boolean indicating if navigation properties should be included in the column listing.
MaxRows	Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
PseudoColumns	This property indicates whether or not to include pseudo columns as columns to the table.
Timeout	The value in seconds until the timeout error is thrown, canceling the operation.
UseSimpleNames	Boolean determining if simple names should be used for the schema, table and column names.

The type of endpoint the OrganizationUrl should resolve to.

Possible Values

CSProduction, CSTesting, WSProduction, WSTesting

Data Type

string

Default Value

"CSProduction"

Remarks

Please see Business Central Endpoints for details on the types of endpoints in Microsoft Dynamics 365 Business Central and what the appropriate value to set the EndpointType to based on your use case.

A boolean indicating if navigation properties should be included in the column listing.

Data Type

bool

Default Value

false

Remarks

Navigation properties will not populate with information when retrieving data. Instead, navigation properties may be used to bind to an existing resource during inserts or updates. For example:

INSERT INTO accounts (_primarycontactid_value) VALUES ('123456')

To specify multiple values, use a comma-separated list. For instance:

INSERT INTO accounts (lead_customer_accounts) VALUES ('123,456')

Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

Data Type

int

Default Value

-1

Remarks

Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

This property indicates whether or not to include pseudo columns as columns to the table.

Data Type

string

Default Value

""

Remarks

This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".

The value in seconds until the timeout error is thrown, canceling the operation.

Data Type

int

Default Value

60

Remarks

If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.

If Timeout expires and the operation is not yet complete, the Cloud throws an exception.

Boolean determining if simple names should be used for the schema, table and column names.

Data Type

bool

Default Value

false

Remarks

Microsoft Dynamics 365 Business Central The company names or table/column names can use special characters in names that are normally not allowed in standard databases. UseSimpleNames makes the Cloud easier to use with traditional database tools.

Setting UseSimpleNames to true will simplify the names of schemas, tables and columns returned. It will enforce a naming scheme such that only alphanumeric characters and the underscore are valid for the displayed schema, tables and columns names. Any nonalphanumeric characters will be converted to an underscore. When setting this property to true, please make sure to query sys_schemas to pick up the simplified company name in SchemaName column before restricting your other connections to a single company using the Company connection property.