Auditing Microsoft Entra ID Authentication in SQL Server

2026-11-14 · by Hannah Vernon · in security

An audit questionnaire asks two things about a SQL Server instance: which accounts can sign in with Microsoft Entra ID, and who the Entra administrator is. Entra ID is Microsoft’s cloud identity service, formerly called Azure Active Directory. SQL Server 2022 is the first on-premises version that accepts Entra sign-ins.^[1] Part of the answer is in the catalog views. The other part, including who the current admin is, is in Azure or the registry.

A DBA with a clipboard checks ID badges in a server cabinet while a separate badge hangs from a cloud outside the window.

This post covers both parts, with an audit query that runs whether Entra is configured or not.

What I Could and Couldn’t Test

I used two instances on one workstation: SQL Server 2019 (15.0.4500.2, Developer) and SQL Server 2025 (17.0.5015.4, Enterprise Developer). Neither one is connected to Azure Arc or set up for Entra authentication, and I don’t have an Entra-joined instance to test against. I can show what the catalog and the parser do on an instance without Entra. Everything I say about a configured instance comes from Microsoft Learn, and the footnotes show where.

How Entra Principals Appear in the Catalog

Entra principals use two type codes. In sys.server_principals, type E is an external login (an Entra user or application) and type X is an external group. The SID of an Entra user or group is the binary form of its Entra object ID. For an Entra application, it’s the binary form of the client ID.^[2] In sys.database_principals, the same two letters mean external user and external group, and authentication_type 4 (EXTERNAL) marks Microsoft Entra authentication.^[3]

A census of server principals by type is the first check:

USE [master];

SELECT
      [sp].[type]
    , [sp].[type_desc]
    , COUNT_BIG(1) AS [principals]
FROM
    [sys].[server_principals] AS [sp]
GROUP BY
      [sp].[type]
    , [sp].[type_desc]
ORDER BY
    [sp].[type];

USE [master];

SELECT

[sp].[type]

, [sp].[type_desc]

, COUNT_BIG(1) AS [principals]

FROM

[sys].[server_principals] AS [sp]

GROUP BY

[sp].[type]

, [sp].[type_desc]

ORDER BY

[sp].[type];

Neither instance returned a row of type E or X. SQL Server 2019 returned types C, G, R, S and U. SQL Server 2025 returned C, R, S and U.

An empty result doesn’t show whether the engine knows about the types, so I read the definitions of the two views with OBJECT_DEFINITION. On both builds, type_desc comes from a lookup in the system base table sys.syspalnames. The sys.database_principals definition on both builds also contains this line, which maps E and X to authentication type 4:

WHEN type = 'E' OR type = 'X' THEN 4

1	WHEN type = 'E' OR type = 'X' THEN 4

System base tables can be read only over the dedicated administrator connection (DAC), a reserved connection for troubleshooting. Learn also says DAC access to them is meant for Microsoft personnel and isn’t a supported customer scenario.^[4] A normal connection got Msg 208, Invalid object name 'sys.syspalnames'. Over the DAC, both builds returned the same lookup rows:

class|value|name
-----|-----|----
DBAT|4|EXTERNAL
LGTY|E|EXTERNAL_LOGIN
LGTY|X|EXTERNAL_GROUP
USTY|E|EXTERNAL_USER
USTY|X|EXTERNAL_GROUP

class|value|name

-----|-----|----

DBAT|4|EXTERNAL

LGTY|E|EXTERNAL_LOGIN

LGTY|X|EXTERNAL_GROUP

USTY|E|EXTERNAL_USER

USTY|X|EXTERNAL_GROUP

So SQL Server 2019 already has names for E and X, even though it can’t create either type. sys.syspalnames is undocumented, so I wouldn’t build a check on it. The Learn page for sys.database_principals lists the group description as EXTERNAL_GROUPS, with an S.^[3] Both builds use EXTERNAL_GROUP. Filter on type, not type_desc.

My 2025 build has a tenant_id column in both views that 2019 doesn’t. I didn’t have 2022 to check. The Learn page for sys.server_principals doesn’t list it. It isn’t NULL on non-Entra logins either. On my SQL Server 2025 instance, every SQL, Windows and certificate-mapped login except sa has an all-zero GUID there. Don’t use it as an Entra filter.

The SID Format

The Azure SQL documentation describes two SID formats for Entra database users. A contained user, created FROM EXTERNAL PROVIDER, has a SID that is the binary object ID. A user created from an Entra login has the object ID followed by the two bytes AADE. The same page gives CAST(sid AS UNIQUEIDENTIFIER) to recover the GUID, and a test of len(sid) = 18 with the AADE suffix to tell the two kinds apart.^[5] The opening paragraph of that page mentions SQL Server, but its Applies to line lists only Azure SQL Database and Managed Instance. I found no SQL Server page that states the same layout, so treat it as unconfirmed on SQL Server.

Without a real Entra SID, I checked the conversion with made-up values on both builds:

label                 | sid                                    | bytes | sid_as_guid
----------------------|----------------------------------------|-------|-------------------------------------
synthetic contained   | 0x67452301AB89EFCD0123456789ABCDEF     | 16    | 01234567-89AB-CDEF-0123-456789ABCDEF
synthetic login-based | 0x67452301AB89EFCD0123456789ABCDEFAADE | 18    | 01234567-89AB-CDEF-0123-456789ABCDEF

label | sid | bytes | sid_as_guid

----------------------|----------------------------------------|-------|-------------------------------------

synthetic contained | 0x67452301AB89EFCD0123456789ABCDEF | 16 | 01234567-89AB-CDEF-0123-456789ABCDEF

synthetic login-based | 0x67452301AB89EFCD0123456789ABCDEFAADE | 18 | 01234567-89AB-CDEF-0123-456789ABCDEF

The first three groups of the GUID are stored with their bytes reversed, and the last two are not. If you copy an object ID from the Entra admin centre and search for it as a hex string, it won’t match. Convert the SID to a GUID first.

Creating an Entra Login Without Entra

CREATE LOGIN ... FROM EXTERNAL PROVIDER maps a login to an Entra principal. Learn describes it as available on Arc-enabled SQL Server 2022 and later. The login name must match the display name of a principal in the tenant, and SQL Server 2025 adds WITH OBJECT_ID for display names that aren’t unique.^[6] I tried both forms, and the contained-user form, with a made-up name. Each statement ran through EXEC inside TRY...CATCH, so a syntax error could be caught. This sample is a write. On an instance that has Entra configured, it would create the login, so don’t run it there unless you want that login:

/* Write: creates a login on an instance where Entra authentication is configured. */
USE [master];

DECLARE @stmt nvarchar(400) = N'CREATE LOGIN [sat_entra@contoso.example] FROM EXTERNAL PROVIDER;';

BEGIN TRY
    EXEC (@stmt);
END TRY
BEGIN CATCH
    SELECT
          ERROR_NUMBER() AS [error_number]
        , ERROR_STATE() AS [error_state]
        , ERROR_MESSAGE() AS [error_message];
END CATCH;

/* Write: creates a login on an instance where Entra authentication is configured. */

USE [master];

DECLARE @stmt nvarchar(400) = N'CREATE LOGIN [sat_entra@contoso.example] FROM EXTERNAL PROVIDER;';

BEGIN TRY

EXEC (@stmt);

END TRY

BEGIN CATCH

SELECT

ERROR_NUMBER() AS [error_number]

, ERROR_STATE() AS [error_state]

, ERROR_MESSAGE() AS [error_message];

END CATCH;

On SQL Server 2025, every statement failed with error 37525 at severity 16. Both CREATE LOGIN forms returned this message:

Msg 37525, Level 16, State 1, Line 1
Command 'CREATE LOGIN FROM EXTERNAL PROVIDER' is not supported as Azure Active Directory is not configured for this instance.

1 2	Msg 37525, Level 16, State 1, Line 1 Command 'CREATE LOGIN FROM EXTERNAL PROVIDER' is not supported as Azure Active Directory is not configured for this instance.

The CREATE USER ... FROM EXTERNAL PROVIDER form, run in a scratch database, returned the same message with CREATE USER in place of CREATE LOGIN. The message still uses the old product name.

On SQL Server 2019, all three failed to parse. CREATE LOGIN and CREATE USER returned Msg 102, Incorrect syntax near 'PROVIDER', and the WITH OBJECT_ID form returned Incorrect syntax near 'OBJECT_ID'. Afterward, neither instance had a login or user whose name started with sat_entra.

Azure Arc and the Entra Admin

Learn describes two ways to set up Entra on an on-premises SQL Server 2022 or later. The main one registers the host and the instance with Azure Arc, which needs the Azure Connected Machine agent and the Azure extension for SQL Server. An instance on an Azure VM uses the SQL IaaS Agent extension instead. Failover cluster instances aren’t supported.^[1] The second way, on Windows only, skips Arc. You install a certificate, register an application in Entra ID, and add values under the instance’s MSSQLServer\FederatedAuthentication registry key.^[7]

The SQL Server 2025 Arc setup page uses a primary managed identity instead of an app registration and certificate. Its manual setup, an alternative to the portal, writes values under the same MSSQLServer\FederatedAuthentication key on an Arc-enabled 2025 instance. One of those values is AADServerAdminSid.^[8] On a configured instance, that value may record the admin, but I haven’t seen one to check.

The FederatedAuthentication key doesn’t exist for either of my instances. sys.dm_server_registry returned only its usual keys (CurrentVersion, Parameters, network protocols, Agent and services) on both builds. I can’t say whether it would list FederatedAuthentication on an instance where the key exists.

With Arc, you choose the Entra admin in the Azure portal or with the CLI, PowerShell or an ARM template. The Arc agent then makes that account a sysadmin login. From the setup tutorial, three points matter for an audit:^[9]

When you change the admin, the previous admin login isn’t removed. It stays in sysadmin until someone drops it.
The new admin doesn’t appear in sys.server_principals until the instance restarts. Until then the catalog shows the old one.
The current admin is shown in the Azure portal.

So sys.server_principals can’t tell you who the admin is right now. An Entra login in sysadmin may be the current admin, a former one, or a login someone added by hand. The same tutorial says its portal steps apply to SQL Server 2022 and not to SQL Server 2025. For 2025, use the managed identity page cited above.

An Audit Query That Runs Either Way

This script lists Entra logins with their server roles, then every Entra user and group in each database the caller can open, with database roles. It runs on SQL Server 2019 as well as 2022 and later, and it returns empty results where Entra isn’t in use. Seeing every login requires ALTER ANY LOGIN or a permission on each login.^[2] Seeing every database user requires ALTER ANY USER or a permission on each user.^[3] The script skips databases that aren’t ONLINE and databases the caller can’t open. Run it as a member of sysadmin, and check sys.databases for anything that was skipped.

USE [master];

SET NOCOUNT ON;

/* Server level: Entra logins and groups, with server role membership. */
SELECT
      [sp].[name]
    , [sp].[type_desc]
    , CONVERT(uniqueidentifier, SUBSTRING([sp].[sid], 1, 16)) AS [sid_as_guid]
    , DATALENGTH([sp].[sid]) AS [sid_bytes]
    , [sp].[is_disabled]
    , [sp].[create_date]
    , [roles].[server_roles]
FROM
    [sys].[server_principals] AS [sp]
OUTER APPLY
(
    SELECT
        STRING_AGG(CONVERT(nvarchar(4000), [r].[name]), N', ') AS [server_roles]
    FROM
        [sys].[server_role_members] AS [srm]
    INNER JOIN
        [sys].[server_principals] AS [r]
            ON [r].[principal_id] = [srm].[role_principal_id]
    WHERE
        [srm].[member_principal_id] = [sp].[principal_id]
) AS [roles]
WHERE
    [sp].[type] IN ('E', 'X')
ORDER BY
      [sp].[type]
    , [sp].[name];

/* Database level: Entra users and groups in every database the caller can open. */
CREATE TABLE #db_principals
(
      [database_name]            sysname        NOT NULL
    , [user_name]                sysname        NOT NULL
    , [type]                     char(1)        NOT NULL
    , [type_desc]                nvarchar(60)   NULL
    , [authentication_type_desc] nvarchar(60)   NULL
    , [sid]                      varbinary(85)  NULL
    , [create_date]              datetime       NULL
    , [database_roles]           nvarchar(4000) NULL
);

DECLARE @database_name sysname;
DECLARE @rows          int;
DECLARE @sql           nvarchar(max);
DECLARE @cur           CURSOR;

SET @cur = CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
    SELECT
        [d].[name]
    FROM
        [sys].[databases] AS [d]
    WHERE
        [d].[state_desc] = N'ONLINE'
        AND HAS_DBACCESS([d].[name]) = 1
    ORDER BY
        [d].[name];

OPEN @cur;
SET @rows = @@CURSOR_ROWS;

WHILE @rows > 0
BEGIN
    FETCH NEXT FROM @cur INTO @database_name;

    SET @sql = N'USE ' + QUOTENAME(@database_name) + N';
        SELECT
              DB_NAME()
            , [dp].[name]
            , [dp].[type]
            , [dp].[type_desc]
            , [dp].[authentication_type_desc]
            , [dp].[sid]
            , [dp].[create_date]
            , [roles].[database_roles]
        FROM
            [sys].[database_principals] AS [dp]
        OUTER APPLY
        (
            SELECT
                STRING_AGG(CONVERT(nvarchar(4000), [r].[name]), N'', '') AS [database_roles]
            FROM
                [sys].[database_role_members] AS [drm]
            INNER JOIN
                [sys].[database_principals] AS [r]
                    ON [r].[principal_id] = [drm].[role_principal_id]
            WHERE
                [drm].[member_principal_id] = [dp].[principal_id]
        ) AS [roles]
        WHERE
            [dp].[type] IN (''E'', ''X'')
            OR [dp].[authentication_type] = 4;';

    INSERT INTO #db_principals
    (
          [database_name]
        , [user_name]
        , [type]
        , [type_desc]
        , [authentication_type_desc]
        , [sid]
        , [create_date]
        , [database_roles]
    )
    EXEC [sys].[sp_executesql] @sql;

    SET @rows = @rows - 1;
END;

CLOSE @cur;
DEALLOCATE @cur;

SELECT
      [p].[database_name]
    , [p].[user_name]
    , [p].[type_desc]
    , [p].[authentication_type_desc]
    , CONVERT(uniqueidentifier, SUBSTRING([p].[sid], 1, 16)) AS [sid_as_guid]
    , CASE
          WHEN DATALENGTH([p].[sid]) = 18
              AND SUBSTRING([p].[sid], 17, 2) = 0xAADE THEN N'login-based (SID ends AADE)'
          WHEN DATALENGTH([p].[sid]) = 16 THEN N'contained (16-byte SID)'
          ELSE N'unrecognized SID length'
      END AS [user_kind_from_sid]
    , CASE
          WHEN EXISTS
          (
              SELECT
                  1
              FROM
                  [sys].[server_principals] AS [sp]
              WHERE
                  [sp].[type] IN ('E', 'X')
                  AND SUBSTRING([sp].[sid], 1, 16) = SUBSTRING([p].[sid], 1, 16)
          ) THEN N'yes'
          ELSE N'no'
      END AS [external_login_with_same_guid]
    , [p].[database_roles]
    , [p].[create_date]
FROM
    #db_principals AS [p]
ORDER BY
      [p].[database_name]
    , [p].[user_name];

DROP TABLE #db_principals;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

USE [master];

SET NOCOUNT ON;

/* Server level: Entra logins and groups, with server role membership. */

SELECT

[sp].[name]

, [sp].[type_desc]

, CONVERT(uniqueidentifier, SUBSTRING([sp].[sid], 1, 16)) AS [sid_as_guid]

, DATALENGTH([sp].[sid]) AS [sid_bytes]

, [sp].[is_disabled]

, [sp].[create_date]

, [roles].[server_roles]

FROM

[sys].[server_principals] AS [sp]

OUTER APPLY

(

SELECT

STRING_AGG(CONVERT(nvarchar(4000), [r].[name]), N', ') AS [server_roles]

FROM

[sys].[server_role_members] AS [srm]

INNER JOIN

[sys].[server_principals] AS [r]

ON [r].[principal_id] = [srm].[role_principal_id]

WHERE

[srm].[member_principal_id] = [sp].[principal_id]

) AS [roles]

WHERE

[sp].[type] IN ('E', 'X')

ORDER BY

[sp].[type]

, [sp].[name];

/* Database level: Entra users and groups in every database the caller can open. */

CREATE TABLE #db_principals

(

[database_name] sysname NOT NULL

, [user_name] sysname NOT NULL

, [type] char(1) NOT NULL

, [type_desc] nvarchar(60) NULL

, [authentication_type_desc] nvarchar(60) NULL

, [sid] varbinary(85) NULL

, [create_date] datetime NULL

, [database_roles] nvarchar(4000) NULL

);

DECLARE @database_name sysname;

DECLARE @rows int;

DECLARE @sql nvarchar(max);

DECLARE @cur CURSOR;

SET @cur = CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR

SELECT

[d].[name]

FROM

[sys].[databases] AS [d]

WHERE

[d].[state_desc] = N'ONLINE'

AND HAS_DBACCESS([d].[name]) = 1

ORDER BY

[d].[name];

OPEN @cur;

SET @rows = @@CURSOR_ROWS;

WHILE @rows > 0

BEGIN

FETCH NEXT FROM @cur INTO @database_name;

SET @sql = N'USE ' + QUOTENAME(@database_name) + N';

SELECT

DB_NAME()

, [dp].[name]

, [dp].[type]

, [dp].[type_desc]

, [dp].[authentication_type_desc]

, [dp].[sid]

, [dp].[create_date]

, [roles].[database_roles]

FROM

[sys].[database_principals] AS [dp]

OUTER APPLY

(

SELECT

STRING_AGG(CONVERT(nvarchar(4000), [r].[name]), N'', '') AS [database_roles]

FROM

[sys].[database_role_members] AS [drm]

INNER JOIN

[sys].[database_principals] AS [r]

ON [r].[principal_id] = [drm].[role_principal_id]

WHERE

[drm].[member_principal_id] = [dp].[principal_id]

) AS [roles]

WHERE

[dp].[type] IN (''E'', ''X'')

OR [dp].[authentication_type] = 4;';

INSERT INTO #db_principals

(

[database_name]

, [user_name]

, [type]

, [type_desc]

, [authentication_type_desc]

, [sid]

, [create_date]

, [database_roles]

)

EXEC [sys].[sp_executesql] @sql;

SET @rows = @rows - 1;

END;

CLOSE @cur;

DEALLOCATE @cur;

SELECT

[p].[database_name]

, [p].[user_name]

, [p].[type_desc]

, [p].[authentication_type_desc]

, CONVERT(uniqueidentifier, SUBSTRING([p].[sid], 1, 16)) AS [sid_as_guid]

, CASE

WHEN DATALENGTH([p].[sid]) = 18

AND SUBSTRING([p].[sid], 17, 2) = 0xAADE THEN N'login-based (SID ends AADE)'

WHEN DATALENGTH([p].[sid]) = 16 THEN N'contained (16-byte SID)'

ELSE N'unrecognized SID length'

END AS [user_kind_from_sid]

, CASE

WHEN EXISTS

(

SELECT

FROM

[sys].[server_principals] AS [sp]

WHERE

[sp].[type] IN ('E', 'X')

AND SUBSTRING([sp].[sid], 1, 16) = SUBSTRING([p].[sid], 1, 16)

) THEN N'yes'

ELSE N'no'

END AS [external_login_with_same_guid]

, [p].[database_roles]

, [p].[create_date]

FROM

#db_principals AS [p]

ORDER BY

[p].[database_name]

, [p].[user_name];

DROP TABLE #db_principals;

Notes on the choices in it:

The GUID column is called sid_as_guid, not object ID, because an Entra application’s SID holds its client ID.^[2]
The login match compares only the first 16 bytes, the part that both Learn SID descriptions share. The Entra server principals page says, “To correlate the user back to its login, the AADE suffix must first be removed.”^[10] I couldn’t confirm either layout on SQL Server.
On SQL Server, read user_kind_from_sid as a hint, since the AADE rule comes from the Azure SQL page.
The script skips tenant_id so that it still compiles on SQL Server 2019.

On my instances the script scanned 23 databases on SQL Server 2019 and 19 on SQL Server 2025, and both result sets were empty. Empty results mean no Entra principals, not that Entra is off. An instance set up without Arc can have the registry values in place and no Entra logins yet.

A database user with external_login_with_same_guid = no and an 18-byte SID is worth checking the same way you’d check an orphaned user. I covered the SID mechanics in Logins, SIDs, and Kerberos from First Principles, Part 1: What a SID Actually Is and the usual cleanup in Finding and Fixing Orphaned Users. Contained users have no login by design, so no is expected for them.

Where Each Answer Lives

Question	Where to look
Which Entra users, groups and applications have logins or database users	The audit query above
Who the current Entra admin is on SQL Server 2022 with Arc	Azure portal, because `sys.server_principals` shows the old admin until a restart
Whether Entra authentication is configured	Arc resource in the Azure portal, or values under the instance’s `FederatedAuthentication` registry key (written by the setup without Arc and by the manual SQL Server 2025 Arc setup)
Who is a member of an Entra group with a login	Entra ID, because the catalog holds one row for the group
Who the Entra admin is on Azure SQL Database	`az sql server ad-admin list` or `Get-AzSqlServerActiveDirectoryAdministrator`
Who the Entra admin is on Azure SQL Managed Instance	`az sql mi ad-admin list` or `Get-AzSqlInstanceActiveDirectoryAdministrator`

Group membership has the same gap as Windows groups, which I covered in Logins, SIDs, and Kerberos from First Principles, Part 5: Windows Groups and the Invisible Members.

Azure SQL Database and Managed Instance

On Azure SQL Database and Managed Instance, Entra authentication needs an Entra admin on the logical server or managed instance. The admin is a single setting, and it can be a group. Removing it turns off all Entra sign-ins. With Microsoft Entra-only authentication turned on, SQL authentication is off, and so is Windows authentication on Managed Instance.^[5] The admin is stored as a user in the master database.^[5]^[11] The Learn pages I read don’t describe anything in the catalog that marks that user as the admin, or that shows the Entra-only setting. They document the cmdlets and CLI commands in the table above for reading the admin, so use those.

The Learn pages disagree on server-level Entra logins for Azure SQL Database. The sys.server_principals page still lists types E and X as in preview there.^[2] The Entra authentication overview and the Entra server principals page, both dated 2026-10-07, say the logins are generally available for Azure SQL Database, Managed Instance and SQL Server 2022 and later.^[5]^[10]

The database-level part of the audit query switches databases with USE inside dynamic SQL. Azure SQL Database doesn’t support USE to switch to another database, and returns error 40508 instead.^[12] On Managed Instance it should run as is. On Azure SQL Database, you’d run the inner SELECT in each database yourself. I didn’t run it on either service.

Try It Yourself

If an instance you look after is set up for Entra, compare its sysadmin Entra logins with the admin the Azure portal shows. If you have a SQL Server 2022 or 2025 instance with Entra configured, I’d like to know what the SID of an Entra login looks like, and whether it ends in AADE.

Have you audited Entra logins on SQL Server, or found something the catalog didn’t show? Find me on Bluesky or LinkedIn.

References

Microsoft Entra authentication for SQL Server overview - Microsoft Learn. States that Entra authentication starts with SQL Server 2022, describes setup with Azure Arc, the Azure VM alternative and the setup without Arc, and says failover cluster instances aren’t supported. ↩
sys.server_principals (Transact-SQL) - Microsoft Learn. Defines types E and X, the SID of Entra users, groups and applications, the permission needed to see other logins, and where E and X are available. ↩
sys.database_principals (Transact-SQL) - Microsoft Learn. Defines database principal types E and X and authentication_type 4 (EXTERNAL), lists the group description as EXTERNAL_GROUPS, and says seeing other users requires ALTER ANY USER or a permission on the user. ↩
System Base Tables - Microsoft Learn. States that binding to a system base table requires the DAC, and that DAC access to them is designed only for Microsoft personnel and isn’t a supported customer scenario. ↩
Microsoft Entra Authentication - Azure SQL Database & Azure SQL Managed Instance - Microsoft Learn. Describes the Entra admin and that it’s a contained database user in master, the SID formats for contained and login-based users (with the AADE suffix), Microsoft Entra-only authentication, and says Entra logins are generally available for Azure SQL Database, Managed Instance and SQL Server 2022 and later. ↩
CREATE LOGIN (Transact-SQL) - Microsoft Learn. Documents FROM EXTERNAL PROVIDER for Arc-enabled SQL Server 2022 and later, the display-name rule, and WITH OBJECT_ID for SQL Server 2025. ↩
Enable Microsoft Entra authentication for SQL Server on Windows without Azure Arc - Microsoft Learn. The setup without Arc, including the FederatedAuthentication registry key and its values. ↩
Set Up Managed Identity and Microsoft Entra Authentication for SQL Server Enabled by Azure Arc - Microsoft Learn. Applies to SQL Server 2025. Sets up Entra with the primary managed identity, and its manual option writes FederatedAuthentication registry values, including AADServerAdminSid. ↩
Set up Microsoft Entra authentication with app registration for SQL Server - Microsoft Learn. States that the admin becomes sysadmin, that a replaced admin keeps its login, that sys.server_principals shows the new admin only after a restart, and that the portal steps apply to SQL Server 2022 and not 2025. ↩
Microsoft Entra server principals - Azure SQL Database & Azure SQL Managed Instance - Microsoft Learn. Says Entra logins are generally available for Azure SQL Database, Managed Instance and SQL Server 2022 and later, and that the AADE suffix must be removed to correlate a user with its login. ↩
Configure Microsoft Entra Authentication - Azure SQL Database & SQL Managed Instance - Microsoft Learn. Says the Entra admin is stored in master as a user, and lists the PowerShell cmdlets and Azure CLI commands (including az sql server ad-admin list and az sql mi ad-admin list) that return the Entra admin for SQL Database and SQL Managed Instance. ↩
USE (Transact-SQL) - Microsoft Learn. States that in Azure SQL Database, USE can refer only to the current database and returns error 40508 for any other. ↩

Tags: auditing, azure-arc, azure-sql, logins, microsoft-entra-id, security, SQL Server, SQL Server 2022, SQL Server 2025

Auditing Microsoft Entra ID Authentication in SQL Server

What I Could and Couldn’t Test

How Entra Principals Appear in the Catalog

The SID Format

Creating an Entra Login Without Entra

Azure Arc and the Entra Admin

An Audit Query That Runs Either Way

Where Each Answer Lives

Azure SQL Database and Managed Instance

Try It Yourself

References

Like this:

Related

Let us know your thoughts on this post Click here to cancel reply.

Search

Categories

Pages

Meta

Auditing Microsoft Entra ID Authentication in SQL Server

What I Could and Couldn’t Test

How Entra Principals Appear in the Catalog

The SID Format

Creating an Entra Login Without Entra

Azure Arc and the Entra Admin

An Audit Query That Runs Either Way

Where Each Answer Lives

Azure SQL Database and Managed Instance

Try It Yourself

References

Share this:

Like this:

Related

Let us know your thoughts on this post Click here to cancel reply.

Search

Categories

Pages

Meta