Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest Fabric, Power BI, and SQL learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to: ✅ SQL analytics endpoint and Warehouse in Microsoft Fabric
This article covers technical methods that users and customers can employ to transition from SQL authentication to Microsoft Entra authentication within Microsoft Fabric. Microsoft Entra authentication is an alternative to usernames and passwords via SQL authentication for signing in to the SQL analytics endpoint of the lakehouse or the Warehouse in Microsoft Fabric. Microsoft Entra authentication is advisable and vital for creating a secure data platform.
This article focuses on Microsoft Entra authentication as an alternative to SQL authentication in Microsoft Fabric items such as a Warehouse or Lakehouse SQL analytics endpoint.
One of Microsoft Fabric's core principles is secure by design. Microsoft Entra is integral to Microsoft Fabric's security by ensuring strong data protection, governance, and compliance.
Microsoft Entra plays a crucial role in Microsoft Fabric's security for several reasons:
Microsoft Entra adopts a complete Zero Trust policy, offering a superior alternative to traditional SQL authentication limited to usernames and passwords. This approach:
Microsoft Entra authentication for use with a Warehouse or Lakehouse SQL analytics endpoint requires configuration in both Tenant and Workspace settings.
A Fabric admin in your tenant must permit service principal names (SPN) access to Fabric APIs, necessary for the SPN to interface for SQL connection strings to Fabric warehouse or SQL analytics endpoint items.
This setting is located in the Developer settings section and is labeled Service principals can use Fabric APIs. Make sure it is Enabled.
A Fabric admin in your workspace must grant access for a user or SPN to access Fabric items.
There are two means by which a User or SPN can be granted access:
Grant a user or SPN membership to a role: Any workspace role (Admin, Member, Contributor, or Viewer) is sufficient to connect to warehouse or lakehouse items with a SQL connection string.
Assign a user or SPN to a specific item: Grant access to a specific Warehouse or SQL analytics endpoint of a Lakehouse. A Fabric admin can choose from different permission levels.
You can alter the default permissions given to the User or SPN by the system. Use the T-SQL GRANT and DENY commands to alter permissions as required, or ALTER ROLE to add membership to roles.
Currently, SPNs don't have the capability as user accounts for detailed permission configuration with GRANT
/DENY
.
Fabric natively supports authentication and authorization for Microsoft Entra users and service principal names (SPN) in SQL connections to warehouse and SQL analytics endpoint items.
Fabric uses the Tabular Data Stream (TDS) protocol, the same as SQL Server, when you connect with a connection string.
Fabric is compatible with any application or tool able to connect to a product with the SQL Database Engine. Similar to a SQL Server instance connection, TDS operates on TCP port 1433. For more information about Fabric SQL connectivity and finding the SQL connection string, see Connectivity.
A sample SQL connection string looks like: <guid_unique_your_item>.datawarehouse.fabric.microsoft.com
.
Applications and client tools can set the Authentication
connection property in the connection string to choose a Microsoft Entra authentication mode. The following table details the different Microsoft Entra authentication modes, including support for Microsoft Entra multifactor authentication (MFA).
Authentication mode | Scenarios | Comments |
---|---|---|
Microsoft Entra Interactive | Utilized by applications or tools in situations where user authentication can occur interactively, or when it is acceptable to have manual intervention for credential verification. | Activate MFA and Microsoft Entra Conditional Access policies to enforce organizational rules. |
Microsoft Entra Service Principal | Used by apps for secure authentication without human intervention, most suited for application integration. | Advisable to enable Microsoft Entra Conditional Access policies. |
Microsoft Entra Password | When applications can't use SPN-based authentication due to incompatibility, or require a generic username and password for many users, or if other methods are infeasible. | MFA must be off, and no conditional access policies can be set. We recommend validating with the customer's security team before opting for this solution. |
While most of the SQL drivers initially came with support for Microsoft Entra authentication, recent updates have expanded compatibility to include SPN-based authentication. This enhancement simplifies the shift to Microsoft Entra authentication for various applications and tools through driver upgrades and adding support for Microsoft Entra authentication.
However, sometimes it's necessary to adjust additional settings such as enabling certain ports or firewalls to facilitate Microsoft Entra authentication on the host machine.
Applications and tools must upgrade drivers to versions that support Microsoft Entra authentication and add an authentication mode keyword in their SQL connection string, like ActiveDirectoryInteractive
, ActiveDirectoryServicePrincipal
, or ActiveDirectoryPassword
.
Fabric is compatible with Microsoft's native drivers, including OLE DB, Microsoft.Data.SqlClient
, and generic drivers such ODBC and JDBC. The transition for applications to work with Fabric can be managed through reconfiguration to use Microsoft Entra ID-based authentication.
For more information, see Connectivity to data warehousing in Microsoft Fabric.
The OLE DB Driver for SQL Server is a stand-alone data access API designed for OLE DB and first released with SQL Server 2005 (9.x). Since, expanded features include SPN-based authentication with version 18.5.0, adding to the existing authentication methods from earlier versions.
Authentication mode | SQL connection string |
---|---|
Microsoft Entra Interactive | Microsoft Entra interactive authentication |
Microsoft Entra Service Principal | Microsoft Entra Service Principal authentication |
Microsoft Entra Password | Microsoft Entra username and password authentication |
For a C# code snippet using OLE DB with SPN-based authentication, see System.Data.OLEDB.Connect.cs.
The Microsoft ODBC Driver for SQL Server is a single dynamic-link library (DLL) containing run-time support for applications using native-code APIs to connect to SQL Server. It is recommended to use the most recent version for applications to integrate with Fabric.
For more information on Microsoft Entra authentication with ODBC, see Using Microsoft Entra ID with the ODBC Driver sample code.
Authentication Mode | SQL Connection String |
---|---|
Microsoft Entra Interactive | DRIVER={ODBC Driver 18 for SQL Server};SERVER=<SQL Connection String>;DATABASE=<DB Name>;UID=<Client_ID@domain>;PWD=<Secret>;Authentication=ActiveDirectoryInteractive |
Microsoft Entra Service Principal | DRIVER={ODBC Driver 18 for SQL Server};SERVER=<SQL Connection String>;DATABASE=<DBName>;UID=<Client_ID@domain>;PWD=<Secret>;Authentication=ActiveDirectoryServicePrincipal |
Microsoft Entra Password | DRIVER={ODBC Driver 18 for SQL Server};SERVER=<SQL Connection String>;DATABASE=<DBName>;UID=<Client_ID@domain>;PWD=<Secret>;Authentication=ActiveDirectoryPassword |
For a python code snippet using ODBC with SPN-based authentication, see pyodbc-dw-connectivity.py.
The Microsoft JDBC Driver for SQL Server is a Type 4 JDBC driver that provides database connectivity through the standard JDBC application program interfaces (APIs) available on the Java platform.
Starting from version 9.2, mssql-jdbc
introduces support for ActiveDirectoryInteractive
and ActiveDirectoryServicePrincipal
, with ActiveDirectoryPassword
being supported in versions 12.2 and above. This driver requires additional jars as dependencies, which must be compatible with the version of the mssql-driver
used in your
application. For more information, see Feature dependencies of the Microsoft JDBC Driver and Client setup requirement.
Authentication Mode | More information |
---|---|
Microsoft Entra Interactive | Connect using ActiveDirectoryInteractive authentication mode |
Microsoft Entra Service Principal | Connect using ActiveDirectoryServicePrincipal authentication mode |
Microsoft Entra Password | Connect using ActiveDirectoryPassword authentication mode |
For a java code snippet using JDBC with SPN-based authentication, see fabrictoolbox/dw_connect.java and sample pom file pom.xml.
This library Microsoft.Data.SqlClient is the newer, cross-platform data provider for SQL Server, intended to replace the older System.Data.SqlClient
which was Windows-only.
Supported platforms:
Microsoft.Data.SqlClient is a union of the two System.Data.SqlClient
components, providing a set of classes for accessing SQL Database Engine databases. Microsoft.Data.SqlClient
is recommended for all new development.
Authentication Mode | More information |
---|---|
Microsoft Entra Interactive | Using interactive authentication |
Microsoft Entra Service Principal | Using service principal authentication |
Microsoft Entra Password | Using Password Authentication |
Code snippets using SPNs:
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest Fabric, Power BI, and SQL learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayTraining
Module
Use service principal authentication in custom connectors - Training
Learn how to use service principal authentication with custom connectors.
Certification
Microsoft Certified: Fabric Data Engineer Associate - Certifications
As a Fabric Data Engineer, you should have subject matter expertise with data loading patterns, data architectures, and orchestration processes.
Documentation
Connectivity to data warehousing - Microsoft Fabric
Follow steps to connect SSMS to data warehousing in your Microsoft Fabric workspace.
Service principals in Fabric Data Warehouse - Microsoft Fabric
Learn about service principals (SPN) as security identities for applications and tools in Fabric warehouse.
Authentication in SQL database - Microsoft Fabric
Learn about authentication in SQL database in Fabric.