Azure DevOps流水线服务主体通过AAD组访问SQL Server失败

将名为Azure_SqlServer_Admins的AAD组设置为Azure SQL Server的Microsoft Entra管理员，组内服务主体DevEnvSP用于Azure DevOps流水线执行SQL脚本（该SP已具备订阅级Contributor角色及Directory Read权限），但两种执行方式均报错Login failed for user ''。

方式一：使用SqlAzureDacpacDeployment任务 #

- task: SqlAzureDacpacDeployment@1
  displayName: 'name'
  inputs:
    azureSubscription: ${{ variables.subscriptionName }}
    AuthenticationType: 'servicePrincipal'
    ServerName: ${{ variables.sqlServerName }}.database.windows.net
    DatabaseName: ${{ parameters.sqlDbName }}
    IpDetectionMethod: 'AutoDetect'
    deployType: 'InlineSqlTask'
    SqlInline: my sql code

执行后报错：Login failed for user ''

方式二：通过AccessToken调用PowerShell #

- task: AzurePowerShell@5
  displayName: Creating user for ${{ parameters.sqlDbName }}
  inputs:
    azureSubscription: ${{ variables.subscriptionName }}
    azurePowerShellVersion: 'LatestVersion'
    ScriptType: 'InlineScript'
    FailOnStandardError: true
    Inline: |
        $token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token

        $CmdText = @"
            My SQL query
        "@
        
        Invoke-SqlCmd -ServerInstance "${{ variables.sqlServerName }}.database.windows.net" `
                      -Database "DbTest" `
                      -AccessToken $token `
                      -Query $CmdText
                      -Verbose
                      -OutputSqlErrors $true

仍出现相同错误

手动创建数据库用户 #

DECLARE @UMIUser NVARCHAR(100) = 'SP name';
IF DATABASE_PRINCIPAL_ID(@UMIUser) IS NULL
BEGIN
  CREATE USER [@UMIUser] FROM EXTERNAL PROVIDER;
  ALTER ROLE db_datareader ADD MEMBER [@UMIUser];
  ALTER ROLE db_datawriter ADD MEMBER [@UMIUser];
  ALTER ROLE db_ddladmin ADD MEMBER [@UMIUser];
END

执行后无效果

1. 修正令牌资源URL的格式 #

Azure SQL的资源ID需要包含末尾斜杠，修改PowerShell脚本中的Get-AzAccessToken参数：

$token = (Get-AzAccessToken -ResourceUrl https://database.windows.net/).Token

2. 修复数据库用户创建的变量引用错误 #

原SQL语句中变量引用方式错误，需使用动态SQL拼接服务主体名称，或者直接指定服务主体的显示名称/对象ID：

方式A：使用服务主体显示名称 #

DECLARE @UMIUser NVARCHAR(100) = 'DevEnvSP'; -- 替换为实际服务主体显示名称
IF DATABASE_PRINCIPAL_ID(@UMIUser) IS NULL
BEGIN
  DECLARE @CreateUserCmd NVARCHAR(MAX) = N'CREATE USER [' + @UMIUser + N'] FROM EXTERNAL PROVIDER;';
  EXEC sp_executesql @CreateUserCmd;
  ALTER ROLE db_datareader ADD MEMBER [DevEnvSP];
  ALTER ROLE db_datawriter ADD MEMBER [DevEnvSP];
  ALTER ROLE db_ddladmin ADD MEMBER [DevEnvSP];
END

方式B：使用服务主体对象ID（更可靠） #

DECLARE @SPObjectId NVARCHAR(100) = '替换为服务主体的对象ID';
IF DATABASE_PRINCIPAL_ID(@SPObjectId) IS NULL
BEGIN
  CREATE USER [@SPObjectId] FROM EXTERNAL PROVIDER;
  ALTER ROLE db_datareader ADD MEMBER [@SPObjectId];
  ALTER ROLE db_datawriter ADD MEMBER [@SPObjectId];
  ALTER ROLE db_ddladmin ADD MEMBER [@SPObjectId];
END

3. 确认AAD组权限同步及成员有效性 #

• 检查Azure Portal中SQL Server的Entra管理员设置，确保Azure_SqlServer_Admins组已正确添加且状态正常
• 等待10-15分钟，AAD组权限到SQL Server的同步存在延迟
• 验证DevEnvSP确实属于Azure_SqlServer_Admins组：在Entra ID的组成员列表中确认

4. 优化SqlAzureDacpacDeployment任务配置 #

添加TenantId参数确保任务获取正确的租户上下文：

- task: SqlAzureDacpacDeployment@1
  displayName: 'Execute SQL Script'
  inputs:
    azureSubscription: ${{ variables.subscriptionName }}
    AuthenticationType: 'servicePrincipal'
    ServerName: ${{ variables.sqlServerName }}.database.windows.net
    DatabaseName: ${{ parameters.sqlDbName }}
    IpDetectionMethod: 'AutoDetect'
    deployType: 'InlineSqlTask'
    SqlInline: |
      -- 你的SQL代码
    TenantId: ${{ variables.tenantId }} -- 添加租户ID变量

5. 验证服务主体的数据库级权限 #

订阅级Contributor角色不直接赋予数据库操作权限，需确保服务主体（或所属AAD组）在数据库层面拥有对应权限：

• 执行SELECT * FROM sys.database_principals WHERE name = 'DevEnvSP';确认用户已创建
• 执行EXEC sp_addrolemember 'db_owner', 'DevEnvSP';直接赋予数据库所有者权限（测试用，生产环境按需调整）

1. 手动测试令牌连接：在本地用服务主体获取令牌，执行Invoke-SqlCmd并添加-Debug参数，查看令牌的受众和声明是否正确
2. 检查服务器级主体：在SQL Server中执行SELECT * FROM sys.server_principals WHERE name = 'Azure_SqlServer_Admins';确认组已被识别

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