1Business Central – App Installation

Download the .APP file from the folder \LatestVersion\BusinessCentral in the link below.

Deploy

Install as “PTE” app in Business Central.

“Search for Extension Management” and select Manage > Upload Extension.

Wait for the installation to finish.

Check the “Installation Status” page in “Manage” if needed.

2Azure – Set up API user

Log into https://portal.azure.com.

Search for “App Registrations” and create a new registration.

After successful creation:

Add the URL below in Redirect URIs: https://businesscentral.dynamics.com/OAuthLanding.htm

Select “API Permissions” > “Add a permission”.

Select Dynamics 365 Business Central.

Select “Delegated Permissions” and “yes” in both checkboxes.

Select “Add a permission” again and do the following set up for “Application permissions”.

Click “Grant admin consent for”.

Choose “Certificates & secrets”.

In the next part the following 3 pieces of data are needed:

Certificates & secrets – Value:

Application (client) ID and Directory (tenant) ID:

3Business Central – Set up Entra Application user

In Business Central search for Microsoft Entra Application.

Create a new Entra Application and paste the Application (client) ID from Azure into Client ID.

Click on “Grant Consent”.

Add the BC2SQLRead permission set. This gives read access to all the BC2SQL related set up tables.

Furthermore the user should be set up to read the tables that need to be synchronized.

4Business Central – Configure BC2SQL

In Business Central search for BC2SQL Setup.

Configure the companies, tables and fields that should be synchronized.

BC2SQL Setup is editable when the status is “On Hold”.

This is to avoid synchronization of data while configuring what to synchronize.

5Azure – SQL Setup

Create Database

Log into https://portal.azure.com.

Search for “SQL Server” > create.

Click Create.

After the SQL Database Server has been created, select the SQL Server and click “Create Database”.

Click “Review + create”.

Create Database Schemas in SQL Server Management Studio

In Azure find the SQL Database and click “Show database connection strings”.

Copy this part of the ADO.NET (SQL Authentication) connection string.

Open Microsoft SQL Server Management Studio and connect as shown below with the SQL username and password from when the database was created.

You may be asked to log in to Azure.

Open a new Query in the database and execute the SQL statements in the file CreateBC2SQLTables.sql in the \LatestVersion\.NET\ folder in the link below: Deploy

This creates the BC2SQL tables that are needed for the synchronization to work.

Select the BC2SQL.TenantSetup table > Right Click > Edit Top 200 Rows.

Complete the setup below:

Default CompanyId can be found in BC by searching in companies:

6Azure – Web App Setup

Log into https://portal.azure.com.

Search for “App Services” > create > “Web App”.

Click Create.

After creation select > Webjobs > Add within the newly created “Web App”.

The BC2SQL.zip file is placed in the folder \LatestVersion\.NET in the link below: Deploy

Set up connection between Webjob and database:

Find and edit appsettings.json.

Enter the connection string from the database created earlier.

Note "DBConn" : {ConnectionString}.

The connection string can be found here:

7On-Premises – SQL Setup

Create Database

Open Microsoft SQL Server Management Studio (SSMS) and connect to the on-premises SQL Server instance using Windows Authentication or a SQL login with sufficient permissions.

Right click Databases > New Database…, name it (for example BC2SQL_Data) and click OK.

Create a login that the Windows Service will use to connect, and grant it db_owner on the new database (or at minimum read/write and the ability to create the BC2SQL schema and tables). This can be either:

Create Database Schemas in SQL Server Management Studio

Open a new Query against the database and execute the SQL statements in the file CreateBC2SQLTables.sql in the \LatestVersion\.NET\ folder in the link below: Deploy

This creates the BC2SQL tables that are needed for the synchronization to work.

Select the BC2SQL.TenantSetup table > Right Click > Edit Top 200 Rows

Complete the setup below:

Default CompanyId can be found in BC by searching in companies:

8On-Premises – Windows Service Setup

Deploy the Files

The BC2SQL.zip file is placed in the folder \LatestVersion\.NET in the link below: Deploy

Configure the Connection

Find and edit appsettings.json in the extracted folder.

Set TargetSQLSystem to MicrosoftSql (or PostgreSql) and enter the connection string for the on-premises database created in the previous section under ConnectionStrings.

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "TargetSQLSystem": "MicrosoftSql", // or "MicrosoftSql" || "PostgreSql"
  "ConnectionStrings": {
    "MicrosoftSql": "server=SERVERNAME\INSTANCE;Database=BC2SQL_Data;User Id=bc2sql;Password=YourPassword;TrustServerCertificate=True;"
  }
}

Alternatively, if the service connects with an Active Directory user (see On-Premises – SQL Setup), use a trusted connection instead of a username and password. The Windows Service then connects as the AD user it runs under, and no password is stored in the file:

"MicrosoftSql": "server=SERVERNAME\INSTANCE;Database=BC2SQL_Data;Trusted_Connection=True;TrustServerCertificate=True;"

Install and Start the Service

Open an elevated (Run as administrator) Command Prompt or PowerShell and create the Windows Service pointing at the BC2SQL executable:

sc.exe create BC2SQL binPath= "C:\BC2SQL\BC2SQL.exe" start= auto DisplayName= "BC2SQL"

Start the service:

sc.exe start BC2SQL

Alternatively, open services.msc, locate the BC2SQL service, set its Startup type to Automatic and click Start.

If the service connects to the database with a trusted connection (Windows Authentication), open the service’s Properties > Log On tab and set This account to the Active Directory user that has access to the SQL Server. The service then runs in that user’s context.