Authentication in SQL database in Microsoft Fabric
Applies to: ✅ SQL database in Microsoft Fabric
This article explains authentication for SQL databases.
Like other Microsoft Fabric item types, SQL databases rely on Microsoft Entra authentication.
To successfully authenticate to a SQL database, a Microsoft Entra user, a service principal, or their group, must have the Read item permission for the database in Fabric. For information on how to grant a Microsoft Entra identity access to a Fabric workspace or a specific database, see Fabric access controls.
To find the connection string to your SQL database in Fabric, see Connect to your SQL database in Microsoft Fabric.
Note
To enable service principals to connect to Fabric and to SQL databases, you also need to enable the Service principals can use Fabric APIs Fabric tenant setting. To learn how to enable tenant settings, see Fabric Tenant settings.
Connect to a SQL database using Microsoft Entra authentication
You can connect to a database using Microsoft Entra authentication with:
- SQL tools that support Microsoft Entra authentication, including SQL Server Management Studio and the mssql extension with Visual Studio Code.
- Applications that use SQL client drivers supporting Microsoft Entra authentication, including SqlClient, JDBC, ODBC, and OLE DB.
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.
Create database users for Microsoft Entra identities
If you plan to configure SQL access controls with Transact-SQL, you first need to create database users corresponding to your Microsoft Entra identities - users, service principals, or their groups - with CREATE USER (Transact-SQL).
Creating database users isn't required if you use Fabric access controls (workspace roles or item permissions). You don't need to create users when you manage SQL database-level roles from Fabric portal either - the portal automatically creates users when needed.
Create database users when connected as a Microsoft Entra user
When you're connected to your database as a Microsoft Entra user, you should use CREATE USER with the FROM EXTERNAL PROVIDER clause to create users for Microsoft Entra principals. FROM EXTERNAL PROVIDER validates the specified principal name with Microsoft Entra, retrieves the principal identifier (user's or group's object ID, application ID, or client ID), and stores the identifier as user's security identifier (SID) in SQL metadata. You must be a member of the Directory Readers role in Microsoft Entra when using the FROM EXTERNAL PROVIDER clause. The following sample T-SQL scripts use FROM EXTERNAL PROVIDER to create a user based on a Microsoft Entra user, a service principal in Microsoft Entra, or a group in Microsoft Entra.
-- Create a user for a Microsoft Entra user
CREATE USER [alice@contoso.com] FROM EXTERNAL PROVIDER;
-- Create a user for a service principal in Microsoft Entra
CREATE USER [HRApp] FROM EXTERNAL PROVIDER;
-- Create a user for a group in Microsoft Entra
CREATE USER [HR] FROM EXTERNAL PROVIDER;
Create database users when connected as a Microsoft Entra service principal
When an application is connected to a database with a service principal, the application must issue CREATE USER with the SID and TYPE clauses to create users for Microsoft Entra principals. The specified principal name isn't validated in Microsoft Entra. It's a responsibility of the application (application developer) to provide a valid name and a valid SID and a user object type.
If the specified principal is a user or a group in Microsoft Entra, the SID must be an object ID of that user or group in Microsoft Entra. If the specified principal is a service principal in Microsoft Entra, the SID must be an application ID (client ID) of the service principal in Microsoft Entra. Object IDs and application IDs (client IDs) obtained from Microsoft Entra must be converted to binary(16).
The value of the TYPE argument must be:
E- if the specified Microsoft Entra principal is a user or a service principal.X- if the specified Microsoft Entra principal is a group.
The following T-SQL example script creates a database user for the Microsoft Entra user, named bob@contoso.com, setting the SID of the new user to the object ID of the Microsoft Entra user. The unique identifier of the user's object ID is converted and then concatenated into a CREATE USER statement. Replace <unique identifier sid> with the user's object ID in Microsoft Entra.
DECLARE @principal_name SYSNAME = 'bob@contoso.com';
DECLARE @objectId UNIQUEIDENTIFIER = '<unique identifier sid>'; -- user's object ID in Microsoft Entra
-- Convert the guid to the right type
DECLARE @castObjectId NVARCHAR(MAX) = CONVERT(VARCHAR(MAX), CONVERT (VARBINARY(16), @objectId), 1);
-- Construct command: CREATE USER [@principal_name] WITH SID = @castObjectId, TYPE = E;
DECLARE @cmd NVARCHAR(MAX) = N'CREATE USER [' + @principal_name + '] WITH SID = ' + @castObjectId + ', TYPE = E;'
EXEC (@cmd);
The following example creates a database user for the Microsoft Entra service principal, named HRApp, setting the SID of the new user to the client ID of the service principal in Microsoft Entra.
DECLARE @principal_name SYSNAME = 'HRApp';
DECLARE @clientId UNIQUEIDENTIFIER = '<unique identifier sid>'; -- principal's client ID in Microsoft Entra
-- Convert the guid to the right type
DECLARE @castClientId NVARCHAR(MAX) = CONVERT(VARCHAR(MAX), CONVERT (VARBINARY(16), @clientId), 1);
-- Construct command: CREATE USER [@principal_name] WITH SID = @castClientId, TYPE = E;
DECLARE @cmd NVARCHAR(MAX) = N'CREATE USER [' + @principal_name + '] WITH SID = ' + @castClientId + ', TYPE = E;'
EXEC (@cmd);
The following example creates a database user for the Microsoft Entra group, named HR, setting the SID of the new user to the object ID of the group.
DECLARE @group_name SYSNAME = 'HR';
DECLARE @objectId UNIQUEIDENTIFIER = '<unique identifier sid>'; -- principal's object ID in Microsoft Entra
-- Convert the guid to the right type
DECLARE @castObjectId NVARCHAR(MAX) = CONVERT(VARCHAR(MAX), CONVERT (VARBINARY(16), @objectId), 1);
-- Construct command: CREATE USER [@groupName] WITH SID = @castObjectId, TYPE = X;
DECLARE @cmd NVARCHAR(MAX) = N'CREATE USER [' + @principal_name + '] WITH SID = ' + @castObjectId + ', TYPE = X;'
EXEC (@cmd);
Limitations
- Microsoft Entra ID is the only identity provider SQL database in Fabric supports. Specifically, SQL authentication isn't supported.
- Logins (server principals) aren't supported.