Discover and Extract Data From Microsoft Fabric

[mariam_ayman]

Overview

Fabric provides two main data experiences:

• Warehouses: Traditional SQL-based data warehouses optimized for structured analytics and business intelligence workloads
• Lakehouses: Open data architecture that combines the flexibility of data lakes with the performance of data warehouses for both structured and unstructured data

Incorta now supports native connectivity to both Fabric Warehouses and Lakehouses through its dedicated connectors, enabling seamless data extraction and analysis from your Fabric environment.

Connection Methods Overview

You can connect to Fabric data through several approaches:

Incorta Connector	Fabric
SQL Server	Warehouse
SQL Server	Lakehoue Delta Tables
Microsoft ADLS Gen2	Lakehoue Delta Tables
Microsoft ADLS Gen2	Lakehouse Files

Prerequisites

Before setting up any connection, ensure you have:

1. Service Principal - Required for secure authentication
2. Workspace Access - Appropriate permissions in your Fabric workspace

Setting Up Service Principal

Step 1: Register Application in Azure

Follow the Microsoft Documentation 

Step 2: Create Application Secret

1. In your registered app, go to Certificates & secrets
2. Click New client secret
3. Copy and securely store the generated secret value

Step 3: Grant Workspace Access

1. Navigate to your Fabric workspace
2. Click Manage Access
3. Select Add User/Group
4. Enter your service principal name
5. Assign appropriate permissions (typically Member or Admin)

Method 1: SQL Server Connection

Getting Your Connection String

You can obtain the connection string directly from your warehouse settings in the Fabric portal.

Connection String Format:

jdbc:sqlserver://<workspace_id>.datawarehouse.fabric.microsoft.com:1433;database=<database_name>;authentication=<authentication_method>;

Database Configuration

• For Warehouse: Replace <database_name> with your warehouse name
• For Lakehouse: Replace <database_name> with your lakehouse name

Authentication Setup

Since Fabric does not support SQL Authentication. Choose one of these methods:

Option A: ActiveDirectoryPassword

• When to use: Direct username/password access available
• Setup:
  • In the Connection Sting, replace <authentication_method> with ActiveDirectoryPassword
  • Provide your credentials in the respective connection fields

Option B: ActiveDirectoryServicePrincipal (Recommended)

• When to use: When ActiveDirectoryPassword is unavailable (e.g., MFA enabled)
• Setup:
  • In the Connection String:
    • Replace <authentication_method> with ActiveDirectoryServicePrincipal
    • Add the following parameters to your connection string
    • Replace <principal_id> and <principal_secret> with the values created in the Setting Up Service Principal section.

AADSecurePrincipalId=<principal_id>;AADSecurePrincipalSecret=<principal_secret>;

Note: When using ActiveDirectoryServicePrincipal, you can add dummy data to the username and password fields, as the connection string parameters will override them.

Method 2: Microsoft ADLS Gen2 Connection

Getting Your Directory Path

1. Navigate to your lakehouse in Fabric
2. Go to Tables or Files properties
3. Copy the ABFS path

Directory Path Format:

For Tables:

abfss://<file_system>@onelake.dfs.fabric.microsoft.com/<path_to_folder>/Tables

For Files:

abfss://<file_system>@onelake.dfs.fabric.microsoft.com/<path_to_folder>/Files

Authentication Options

Option A: Storage Account Key

• Follow Microsoft's documentation for creating an Azure Storage Account Key
• Enter the key in the authentication field
• Enter the Directory Path obtained above

Option B: Service Principal (Recommended)

• Enter the Client ID and the Tenant ID in their respective fields
• Enter the Directory Path obtained above

Next Steps

Once your connection is established and tested successfully, you can:

• Create schemas to organize your data
• Set up data discovery to explore available tables and files
• Configure data refresh schedules
• Build dashboards and reports using your Fabric data

Troubleshooting Tips

• Ensure your service principal has the necessary permissions in both Azure and Fabric
• Verify that your connection string format matches exactly
• Check that your workspace ID and database names are correct
• For ADLS Gen2 connections, confirm the ABFS path is copied correctly