Azure SQL DBA Tools: Linked Server to MS Azure Fabric Database / Datawarehouse

Overview

Creating a linked server from SQL Server to Microsoft Azure Fabric Database or Datawarehouse enables seamless cross-platform querying. The key to making this work is using an ODBC bridge with a service principal account for secure authentication.

The ODBC Bridge Is Key

To create a Linked Server in SQL Server to MS Azure Fabric Database/Datawarehouse, you need to use a service principal and ODBC Driver 17 for SQL Server. The ODBC bridge acts as the translation layer between your on-premises SQL Server and the cloud-based Fabric endpoint.

Step-by-Step Setup

Step 1: Get the MS Azure Fabric Datawarehouse Endpoint

Navigate to your Azure Fabric workspace and locate the SQL connection endpoint. It will follow this format:

<your-fabric-sql-endpoint>.datawarehouse.fabric.microsoft.com

Step 2: Create and Register an Azure AD Application (Service Principal)

In Microsoft Entra ID (formerly Azure Active Directory), create and register a new application:

• Display Name: e.g., Fabric-Linked_Servers
• Application (Client ID): Your service principal client ID
• Secret: Generate a client secret (password) for the service principal

Step 3: Grant Access in the Fabric Database/Datawarehouse

Ensure the service principal has appropriate permissions in your Fabric database or datawarehouse. Grant the necessary roles so the linked server can read data from Fabric.

Step 4: Create a 64-bit ODBC System DSN

Create a 64-bit ODBC System DSN on your SQL Server host. Note that 32-bit DSNs do not work for this configuration.

• Driver: ODBC Driver 17 for SQL Server
• Name: Your chosen ODBC DSN name (e.g., FabricDSN)
• Server: <fabric-sql-endpoint>.datawarehouse.fabric.microsoft.com
• Login ID: <service principal client id>@<tenant id>
• Password: The service principal secret

Step 5: Create the Linked Server

In SQL Server Management Studio (SSMS), create the linked server with these settings:

• Provider: Microsoft OLE DB Provider for ODBC Drivers
• Data Source: Your ODBC DSN name
• Security: Select "Be made using this security context"
• Remote login: <service principal client id>@<tenant id>
• With password: The service principal secret

Querying the Linked Server

Once configured, you can query your Azure Fabric Database or Datawarehouse directly from SQL Server using four-part naming:

SELECT * FROM [LinkedServerName]..[SchemaName].[TableName]

Datazip specializes in Azure SQL hybrid architectures and managed database services. Call 1-833-DATAZIP or visit www.SQLBrainBox.com for our monitoring tool.