Connectivity to data warehousing in Microsoft Fabric

  • Article

Applies to: ✅ SQL analytics endpoint and Warehouse in Microsoft Fabric

In 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 TDS 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:

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

For more information, see Microsoft Entra authentication as an alternative to SQL authentication in Microsoft Fabric.

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.

Allow Power BI service tags through firewall

To ensure proper access, you need to allow the Power BI service tags for firewall access. For more information, see Power BI Service Tags. You cannot use the Fully Qualified Domain Name (FQDN) of the TDS Endpoint alone. Allowing the Power BI service tags is necessary for connectivity through the firewall.

Retrieve the SQL connection string

To retrieve the connection string, follow these steps:

  1. Navigate to your workspace, select the Warehouse.
  2. Select the Copy button in the SQL connection string box to copy the connection string to your clipboard.

Or, in the OneLake Data Hub:

  1. Select the Warehouse, and select the ... ellipses for 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.

    Screenshot showing where to select Database Engine on the Connect menu.

  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, via the option Microsoft Entra MFA.

    Screenshot showing the Connect to server window.

  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.

    Screenshot showing where the connected server name appears in the Object Explorer pane.

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. Only SSMS 19 or higher is supported.

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 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 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. Only ODBC 18 or higher versions are supported.

Connect using JDBC

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, then select Download/Update to load all dependencies. For example:

    Screenshot showing where to select Download/Update.

  2. Select Test connection, and Finish.

    Screenshot of the Dependency Declaration tab. 

    <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 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 non-Microsoft tool can also use the SQL connection string via ODBC or OLE DB drivers to connect to a Microsoft Fabric Warehouse or SQL analytics endpoint, using Microsoft Entra ID (formerly Azure Active Directory) authentication. For more information and sample connection strings, see Microsoft Entra authentication as an alternative to SQL 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. For more information and sample connection strings, see Microsoft Entra authentication as an alternative to SQL authentication.

Best practices

We recommend adding retries in your applications/ETL jobs to build resiliency. For more information, see the following docs:

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.
  • If you receive this error "Couldn't complete the operation because we reached a system limit", it's due to the system token size reaching its limit. This issue can be caused if the workspace has too many warehouses/SQL analytics endpoints, if the user is part of too many Microsoft Entra groups, or a combination of the two. We recommend having 40 or fewer warehouses and SQL analytics endpoint per workspace to prevent this error. If the issue persists, contact support.
  • If you receive error code 24804 with the message "Couldn't complete the operation due to a system update. Close out this connection, sign in again, and retry the operation" or error code 6005 with the message "SHUTDOWN is in progress. Execution fail against sql server. Please contact SQL Server team if you need further support.", it's due to temporary connection loss, likely because of a system deployment or reconfiguration. To resolve this issue, sign in again and retry. To learn how to build resiliency and retries in your application, see Best Practices.
  • If you receive the error code 18456: "Execution failed against SQL server, please contact SQL server team if you need further support.", refer to Known issue - Data warehouse connection or query execution fails.
  • Linked server connections from SQL Server are not supported.

Related content