Skip to main content

MS Fabric Connector

Guide to connecting MS Fabric

Updated over 2 weeks ago

Welcome to the Microsoft Fabric Connector setup guide! This document serves as a comprehensive guide for configuring Microsoft Fabric to integrate seamlessly with Savant using Azure AD App registration and JDBC.

Features

  • Read data from MS Fabric Data warehouse

  • Writing analysis outcome to MS Fabric

Requirements

  • Admin access to Azure AD (to register applications and configure permissions)

  • Access to Microsoft Fabric with ability to create or manage a Warehouse

Connection Methods

Savant supports secure authentication to Fabric using Azure AD App Registration + JDBC.

  • Azure AD OAuth: (Client ID, Client Secret, Tenant ID)

This step-by-step guide ensures you can efficiently set up your environment for optimal data integration.

Step 1: Create Azure AD App Registration

  • Go to Azure Portal

  • Navigate to Azure Active Directory → App registrations → New registration

  • Configure the app:

  • Click Register

  • Save these values:

    • Application (client) ID: e.g., 12345678-1234-1234-1234-123456789012

    • Directory (tenant) ID: e.g., 87654321-4321-4321-4321-210987654321

  • Go to Manage > “Certificates & secrets” > + New client secret

  • Note down the client secret you only have this chance to see it

Step 2: Configure App Permissions

  • In your app registration, go to API permissions

    image-20240923-045507.png

  • Click Add a permission

  • Choose APIs my organization uses

  • Search for Azure SQL Database

  • Select Delegated permissions

  • Check: user_impersonation

  • Click Add permissions

  • Set “Active Directory Admin” for Azure SQL server. This enables the link between SQL server and Microsoft Entra ID. Refer to Microsoft Documentation

Step 3: Set Up Your Fabric Warehouse

  • Go to Microsoft Fabric

  • Create a new Workspace or select any created workspace

  • Add users to the Fabric Workspace

    • In your Fabric workspace → Manage access

    • Click "Add people or groups"

    • Search for your app registration name (Fabric-Warehouse-Connector)

    • Assign role: "Admin" or "Member" (Contributor might not be sufficient for DDL for some instances)

    • Click "Add"

  • Create or Warehouse (not Lakehouse):

    • Click New → Warehouse

    • Give it a name (e.g., MyWarehouse)

  • Grant the user access to the Warehouse

    • Click on 3 dots next to the Warehouse and Go to Manage Permissions

    • Click on Add User

    • Add the user with Read all and Build Reports permission

  • If needed, Grant SQL Permissions in Warehouse

    • Connect to the warehouse using SQL (as an admin) and run:

      -- Create a user for the service principal  
      CREATE USER [Fabric-Warehouse-Connector] FROM EXTERNAL PROVIDER;
      -- Grant necessary permissions
      ALTER ROLE db_owner ADD MEMBER [Fabric-Warehouse-Connector];
      -- Or for more granular control:
      -- GRANT CREATE TABLE TO [Fabric-Warehouse-Connector];
      -- GRANT ALTER ON SCHEMA::dbo TO [Fabric-Warehouse-Connector];
      -- GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO [Fabric-Warehouse-Connector];

  • Once created, get the SQL connection string:

    • Open the Warehouse

    • Click Settings (gear icon)

    • Copy the SQL connection string (e.g., 

      24udi3n7wzeurfvjqxcfi3pb34-6gflpajeaz4evjptimyhy6km5q.datawarehouse.fabric.microsoft.com

Step 5: Configure the connector in Savant

In Savant, go to Connections → Add System → Microsoft Fabric.

You’ll need to provide:

  • Client ID*: From Azure AD App Registration

  • Client Secret*: From Azure AD App (Certificates & secrets)

  • Tenant ID*: Directory (tenant) ID from Azure AD

  • Server connection string*: From Fabric Warehouse SQL copied above

  • Warehouse*: Name of your Fabric Warehouse

Click Authenticate. On success, Savant will validate the connection and list available schemas/tables.

Important Notes:

  • Use the exact app registration name in square brackets

  • The db_owner role provides full DDL/DML permissions

  • Use more restrictive permissions based on needs

  • Ensure user has "Admin" or "Member" role in workspace

  • Read and Write Operations can be performed only for the Warehouse to which the user has connected using the SQL string.

  • Critical: Are you connecting to a Lakehouse SQL Analytics Endpoint instead of a Warehouse?

    • Lakehouse SQL Analytics Endpoint is READ-ONLY for DDL operations. Microsoft Fabric doesn't support directly creating tables in OneLake using DDL statements. OneLake functions as the data lake storage layer, and DDL statements are typically used for structured data management within a data warehouse environment.

    • How to verify:

      • Lakehouse endpoints look like: *.datawarehouse.fabric.microsoft.com (same as warehouse)

      • But the database name for a Lakehouse typically ends with _lakehouse or is associated with a Lakehouse item

      • Warehouse database names are typically just the warehouse name

Related Articles
Databricks Connector Setup
Microsoft Exchange Connector
Azure Synapse Connector
Microsoft OneDrive/Sharepoint Connector
ADP Connector
Did this answer your question?