Azure SQL使用AAD MFA登录报18456错误 同组用户可正常登录

你遇到的Login failed for user ''、Microsoft SQL Server, Error: 18456以及提示<token-identified principal>本质是同一类错误：Azure SQL校验你提交的AAD访问令牌时，找不到任何和令牌中声明的身份匹配的、拥有登录权限的数据库主体。你虽然在被授权的AAD组内，但出现同组其他成员可正常登录、仅你登录失败的情况，基本是以下几个原因导致：

• 令牌缓存了旧的组归属声明：这是90%以上同类问题的诱因。AAD访问令牌签发时会把当前用户所属的组列表写入令牌声明，如果你是在组被映射为数据库授权用户之后才加入该组，本地缓存的旧令牌（SSMS、Windows身份组件都会缓存AAD令牌）没有包含你属于该授权组的声明，Azure SQL校验时自然无法识别你的组权限，直接拒绝登录。同组其他成员早于你加入组，登录时拿到的是包含组声明的新令牌，因此可以正常访问。
• 组嵌套层级超出Azure SQL支持范围：Azure SQL对AAD嵌套组的支持有层级限制，用户身份场景下最多支持2层嵌套。如果你是通过嵌套在子组内的方式间接加入顶层授权组，且嵌套层级超过限制，令牌中不会携带顶层授权组的ID声明，SQL无法匹配到对应的授权主体。
• 授权组类型不符合要求：如果数据库中映射的授权组是Microsoft 365组、通讯组而非安全组，部分场景下AAD不会向Azure SQL返回完整的组成员声明，会出现随机成员无法识别权限的问题（这类问题通常伴随同组部分成员可登录、部分不可的现象）。
• 授权组的对象ID不匹配：如果该AAD组曾经被删除后重建，数据库中存储的组用户SID对应的是旧组的对象ID，组重建后新加入的成员会无法匹配权限，早期加入的成员如果令牌缓存了旧组的声明可能暂时还能登录，后续缓存失效后也会报错。

你单独执行CREATE USER ... FROM EXTERNAL PROVIDER创建个人用户后可以正常登录，是因为这种方式直接将你的个人AAD主体ID映射为数据库用户，不再依赖令牌中的组归属声明做权限匹配，绕开了上述所有组映射、令牌缓存的问题。

不需要给个人单独建库用户（这种方式会提升后续权限运维成本，不符合AAD授权最佳实践），按以下顺序排查即可：

1. 清理本地令牌缓存：完全关闭所有SSMS窗口，在任务管理器中结束所有SSMS、Microsoft Authentication Hub相关的后台进程，再打开Windows「凭据管理器」，删除所有和目标AAD域名、Azure SQL相关的保存凭据，重新打开SSMS走完整MFA验证流程拿新令牌登录，绝大多数场景下这一步就能解决问题。
2. 找数据库管理员执行以下SQL，确认库内映射的外部主体信息：

   SELECT name, sid, type_desc, create_date
   FROM sys.database_principals
   WHERE type = 'X' -- X代表AAD外部主体，包含外部用户、外部组
   

   核对返回的组条目对应的SID（SID实际是AAD对象ID的格式转换结果）是否和你所在的授权组当前的AAD对象ID一致，排除组删重建导致ID不匹配的问题。
3. 确认被映射的授权组是AAD安全组，而非M365组、通讯组；同时确认你是该组的直接成员，不存在超过2层的嵌套关系。

注：如果排查完以上项仍有问题，可以在登录失败时，让管理员在Azure SQL的审计日志中查看error_state字段：18456错误状态为13代表令牌中没有可匹配的权限主体，状态为12代表令牌有效但权限不足，可以进一步缩小排查范围。

内容的提问来源于stack exchange，提问作者learner