Connecting Azure Data Factory to a Mirrored Database in Microsoft Fabric (cross-tenant) Using a Service Principal

Sathya Narayan — Mon, 14 Apr 2025 18:54:38 +0000

Microsoft Fabric allows data engineers to mirror databases such as Azure SQL or Cosmos DB directly into Fabric for real-time analytics. However, teams working with Azure Data Factory (ADF) often need to use Service Principal (SP) authentication to connect and extract data from these mirrored databases.

In this post, I outline a working solution with all the steps, for cases where Azure Data Factory (ADF) and Fabric are in different tenants. The solution uses Service Principal (SP) to authenticate across tenants.

My current understanding, as of this writing (April 2025), is that ADF cannot directly connect to mirrored databases in Fabric using the SQL endpoint and service principal authentication, even though:

The mirrored database exposes a SQL endpoint.
The service principal has been granted access via Fabric.
Connectivity is successful using tools like SSMS.

Solution Summary

To enable ADF to access data from a mirrored database in Microsoft Fabric located in a different tenant:

Service principal authentication must be configured across tenants.
Since mirrored databases are not directly supported in ADF, the data must be copied into a native Fabric warehouse.
ADF can then connect to the Fabric warehouse using the native connector with SP authentication.

Step-by-Step Implementation Guide

① Configure the Service Principal

In the customer's (ADF-hosting) tenant:

Go to Azure Active Directory > App registrations > + New registration.
Register an app and note:
- Application (client) ID
- Tenant ID
Under Certificates & secrets, generate a new Client Secret.
Under Authentication, set the supported account types to: > Accounts in any organizational directory (Multitenant)

In your tenant (Fabric-hosting):

Federate the service principal:

az ad sp create --id <client-id>

Retrieve the object ID:

az ad sp list --filter "appId eq '<client-id>'" --query [0].id --output tsv

② Assign Fabric Access to the Service Principal

Using PowerShell:

Install-Module -Name MicrosoftPowerBIMgmt -Scope CurrentUser
Connect-PowerBIServiceAccount
Add-PowerBIWorkspaceUser -Id <WorkspaceId> `
                         -PrincipalType App `
                         -Identifier <ObjectId> `
                         -AccessRight Contributor

The workspace ID is the GUID of your Fabric workspace, visible in the URL.

Confirm access by checking the Manage Access pane in Fabric.

③ Copy Data from Mirrored Database to Fabric Warehouse

Since ADF cannot read directly from a mirrored database, replicate its data into a Fabric warehouse.

Option 1: Use T-SQL in Fabric

Use the SQL endpoint of the Fabric mirrored DB to run:

CREATE TABLE [warehouse].[table_name] AS
SELECT * FROM [mirrored].[source_table];

Use CREATE TABLE AS SELECT (CTAS) in your warehouse to replicate the table.

Option 2: Use Dataflows Gen2 or Pipelines inside Fabric

Use Power Query or Fabric pipelines to stage data from the mirrored DB into the warehouse.

④ Connect ADF to the Fabric Warehouse

In ADF, go to Manage > Linked Services > + New.
Choose connector: Microsoft Fabric Warehouse (or Azure SQL if Fabric connector is limited).
Fill in:
- Server name: SQL endpoint of the warehouse
- Database name: Warehouse name
- Authentication: Azure Active Directory - Service Principal
- Tenant ID: Your tenant (Fabric's tenant)
- Client ID + Client Secret: From App Registration in the customer tenant
Click Test Connection.

Summary: What Works and What Doesn’t

Capability	Supported?	Notes
ADF connecting to Fabric Warehouse via SP	Yes	Use native Fabric Warehouse connector
ADF connecting to mirrored DB SQL endpoint via SP	No	SQL endpoint exists but is not ADF-compatible
SSMS connecting to mirrored DB via SP	Yes	Confirms permissions and network setup
Fabric SP assignment	Yes	Must use PowerShell with Object ID
OneLake/Delta export from mirrored DB	Yes	Alternative if warehouse is not used

Final Thoughts

I hope this will help others who are trying to integrate cross-tenant SP-authenticated ADF pipelines into Fabric data platform.

Forem: Sathya Narayan