Microsoft Entra authentication as an alternative to SQL authentication

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.

Benefits of Microsoft Entra authentication in Fabric

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:

  • Authentication: Verify users and service principals using Microsoft Entra ID, which grants access tokens for operations within Fabric.
  • Secure access: Connect securely to cloud apps from any device or network, safeguarding requests made to Fabric.
  • Conditional access: Admins can set policies that assess user login context, control access, or enforce extra verification steps.
  • Integration: Microsoft Entra ID seamlessly works with all Microsoft SaaS offerings, including Fabric, allowing easy access across devices and networks.
  • Broad platform: Gain access to Microsoft Fabric with Microsoft Entra ID via any method, whether through the Fabric portal, SQL connection string, REST API, or XMLA endpoint.

Microsoft Entra adopts a complete Zero Trust policy, offering a superior alternative to traditional SQL authentication limited to usernames and passwords. This approach:

Fabric configuration

Microsoft Entra authentication for use with a Warehouse or Lakehouse SQL analytics endpoint requires configuration in both Tenant and Workspace settings.

Tenant setting

A Fabric admin in your tenant must permit 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.

Screenshot from the Fabric portal of the Developer Settings page in Tenant Settings.

Workspace setting

A Fabric admin in your workspace must grant access for a User/SPN to access Fabric items.

There are two means by which a User/SPN can be granted access:

  • Grant a user/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.

    1. In the Manage access option in the Workspace, assign the Contributor role. For more information, see Service roles.
  • Assign a user/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.

    1. Navigate to the relevant Warehouse or SQL analytics endpoint item.
    2. Select More options, then Manage Permissions. Select Add user.
    3. Add the User/SPN on the Grant people access page.
    4. Assign the necessary permissions to a User/SPN. Choose no Additional permissions to grant connect permissions only.

    Screenshot from the Fabric portal of the Grant people access page.

You can alter the default permissions given to the User/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.

Support for user identities and service principal names (SPNs)

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.

  • User identities are the unique credentials for each user within an organization.
  • SPNs represent application objects within a tenant and act as the identity for instances of applications, taking on the role of authenticating and authorizing those applications.

Support for tabular data stream (TDS)

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, see Connectivity.

To obtain the connection string, select More options on a Fabric warehouse or SQL analytics endpoint item.

Screenshot from the Fabric portal of the More options context menu. The Copy SQL connection string option is highlighted.

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.

Flowchart showing Microsoft Entra authentication modes and decision points.

Driver support for Microsoft Entra authentication

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.

Microsoft OLE DB

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.

Microsoft ODBC Driver

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.

Microsoft JDBC Driver

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 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.

Microsoft.Data.SqlClient in .NET Core (C#)

The Microsoft.Data.SqlClient is a data provider for Microsoft SQL Server and Azure SQL Database. It is a union of the two System.Data.SqlClient components that live independently in .NET Framework and .NET Core, providing a set of classes for accessing Microsoft SQL Server databases. Microsoft.Data.SqlClient is recommended for all new and future 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: