Blog Post

Azure Database Support Blog
6 MIN READ

Troubleshooting Azure SQL Managed Instance Permissions with Microsoft Graph

Francisco_do_O's avatar
Nov 17, 2026

A user connects to Azure SQL Managed Instance through SQL Server Management Studio (SSMS) using Microsoft Entra authentication with multifactor authentication (MFA). The sign-in flow completes, but the connection fails:

Login failed for user 'alex@contoso.com'.
Error Number: 18456
State: 1

The user belongs to a user group. The group has permission to connect. An administrator creates an individual login and grants it CONNECT SQL, but the connection still fails.

So... What could be missing?

One possibility is a conflicting permission inherited through another Microsoft Entra group. This article shows how to correlate SQL permissions with direct and nested group memberships using Microsoft Graph.

All identities, group names, object IDs, and database names in this article are fictional examples. The procedure focuses on Microsoft Entra authentication to Azure SQL Managed Instance, not every possible cause of error 18456.

Authentication and authorization answer different questions

These are separate checks:

  • Authentication: Who is connecting? Microsoft Entra ID verifies the identity, including MFA when required.
  • Authorization: What can that identity access? SQL evaluates applicable login, group, role, and database permissions.

A successful MFA interaction does not guarantee SQL access.

For an ordinary, non-sysadmin connection, an applicable DENY CONNECT SQL overrides a GRANT CONNECT SQL, including a grant assigned directly to the individual login.

Consider this example:

alex@contoso.com
  |
  +-- Individual Entra login
  |     GRANT CONNECT SQL
  |
  +-- Application-DBA group
  |     GRANT CONNECT SQL
  |
  +-- Legacy-Application-Restricted group
        DENY CONNECT SQL

Effective server connection permission: denied

Creating an individual Entra login does not remove the user's group memberships or bypass an applicable group deny.

Before you begin

You need:

  • An administrative connection to the affected managed instance, with sufficient permission to read the SQL error log and relevant security metadata.
  • The affected user's user principal name (UPN), the intended database, and a recent failed connection's timestamp and time zone.
  • Authorized directory-read access in the managed instance's Microsoft Entra tenant, or assistance from its Entra administrator.

Run SQL examples in SSMS against the affected managed instance. Run HTTP requests in Microsoft Graph Explorer.

The diagnostic steps are read-only. The remediation examples later in the article change production permissions and require separate approval.

Step 1: Read the server-side failure reason

The client can report a generic state such as 1. Start with the SQL error-log entry for the affected user:

EXEC master.dbo.sp_readerrorlog
    0, 1,
    N'Login failed',
    N'alex@contoso.com';

For this example, the relevant entry is:

Login failed for user 'alex@contoso.com'.
Reason: Token-based server access validation failed with an
infrastructure error. Login lacks Connect SQL permission.

 

Check the entry's timestamp against the actual attempt. Error-log timestamps use the instance's configured time zone; do not assume they are UTC. The first parameter, 0, selects the current log. Use 1, 2, and so on to inspect archived logs if necessary.

Step 2: Inspect grants and denies on the managed instance

The following query shows:

  • The individual login.
  • The expected access group.
  • The public server role.
  • Every explicit server-level DENY CONNECT SQL.

Replace the example identity and group name:

USE master;
GO

DECLARE @UserName sysname = N'alex@contoso.com';
DECLARE @AccessGroup sysname = N'Application-DBA';

SELECT
    p.name AS PrincipalName,
    p.type_desc,
    p.is_disabled,
    perm.permission_name,
    perm.state_desc
FROM sys.server_principals AS p
LEFT JOIN sys.server_permissions AS perm
    ON perm.grantee_principal_id = p.principal_id
   AND perm.class_desc = N'SERVER'
   AND perm.permission_name = N'CONNECT SQL'
WHERE p.name IN (@UserName, @AccessGroup, N'public')
   OR perm.state_desc = N'DENY'
ORDER BY p.name;

A result might include:

PrincipalNametype_descis_disabledpermission_namestate_desc
alex@contoso.comEXTERNAL_LOGIN0CONNECT SQLGRANT
Application-DBAEXTERNAL_GROUP0CONNECT SQLGRANT
Legacy-Application-RestrictedEXTERNAL_GROUP0CONNECT SQLDENY

This is not yet proof that the denied group affects this user. First establish membership.

Also keep these limitations in mind:

  • A null permission value means no matching explicit permission row was returned, not necessarily that the principal has no effective access.
  • Server-role permissions must also be considered. This query is not a complete effective-permission calculation.
  • Catalog visibility depends on the querying account's permissions. Incomplete metadata visibility can hide relevant principals or permissions.
  • Denies on unrelated principals do not affect the user merely because they appear in the results.

Step 3: Extract the object IDs of denied Entra groups

Group names are useful labels, but object IDs provide the identity match.

For Microsoft Entra group server principals on SQL Managed Instance, sid is the binary representation of the group's Entra object ID.

USE master;
GO

SELECT
    p.name AS DeniedGroup,
    CASE
        WHEN DATALENGTH(p.sid) = 16
        THEN CONVERT(uniqueidentifier, p.sid)
    END AS EntraGroupObjectId
FROM sys.server_principals AS p
JOIN sys.server_permissions AS perm
    ON perm.grantee_principal_id = p.principal_id
WHERE p.type = 'X'
  AND DATALENGTH(p.sid) = 16
  AND perm.class_desc = N'SERVER'
  AND perm.permission_name = N'CONNECT SQL'
  AND perm.state_desc = N'DENY'
ORDER BY p.name;

Why these conditions?

  • p.type = 'X' selects external group logins backed by Microsoft Entra ID.
  • DATALENGTH(p.sid) = 16 limits conversion to the 16-byte identifier format expected by uniqueidentifier.
  • The permission filters select explicit server-level connection denies.

Example output:

DeniedGroupEntraGroupObjectId
Legacy-Application-Restricted11111111-2222-4333-8444-555555555555

This deliberately focuses on Entra groups. If the wider inspection found relevant server-role or Windows-principal denies, investigate those paths separately.

Step 4: Retrieve direct and nested memberships with Microsoft Graph

Checking only direct memberships can miss the group that supplies the deny.

The Microsoft Graph transitiveMemberOf relationship returns memberships through nested groups as well as direct memberships.

Use Graph Explorer

  1. Open Microsoft Graph Explorer.
  2. Sign in to the managed instance's Entra tenant. Do not use the sample tenant.
  3. Select GET and API version v1.0.
  4. Paste the following URL into the request URL field.
  5. Leave the request body empty and run the query.
https://graph.microsoft.com/v1.0/users/alex@contoso.com/transitiveMemberOf?$select=id,displayName

The response can contain group memberships and directory-role memberships. Group entries are identified by @odata.type:

{
  "value": [
    {
      "@odata.type": "#microsoft.graph.group",
      "id": "11111111-2222-4333-8444-555555555555",
      "displayName": "Legacy-Application-Restricted"
    }
  ]
}

 

The uncast endpoint above avoids the additional advanced-query requirements associated with some OData casts and filters. It still provides the group memberships needed for this comparison.

Step 5: Match by object ID

Compare:

SQL:   EntraGroupObjectId
Graph: id

For example:

SQL denied group:
11111111-2222-4333-8444-555555555555

User's transitive membership:
11111111-2222-4333-8444-555555555555

Result: same Entra group

GUID letter casing does not affect the match. Do not rely on display names alone.

The matching ID establishes that the user is a member of a group SQL explicitly denies from connecting. Together with the server-side permission error, this identifies a concrete authorization conflict.

Step 6: Correct the intended access policy, not just the symptom

Before changing permissions, determine why the deny exists.

Possible approved resolutions include:

  • Removing an unnecessary direct or nested membership path for the affected user.
  • Removing the group's explicit deny and allowing existing grants to determine access.
  • Granting the group server connection permission if all its applicable members are intended to connect.
  • Redesigning groups so access-granting and access-restricting policies do not unintentionally overlap.

The following SQL statements illustrate alternative changes, not a script to run in sequence.

Alternative A: Remove the explicit group deny

USE master;
GO

REVOKE CONNECT SQL
FROM [Legacy-Application-Restricted];
GO

REVOKE removes the explicit permission entry. It does not itself grant access. Applicable permissions from other principals and roles still determine the result.

Alternative B: Explicitly allow the group to connect

USE master;
GO

GRANT CONNECT SQL
TO [Legacy-Application-Restricted];
GO

This replaces that group's explicit deny with a grant.

Both alternatives can change effective access for every applicable group member, including nested members. Obtain access-owner approval and assess the impact first. Do not remove all denies, grant sysadmin, or broadly allow a restricted group merely as a diagnostic shortcut.

CONNECT SQL permits server connection. It does not automatically grant access to every database or permission to read application data.

Step 7: Verify the result

After an approved correction, retest the connection.

If the connection still fails, collect a fresh timestamp and the latest error-log reason. Remaining applicable denies can still block server access. A different failure reason may require a separate investigation, such as database-user mapping or database permissions.

If no denied-group match is found

Do not conclude that SQL permissions are correct merely because this comparison finds nothing.

Check:

  • Whether the directory response was complete, including all pages and relevant membership visibility.
  • The tenant and user object actually used for the connection.
  • Whether the SQL Entra login's SID matches the intended user's object ID.
  • Disabled logins and applicable server-role or other principal permissions.
  • The requested database, database-user mapping, and database permissions when the failure reason points there.

References

Published Nov 17, 2026
Version 1.0
No CommentsBe the first to comment