This repo provides an overview of Enzo Server integration capabilities with Business Central (Microsoft 365) and sample SQL code to read and write data through the Business Central API and ODataV4 endpoint (either provided by Microsoft or exposed by your AL logic).
NOTE: This project is under construction and should be considered in DRAFT mode. Feedback is welcome: info@enzounified.com
Enzo Server provides deep integration capabilities with Business Central's APIs and OData endpoints to enable rapid Business Process Automation and data discovery use cases.
Enzo Server provides direct connectivity to Business Central using native SQL commands to read/write data quickly. Using SQL commands allows integration teams to quickly build otherwise complex business process automation logic and enables data discovery scenarios. SQL Server Management Studio (SSMS) is the recommended tool to execute SQL commands against Enzo by either connecting directly to Enzo or using a Linked Server to Enzo. Since Enzo Server is a SQL Server emulator, you can connect to it directly. Enzo implements a subset of the T-SQL language necessary to access the API of the remote system; complex SQL operations (such as JOIN or GROUP BY) are not supported.
Enzo Server works by installing adapters, which abstract access to specific endpoints; the Business Central adapter is designed to expose the BC API and ODataV4 endpoints as SQL commands, and can be extended and/or customized to your needs and custom BC endpoints. In addition, Enzo hides the low-level OAuth 2.0 authentication complexity, automatically generates and stores Bearer Tokens securely, and refreshes the Bearer Tokens as needed, automatically. You can also create your OAuth token separately.
Enzo Server accepts connections from SSMS directly or through Linked Server. When creating integration scripts, such as jobs using the SQL Server Agent for example, or creating deep integrations within a database (as a Stored Procedure for example), connecting to Enzo through a Linked Server connection is necessary. Most operations can be performed either directly or through a Linked Server connection, but there are differences due to the way Linked Server works.
As a result, the following table outlines which operations are officially supported for the Business Central adapter (note: in some cases, an unsupported operation may work, but it is not guaranteed to work in future releases):
| Operation | Direct | Linked Server |
|---|---|---|
| EXEC | Supported | Supported |
| SELECT | Supported | Supported |
| INSERT | Supported | Not Supported |
| UPDATE | Supported | Not Supported |
| DELETE | Supported | Not Supported |
Setting up a Linked Server to Enzo Server is simple but requires specific settings. Visit the online documentation for more information.
This documentation assumes that the name given to a Linked Server connection is ENZO. In addition, the database name BSC must be added to the call:
-- return all available commands when connected to Enzo directly
EXEC BusinessCentral.help 0
-- return all available commands when connected through a Linked Server connection called ENZO
EXEC ENZO.BSC.BusinessCentral.help 0
In order to run the sample code provided in this document, you will need to have an Enzo Server running on a Virtual Machine (Windows Server or Windows 10 or higher). You can quickly obtain a test Enzo Server Virtual Machine in AWS or Azure by using the Enzo Server 3.1 Marketplace offer. For more information, please visit the Enzo Download page on the Enzo Unified website.
Configuring Enzo to access Business Central 365 requires the creation of an Azure Active Directory (AAD) Enterprise Application along with other important configuration steps in Business Central administrative screens. In addition, Enzo needs to be configured to access Business Central by creating a configuration setting.
The necessary configuration steps are documented in the BusinessCentralConfiguration.pdf document on the Enzo Unified website.
The document provided in the Configuration section provides the SQL command necessary to store the security settings in Enzo.
Enzo uses the following commands to create and update configuration settings respectively:
_configCreate and _configUpdate
Sample SQL command to create a configuration setting in Enzo
For reference, the SQL command to create a new configuration setting is provided below:
EXEC BSC.BusinessCentral._configCreate
'cronus', -- name of the configuration
1, -- make it the default setting
'AAD_APP_ID', -- AppId of AAD Enterprise Application
null,
'AAD_APP_SECRET', -- App Secret of AAD Enterprise Application
-- Business Central API base URL (replace AAD_TENANT_ID with your Azure Tenant ID, which is a GUID value):
'https://api.businesscentral.dynamics.com/v2.0/AAD_TENANT_ID/Production/api/v2.0',
'bearer', -- Indicates Bearer Token Auth is used
0, -- no encoding
0, -- no encoding
-- OAuth Configuration Settings:
'{
"tokenUrl": "https://login.microsoftonline.com/AAD_TENANT_ID/oauth2/v2.0/token",
"callTokenForRefresh": true,
"supportsTokenRefresh": false,
"redirect_uri": "https://localhost",
"scopes": "https://api.businesscentral.dynamics.com/.default",
"token_behavior": {
"authorization": "none",
"method": "POST",
"query_encode": false,
"payload_encode": false,
"query": "",
"grant_type": "client_credentials",
"payload": "grant_type={grant_type}&client_id={client_id}&client_secret={client_secret}&scope=https://api.businesscentral.dynamics.com/.default"
},
"refreshToken_behavior": null
}',
'AAD_TENANT_ID', -- Azure Directory Tenant ID
'CRONUS_COMPANY_ID', -- CRONUS Company ID
'CRONUS USA, Inc.' -- CORNUS Company name
All Business Central API endpoints that allow you to read data are exposed through EXEC and SELECT commands.
To list all the available commands available, you can run the following SQL command on Enzo:
SELECT [procedure], tablename FROM BusinessCentral._handlers WHERE groups='API'
Sample output (10 records)
Here is a sample output of the first 10 records:
| procedure | tableName |
|---|---|
| getAccount | Account |
| getAgedAccountsPayable | AgedAccountsPayable |
| getAgedAccountsReceivable | AgedAccountsReceivable |
| getAttachments | Attachments |
| getAttachmentsForJournalLine | AttachmentsForJournalLine |
| getAttachmentsForJournalLineForJournal | AttachmentsForJournalLineForJournal |
| getBalanceSheet | BalanceSheet |
| getBankAccount | BankAccount |
| getCashFlowStatement | CashFlowStatement |
| getCompany | Company |
The above statement shows you that you can retrieve records from Business Central using either an EXEC command using the produce name, or a SELECT statement on the tablename table. Most tablename tables allow the INSERT, UPDATE and DELETE operations but not all.
You can fetch data from the Business Central API using EXEC commands.
-- retrieve all vendors from Business Central
EXEC BusinessCentral.listVendors 'dc50d5e8-f9c9-ed11-94cc-000d3a220b2f'
The EXEC operator offers a few optional parameters. To view the list of parameters of the EXEC command, run this SQL:
EXEC BusinessCentral.listVendors help
For example, the top parameter allows you to return the first few records:
-- retrieve 10 vendors from Business Central
EXEC BusinessCentral.listVendors 'dc50d5e8-f9c9-ed11-94cc-000d3a220b2f', 10
You can also use the parameter name:
-- retrieve 10 vendors from Business Central
EXEC BusinessCentral.listVendors @company_id='dc50d5e8-f9c9-ed11-94cc-000d3a220b2f', @top=10
To run the same command through a Linked Server connection to Enzo:
EXEC ENZO.BSC.BusinessCentral.listVendors @company_id='dc50d5e8-f9c9-ed11-94cc-000d3a220b2f', @top=10
Generally speaking, using SELECT commands is more flexible than using EXEC operators because you can apply client-side filters on the data, select the desired column names, and perform ORDER BY operations (the ORDER BY is applied client-side). The following SELECT command in SQL Server Management Studio returns all Vendors from Business Central (replace company_id with your Business Central Company ID):
SELECT * FROM BusinessCentral.Vendors WHERE company_id='dc50d5e8-f9c9-ed11-94cc-000d3a220b2f'
To retrieve the first 10 records, use the TOP operator:
SELECT TOP 10 * FROM BusinessCentral.Vendors WHERE company_id='dc50d5e8-f9c9-ed11-94cc-000d3a220b2f'
To apply secondary filters (client-side) the filters must be added after the parameters of the SQL command. For example, the listVendors command (Vendors table) accepts the following parameters, so if you use them they must be listed immediately after the WHERE clause (the company_id is a required parameter):
- company_id
- top
- skip
- limit
- filter
- expand
- select
To return all records that contain an 'n' character in their displayName field, the following command will work because the client-side filter on the displayName field is last in the WHERE clause:
SELECT * FROM BusinessCentral.Vendors WHERE company_Id = 'dc50d5e8-f9c9-ed11-94cc-000d3a220b2f' AND displayName like '%n%'
You can run the same command through a Linked Server connection to Enzo:
SELECT * FROM ENZO.BSC.BusinessCentral.Vendors WHERE company_Id = 'dc50d5e8-f9c9-ed11-94cc-000d3a220b2f' AND displayName like '%n%'
You can perform inserts, updates, and delete operations using either an EXEC command or its equivalent INSERT, UPDATE or DELETE operation.
WARNING: The INSERT, UPDATE and DELETE operations are only supported when connecting directly to ENZO; it is recommended to use the EXEC operations to change data when using a Linked Server connection.
EXEC operations come in two flavors for Business Central: specifying individual parameters or by providing a raw JSON document.
For example, the PostVendor command takes 18 parameters, most of which are optional, such as the vendor number and the displayName. To view the list of parameters available, run this command:
EXEC BusinessCentral.PostVendor help
Here is a sample SQL command to create a new Vendor by passing individual values:
EXEC BusinessCentral.PostVendor 'dc50d5e8-f9c9-ed11-94cc-000d3a220b2f', null, 'V1001', 'test vendor'
The same command can be sent using a Linked Server connection to Enzo:
EXEC ENZO.BSC.BusinessCentral.PostVendor 'dc50d5e8-f9c9-ed11-94cc-000d3a220b2f', null, 'V1001', 'test vendor'
Each Enzo command that allows updating/inserting data also provides a secondary command of the same name, with RAW appended to it. These additional commands allow you to pass a custom JSON payload instead of providing individual parameters.
To view the parameters of the PostVendorRAW command, use this command:
EXEC BusinessCentral.PostVendorRAW help
The PostVendorRAW command takes two parameters: the company_id, and the json payload.
EXEC BusinessCentral.PostVendorRAW 'dc50d5e8-f9c9-ed11-94cc-000d3a220b2f', '{ "number": "V1002", "displayName": "test vendor 2" }'
The same command can be sent using a Linked Server connection to Enzo:
EXEC ENZO.BSC.BusinessCentral.PostVendorRAW 'dc50d5e8-f9c9-ed11-94cc-000d3a220b2f', '{ "number": "V1002", "displayName": "test vendor 2" }'
Most objects in Business Central provide an HTTP PATCH (UPDATE) operation. The table name to use for the command is also provided as part of the help output. For example, the PatchVendor command's help output contains a TableName column that would be used for the UPDATE operation. The table name is vendor.
EXEC BusinessCentral.PatchVendor help
Updating data in Business Central requires a valid etag to avoid concurrency issues. The etag value can be found by calling the vendor record. The following command returns the current etag value for a given vendor_id:
SELECT [@odata.etag] FROM BusinessCentral.Vendor WHERE company_Id = 'dc50d5e8-f9c9-ed11-94cc-000d3a220b2f' AND vendor_id='625C8AFB-52F3-ED11-8848-000D3A373307'
The UPDATE operation on the vendor table requires the company_id, the vendor_id, the etag, and the SET section that updates specific fields:
UPDATE BusinessCentral.Vendor
SET email='test@enzounified.com'
WHERE
company_Id = 'dc50d5e8-f9c9-ed11-94cc-000d3a220b2f'
AND vendor_id='625C8AFB-52F3-ED11-8848-000D3A373307'
AND [@odata.etag]='W/"JzIwOzE3NDc5MTgwODIxNjI2MjY0MzM0MTswMDsn"'
The UPDATE operation may not work as expected using a Linked Server connection; if you are writing SQL code through a Linked Server connection, use the corresponding EXEC operation instead.
Most objects in Business Central provide an HTTP POST (INSERT) operation. The table name to use for the command is also provided as part of the help output. For example, the PatchVendor command's help output contains a TableName column that would be used for the UPDATE operation. The table name is vendor.
EXEC BusinessCentral.PatchVendor help
The INSERT operation on the vendor table requires the company_id and other values as desired:
INSERT INTO BusinessCentral.Vendor (company_id, [number], displayName) VALUES ('dc50d5e8-f9c9-ed11-94cc-000d3a220b2f', 'V1003', 'test vendor 3')
The INSERT operation may not work as expected using a Linked Server connection; if you are writing SQL code through a Linked Server connection, use the corresponding EXEC operation instead.
Most objects in Business Central provide an HTTP DELETE (DELETE) operation. The table name to use for the command is also provided as part of the help output. For example, the PatchVendor command's help output contains a TableName column that would be used for the UPDATE operation. The table name is vendor.
EXEC BusinessCentral.PatchVendor help
The INSERT operation on the vendor table requires the company_id and the id of the vendor:
DELETE FROM BusinessCentral.Vendor WHERE company_id='dc50d5e8-f9c9-ed11-94cc-000d3a220b2f' AND vendor_id='625C8AFB-52F3-ED11-8848-000D3A373307'
The DELETE operation may not work as expected using a Linked Server connection; if you are writing SQL code through a Linked Server connection, use the corresponding EXEC operation instead.
You can access the ODataV4 and custom AL endpoints using EXEC commands only in this release. The ODataV4 endpoints are exposed using a different URI than the BC APIs, and follow this pattern: https://api.businesscentral.dynamics.com/v2.0/TENANT_ID/ENVIRONMENT/ODataV4/Company('COMPANYNAME')/operation
To access any ODataV4 or custom endpoint, use the _rawHttpRequest like this:
EXEC BusinessCentral._rawHttpRequest '{"method": "GET",
"uri": "https://api.businesscentral.dynamics.com/v2.0/TENANTID/Production/ODataV4/Company(''CRONUS USA, Inc.'')/Power_BI_Customer_List",
"applyContentTx": {
"rootPath": "value",
"sourceColumn": "0",
"rawColumn": "_raw"
}
}'
The _rawHttpRequest operation takes an input JSON document that provides the necessary information to make an HTTP/S call using the saved BusinessCentral credentials:
- The method attribute represents the HTTP Verb
- The uri attribute is either the relative URI from the configuration or the full URI to the endpoint (ODataV4 requires the use of your Azure Tenant ID)
- The applyContentTx attribute is optional, and when set, transforms the HTTP Response into rows and columns automatically using the JSON Path provided
When specifying a POST, PUT, or PATCH operation, the following additional attributes can be used:
- httpPayload: the body of the operation as a string
- httpContentType: the content type in the body
If headers need to be provided, the headers attribute can be added; for example, the If-Match header can be specified like this:
"headers": [
{
"Key": "If-Match",
"Value": "..........."
}
]
Here is a sample JSON Payload to send a PATCH request to the BC API using a relative URI. Note that COMPANY-ID, JOURNAL-ID, ETAG-VALUE, and JSON-BODY must be specified per the API or ODataV4 specification.
{
"method": "PATCH",
"uri": "/companies(COMPANY-ID)/journalLines(JOURNAL-ID)",
"applyContentTx": null,
"headers": [
{
"Key": "If-Match",
"Value": "ETAG-VALUE"
}
],
"paging": null,
"httpPayload": "JSON-BODY",
"httpContentType": "application/json"
}
When configured correctly, Enzo Server will automatically call the OAuth 2.0 endpoint to generate or refresh tokens. However, you can specify your own token too.
To inspect the Bearer Token used by Enzo Server, run this command:
SELECT * FROM BusinessCentral._oauthTokens
You can also save your own token; a token must be saved to an existing configuration setting created previously. Assuming the name of the configuration setting is cronus, the following SQL command saves a custom token:
EXEC BusinessCentral._saveOAuthTokens 'cronus', 'paste your OAuth 2.0 token here'