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:
- Prevents user impersonation.
- Enables fine-grained access control considering user identity, environment, devices, etc.
- Supports advanced security like Microsoft Entra multifactor authentication.
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.
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.
- 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.
- Navigate to the relevant Warehouse or SQL analytics endpoint item.
- Select More options, then Manage Permissions. Select Add user.
- Add the User/SPN on the Grant people access page.
- Assign the necessary permissions to a User/SPN. Choose no Additional permissions to grant connect permissions only.
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.
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. |
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: