Connectivity to data warehousing in Microsoft Fabric

Applies to: SQL analytics endpoint and Warehouse in Microsoft Fabric

In Microsoft Microsoft Fabric, a Lakehouse SQL analytics endpoint or Warehouse is accessible through a Tabular Data Stream, or TDS endpoint, familiar to all modern web applications that interact with a SQL Server endpoint. This is referred to as the SQL Connection String within the Microsoft Fabric user interface.

This article provides a how-to on connecting to your SQL analytics endpoint or Warehouse.

To get started, you must complete the following prerequisites:

• You need access to a SQL analytics endpoint or a Warehouse within a Premium capacity workspace with contributor or higher permissions.

Authentication to warehouses in Fabric

In Microsoft Fabric, two types of authenticated users are supported through the SQL connection string:

• Microsoft Entra ID (formerly Azure Active Directory) user principals, or user identities
• Microsoft Entra ID (formerly Azure Active Directory) service principals

The SQL connection string requires TCP port 1433 to be open. TCP 1433 is the standard SQL Server port number. The SQL connection string also respects the Warehouse or Lakehouse SQL analytics endpoint security model for data access. Data can be obtained for all objects to which a user has access.

Retrieve the SQL connection string

To retrieve the connection string, follow these steps:

1. Navigate to your workspace, select the Warehouse, and select More options.

   

2. Select Copy SQL connection string to copy the connection string to your clipboard.

   

Get started with SQL Server Management Studio (SSMS)

The following steps detail how to start at the Microsoft Fabric workspace and connect a warehouse to SQL Server Management Studio (SSMS).

1. When you open SSMS, the Connect to Server window appears. If already open, you can connect manually by selecting Object Explorer > Connect > Database Engine.

   

2. Once the Connect to Server window is open, paste the connection string copied from the previous section of this article into the Server name box. Select Connect and proceed with the appropriate credentials for authentication. Remember that only Microsoft Entra multifactor authentication (MFA) is supported.

   

3. Once the connection is established, Object Explorer displays the connected warehouse from the workspace and its respective tables and views, all of which are ready to be queried.

   

When connecting via SSMS (or ADS), you see both a SQL analytics endpoint and Warehouse listed as warehouses, and it's difficult to differentiate between the two item types and their functionality. For this reason, we strongly encourage you to adopt a naming convention that allows you to easily distinguish between the two item types when you work in tools outside of the Microsoft Fabric portal experience.

Connect using Power BI

A Warehouse or Lakehouse SQL analytics endpoint is a fully supported and native data source within Power BI, and there is no need to use the SQL Connection string. The Data pane exposes all of the warehouses you have access to directly. This allows you to easily find your warehouses by workspace, and:

1. Select the Warehouse.
2. Choose entities.
3. Load Data - choose a data connectivity mode: import or DirectQuery.

For more information, see Create reports in Microsoft Microsoft Fabric.

Connect using OLE DB

We support connectivity to the Warehouse or SQL analytics endpoint using OLE DB. Make sure you're running the latest Microsoft OLE DB Driver for SQL Server.

Connect using ODBC

Microsoft Microsoft Fabric supports connectivity to the Warehouse or SQL analytics endpoint using ODBC. Make sure you're running the latest ODBC Driver for SQL Server. Use Microsoft Entra ID (formerly Azure Active Directory) authentication.

Connect using JDBC

Microsoft Microsoft Fabric also supports connectivity to the Warehouse or SQL analytics endpoint using a Java database connectivity (JDBC) driver.

When establishing connectivity via JDBC, check for the following dependencies:

1. Add artifacts, choose Add Artifact and add the following four dependencies in the window like this, then select Download/Update to load all dependencies.

   

2. Select Test connection, and Finish.

   

   <dependency>
      <groupId>com.microsoft.azure</groupId>
      <artifactId>msal4j</artifactId>
      <version>1.13.3</version>
   
   </dependency>
   
   <dependency>
      <groupId>com.microsoft.sqlserver</groupId>
      <artifactId>mssql-jdbc_auth</artifactId>
      <version>11.2.1.x86</version>
   </dependency>
   
    <dependency>
      <groupId>com.microsoft.sqlserver</groupId>
      <artifactId>mssql-jdbc</artifactId>
      <version>12.1.0.jre11-preview</version>
   </dependency>
   
    <dependency>
      <groupId>com.microsoft.aad</groupId>
      <artifactId>adal</artifactId>
      <version>4.2.2</version>
   </dependency>
   

Connect using dbt

The dbt adapter is a data transformation framework that uses software engineering best practices like testing and version control to reduce code, automate dependency management, and ship more reliable data—all with SQL.

The dbt data platform-specific adapter plugins allow users to connect to the data store of choice. To connect to Synapse Data Warehouse in Microsoft Microsoft Fabric from dbt use dbt-fabric adapter. Similarly, the Azure Synapse Analytics dedicated SQL pool data source has its own adapter, dbt-synapse.

Both adapters support Microsoft Entra ID (formerly Azure Active Directory) authentication and allow developers to use az cli authentication. However, SQL authentication is not supported for dbt-fabric

The DBT Fabric DW Adapter uses the pyodbc library to establish connectivity with the Warehouse. The pyodbc library is an ODBC implementation in Python language that uses Python Database API Specification v2.0.  The pyodbc library directly passes connection string to the database driver through SQLDriverConnect in the msodbc connection structure to Microsoft Fabric using a TDS (Tabular Data Streaming) proxy service.

For more information, see the Microsoft Fabric Synapse Data Warehouse dbt adapter setup and Microsoft Fabric Synapse Data Warehouse dbt adapter configuration.

Connectivity by other means

Any third-party tool can use the SQL Connection string via ODBC or OLE DB drivers to connect to a Microsoft Microsoft Fabric Warehouse or SQL analytics endpoint, using Microsoft Entra ID (formerly Azure Active Directory) authentication.

Custom applications

In Microsoft Fabric, a Warehouse and a Lakehouse SQL analytics endpoint provide a SQL connection string. Data is accessible from a vast ecosystem of SQL tooling, provided they can authenticate using Microsoft Entra ID (formerly Azure Active Directory). For more information, see Connection libraries for Microsoft SQL Database.

Considerations and limitations

• SQL Authentication is not supported.
• Multiple Active Result Sets (MARS) is unsupported for Microsoft Fabric Warehouse. MARS is disabled by default, however if MultipleActiveResultSets is included in the connection string, it should be removed or set to false.
• On connection to a warehouse, you might receive an error that "The token size exceeded the maximum allowed payload size". This is due to having a large number of warehouses within the workspace or being a member of a large number of Microsoft Entra groups. In event of this error, work with the Workspace admin to clean up unused Warehouses and retry the connection, or contact support if the problem persists.
• Linked server connections from SQL Server are not supported.

Related content

• Create a Warehouse in Microsoft Fabric
• Better together: the lakehouse and warehouse