Dynamics 365 for Finance and Operations Integration with on-premises SQL Server using OData
To export data from Dynamics 365 for Finance & Operations (referred to as D365 from here onwards) and import it into an SQL Server database we need a separate .Net application running on premises. The reason is that D365 which is in the cloud is not able to access on premise applications for security reasons, therefore an intermediary application is required which will connect with D365 using the OData protocol.
The .Net application will first read data from D365 after authenticating with Azure Active Directory (AAD) and then insert the data into the SQL Server using SQL commands.
This application was built on the Microsoft .NET framework version 4.7.02053 using C# code.
Azure Active Directory Authentication
Since the D365 is hosted by Azure (cloud), the .Net application needs to authenticate the user or service account with the AAD running D365. The user name will first be authenticated with AAD, if successful AAD will return an access token. This token will then be used to authenticate the .Net application with D365.
OData is used to transfer data from and to D365. It uses a Representational State Transfer (REST) based protocol to Create, Read, Update, and Delete (CRUD) data. One of the advantages of OData is that it is a widely used integration protocol across products.
AAD Authentication Setup
Before we can start looking at the code we need to make some configurations in the Azure Portal and D365 in order to give access to the .NET application to read/write data from D365.
For this POC the username firstname.lastname@example.org will be used for authentication purposes.
The following steps explain the required configuration to give permission to the account used to connect with OData in D365.
First go to https://portal.azure.com and select the correct AAD. For this example we are using the Bluefort AAD.
Next, go to Azure Active Directory > App registrations > and click on “New application registration”.
Enter a name, select Web app/API, and enter your D365 URL for sign on. In our POC example we are using the DEV box URL https://usnconeboxax1aos.cloud.onebox.dynamics.com.
Go back to the list of applications and choose the newly created app. Click on the app and go to “required permissions”.
In this page you will need to add “Microsoft Dynamics ERP” and give access permissions.
In the registered app settings page take note of the Application ID. You will need this further on, in our case the Application ID is 5899f0a7-30a8-4040-a6ca-c83f8518a5f4.
From the registered app settings page go to Keys and create a new key. Copy the key value as you will also be needing this to authenticate the .NET application with D365.
Your AAD configuration is now done. By the end of these steps you should have the following information:
D365 Application Access
Open D365 and go to System Administration > Setup > Azure Active Directory applications. In this page add the application id (from previous steps in Azure Portal) and give access to the user. In the POC case email@example.com is using the Admin account.
.NET application details
The .NET application for the POC is coded in C# and its solution contains three projects. Projects AuthenticationUtility and OdataUtility are libraries containing code used for authentication and OData purposes. The main project ODataFormApplication has the code which reads data from the GL in D365 and writes it in an SQL Server 2008 database.
This assembly has all the metadata information about D365 data entities that are exposed and can be consumed. This library was automatically generated and provided by Microsoft from this link: https://github.com/Microsoft/Dynamics-AX-Integration/tree/master/ServiceSamples/ODataUtility.
The authentication utility contains all the client parameters used for authentication with AAD and the SQL Server connection. These parameters can be found in the ClientConfiguration.cs file.
Below are the parameters used for the POC.
In the OAuthHelper.cs file, method GetAuthenticationHeader contains the code which is used to authenticate with the ADD. It uses the application Id and key (client secret) that were created in the Azure setup previously.
The POC application has only one form. It displays the D365/Azure/SQL parameters on the left hand side, and the execution log on the right hand side. To run the data transfer from D365 to SQL click on the “Run D365 GL Extract”.
The application will read data from the LedgerJournalLines data entity of D365 and insert the records in the SQL Server 2008 database provided in the SQL connection string. The code which is connecting with D365 and SQL Server 2008 and doing the data tranfer is in the Program.cs file of the ODataFormApplication project.
Calling OData Queries
To get the General Ledger data from D365, the .NET application reads transaction data from the LedgerJournalLines data entity in D365 using OData queries. For more details on how to use OData in D365 refer to the Microsoft documentation from this link: https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/data-entities/odata.
In the Program.cs the method getContext() creates the connection with D365 by calling the authentication methods in the OAutheHelper.cs class and returns a Resource class (OData Entity class with an open connection with D365).
The resource class is then used to build the OData to get the number of specified records and filter by company. In the code below you can see that first we call the getContent() method to open & authenticate the connection with D365, then we build the OData query with a number of filters and finally we execute the OData query to get a list of GL records.
The final OData query will be similar to the one below:
https://usnconeboxax1aos.cloud... eq ‘usmf’
The next lines of code will then loop through the retrieved GL records and insert them one by one in the SQL Server. More details in the following section.
For this POC, the .NET application is writing GL transaction data into the SQL Server 2008 instance bavsql01\idlt. The GL records from the D365 will be inserted into table GLExtract in the SQL Server 2008 database. The SQL script below can be used to re-create the table on a different database.
To make the SQL Connection we are using the SQLConnection class from the standard .NET System.Data.SqlClient library. The SQL connection string can be viewed in the ClientConfiguration.cs file of the Authentication Utility project.
The .NET application will loop the GL transactions retrieved from the OData query and insert the records in the SQL Server. This code can be found in the Program.cs file of the ODataFormApplication project.
string saveGLTransaction = “INSERT into GLExtract (JournalBatchNumber, Voucher, TransactionText, TransactionDate, DebitAmount, CreditAmount, Company) VALUES (@JournalBatchNumber, @Voucher, @TransactionText, @TransactionDate, @DebitAmount, @CreditAmount, @Company)”;