Microsoft Fabric Data Lakehouse with Business Central : ADF Connectivity

Connect Azure Data Factory (ADF) to Business Central

Overview

To connect ADF Pipeline to Dynamics 365 Business Central, you can use the OData connector with Microsoft Entra service principal authentication. Here’s a step-by-step guide based on Microsoft’s official documentation and community insights.

While Fabric Pipelines can technically connect to Business Central, they currently do not support OAuth 2.0 in the Copy Activity, which is required for secure access to Business Central. Whilst  Fabric Gen 2 Dataflows does now support a basic incremental load and is easier to set up ADF has high performance and more contract. Where the requirement is to load the entire Business Central data set into the Bronze Fabric Data Lakehouse. Therefore, there is a need to use Azure Data Factory (ADF) to connect to Business Central and Load Data into the Bronze area of the Data Lakehouse.

References

Create an Entra App for BC Integration

Extract data from Business Central – Business Central | Microsoft Learn

How to connect Business Central to Azure Data Factory – Microsoft Q&A

Registering Dynamics 365 Business Central Application in Azure

Set up the Microsoft Entra application in Business Central

 

1: Service Principal for BC

Within Microsoft Entra : Create Service Principal to Connect Business Central to Azure Data Factory.

Reference: Register a Microsoft Entra app and create a service principal – Microsoft identity platform | Microsoft Learn

Log into https://entra.microsoft.com/#home

Microsoft Entra – Microsoft Entra admin center

Entra ID > App registrations-> New registration

Give the application a name: BusinessCentralCRONUS

Register an app in Microsoft Entra

 

Click “Register”

Gather the following information …

Application (client) ID: 6c6cc50a-37a9-4005-bfc0-7cc07e90a055

Object ID: 4f7379fa-88cc-4aa7-82ef-e1e3d467a0b1

Directory (tenant) ID: f56c8c22-49d3-4498-be8b-e71a4b5eddc6

Certificates & secrets> New client secret

Value

xxxxx~xxxxxxxx-xxxxxxxxxxxxxxxxxxxxxx

N.B. For security purposes within this blog the Value has to be masked.

Secret ID

cafc6007-95f6-4d42-9d5b-2a2b706253d5

 

2: BC API permissions

Assign API permissions to Business Central


 

3: BC Service Principal Permissions

Assign Business Central Permissions to this Service Principal

Click on “Grant”

Click on “Accept”

Within Business Central Client search for “Microsoft Entra Application Card”

Select “New”

Get the OData URL:

  • Format:
  • https://api.businesscentral.dynamics.com/v2.0/{tenant-id}/{environment}/ODataV4/
  • Example:

https://api.businesscentral.dynamics.com/v2.0/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/Production/ODataV4/

https://api.businesscentral.dynamics.com/v2.0/f56c8c22-49d3-4498-be8b-e71a4b5eddc6/Production/ODataV4/ 

4: BC Linked Service

Create a Linked Service to connect Business Central to Azure Data Factory

  1. Go to Azure Data Factory (ADF) > New Pipeline > Source -> New Linked Service
  2. Choose OData as the connector.
  3. Fill in the following details:
    • Service URL: Your Business Central OData URL which is
      • https://api.businesscentral.dynamics.com/v2.0/f56c8c22-49d3-4498-be8b-e71a4b5eddc6/Production/ODataV4/
    • Authentication Type: Service Principal with Key
    • Tenant: Your Azure tenant ID: f56c8c22-49d3-4498-be8b-e71a4b5eddc6
    • Service Principal ID: Client ID from Entra: 6c6cc50a-37a9-4005-bfc0-7cc07e90a055
    • Service Principal Key: Client Secret (Value) from Entra: – ecC8Q~ZbB2Tf0a0ir~A616ykFjhhxqu9hzlq3dzP
    • Microsoft Entra ID Resource: https://api.businesscentral.dynamics.com
  4. Test the connection and save.

Previewed data …


5: Create a Dataset

Create a data source data set which utilizes the Business Central Linked Service you have created.

  1. Go to Author > Datasets > New Dataset.
  2. Choose OData and link it to the Linked Service you just created.
  3. Specify the Entity/Table name Company from Business Central.

6: Create Lakehouse

To prepare to create the destination of the ADF pipeline open another internet browser session, login to Microsoft Fabric, create a workspace and then create a data lakehouse which will contain the destination table of the ADF pipeline.

7: Service Principal for Fabric

Create Service Principal to act as a service account to connect the Fabric Lakehouse access to Azure Data Factory

Reference: Register a Microsoft Entra app and create a service principal – Microsoft identity platform

Log into https://entra.microsoft.com/#home

Microsoft Entra – Microsoft Entra admin center

Entra ID > App registrations-> New registration

Give the application a name: FabricLakehouseServiceDev

 

Click “Register”
Gather the following information …
Application (client) ID: 234209f3-d19c-4dae-a2a0-789b0089cbe6
Object ID: e67971a9-fb47-468a-b692-e646bd001262
Directory (tenant) ID: f56c8c22-49d3-4498-be8b-e71a4b5eddc6

Certificates & secrets> New client secret

FabricLakehouseServiceDevSecret

 

Click “Add”

Value

xxxxx~xxxxxxxx-xxxxxxxxxxxxxxxxxxxxxx

N.B. For security purposes within this blog the Value has to be masked.

Secret ID
82a9298e-2a0d-4faa-a31c-de72393ddb21

 

Service Principal will need access to update this Lakehouse

  1. Go to API permissions > Add a permission.
  2. Choose Microsoft Graph > Application permissions.
  3. Add:
    • Files.ReadWrite.All
    • Sites.ReadWrite.All
  4. Click Add permissions.
  5. Click Grant admin consent for your tenant.

 

Create or Use a Microsoft Entra Security Group:

    • Go to Microsoft Entra ID > Groups.
    • Create a Security group (not Microsoft 365).
    • Add the App ID as a member of this group.

Ensure the tenant admin can enable Service principals can use Fabric APIs in Fabric Admin portal.

 

Group Name: Fabric

Enable in Fabric Admin Portal:

  1. Sign in to the Microsoft Fabric Admin portal.
  2. Navigate to Tenant Settings > Admin API settings.
  3. Enable one or both of the following:
    • Service principals can access read-only admin APIs
    • Service principals can access admin APIs used for update
  4. Choose Specific security groups and enter the group created in Step 3.
  5. Click Apply.

 

Assign the Service Principal to the Fabric Workspace

  1. Go to Microsoft Fabric.
  2. Navigate to the workspace containing lakehouse_cronus.
  3. Click Manage Access.
  4. Click Add > Add user or group.
  5. Enter the App ID (from Azure AD registration). 234209f3-d19c-4dae-a2a0-789b0089cbe6

6. Assign the role Contributor or Member.

 

Grant OneLake Permissions

  1. In the Fabric workspace, go to OneLake Data Hub.
  2. Locate lakehouse_cronus.
  3. Click the three dots (⋯) > Manage Permissions.
  4. Add the Service Principal (App ID or name).
  5. Assign Read/Write or Full Control.

 

Step 8: Build a Pipeline

Use Copy Data Activity to extract data from Business Central and copy to a Microsoft Fabric table.

Set the source as your OData dataset.

Step 9: Linked Service for Fabric

Configure Linked Service for Fabric Destination (Lakehouse)

LakehouseTableCompany

 

    • Service Principal ID: Client ID from Entra: 234209f3-d19c-4dae-a2a0-789b0089cbe6

Service Principal Key: Client Secret (Value) from Entra: – xxxxx~xxxxxxxx-xxxxxxxxxxxxxxxxxxxxxx

(Enter new table name and then pipeline will create the table on the initial load)

Run (debug) pipeline

 

 

 

View results in Fabric …

Microsoft Fabric Data Lakehouse with Business Central : Management Summary and Architecture
Build a Production-Ready Microsoft Fabric Data Platform in 5 Days