如何通过Bicep为SQL Server分配含用户分配标识的多Entra管理员

你不需要依赖Entra ID组来实现多主体的数据库访问授权，直接通过数据库级别的权限分配即可，而且可以完全集成到CI/CD流水线中，不需要修改任何Entra组。以下是具体实现方式：

核心思路 #

SQL Server的Entra ID管理员只能设置一个主体，但你不需要把团队成员或用户分配标识都设为服务器管理员。只需通过SQL语句直接在数据库层面给目标主体授予对应权限（比如建表、CRUD所需的db_ddladmin+db_datawriter+db_datareader，或者更宽泛的db_owner），就能实现访问需求。

由于你的SP拥有订阅Contributor权限，它可以通过Azure AD身份认证连接到SQL Server，执行这些权限分配的SQL命令。

具体实现步骤 #

1. 调整Bicep模板：完善服务器与数据库配置 #

首先确保你的SQL Server启用Azure AD唯一认证，同时创建目标数据库。可以保留用户分配标识作为服务器管理员（方便后续自动化操作），也可以换成一个固定的Entra管理员（比如团队的运维账号）：

param userAssignedIdentityName string
param databaseServerName string
param resourceNameSuffix string
param location string
param sqlAdministratorPassword string
param databaseName string = 'your-db-name'

// 引用现有用户分配标识
resource userAssignedIdentity 'Microsoft.ManagedIdentity/userAssignedIdentities@2023-01-31' existing = {
  name: userAssignedIdentityName
}

// 创建SQL Server，启用Azure AD唯一认证
resource databaseServer 'Microsoft.Sql/servers@2023-05-01-preview' = {
  name: '${toLower(databaseServerName)}-${resourceNameSuffix}'
  location: location
  properties: {
    administratorLoginPassword: sqlAdministratorPassword
    version: '12.0'
    minimalTlsVersion: '1.2'
    publicNetworkAccess: 'Enabled'
    administrators: {
      administratorType: 'ActiveDirectory'
      principalType: 'Application'
      login: userAssignedIdentity.name
      sid: userAssignedIdentity.properties.principalId
      tenantId: tenant().tenantId
      azureADOnlyAuthentication: true
    }
    restrictOutboundNetworkAccess: 'Disabled'
  }
}

// 创建目标数据库
resource sqlDatabase 'Microsoft.Sql/servers/databases@2023-05-01-preview' = {
  parent: databaseServer
  name: databaseName
  location: location
  sku: {
    name: 'GP_Gen5_2' // 根据实际需求调整SKU
    tier: 'GeneralPurpose'
  }
}

2. 自动化分配数据库权限（两种可选方式） #

方式一：在Bicep中内嵌SQL脚本执行授权

利用Microsoft.Sql/servers/databases/sqlScripts资源，在数据库部署完成后自动执行权限分配SQL：

// 定义需要授权的团队成员和用户分配标识
param teamMemberEmails array = ['member1@yourdomain.com', 'member2@yourdomain.com']

// 生成SQL授权语句
var grantSql = concat(
  // 给用户分配标识授予权限
  'CREATE USER [${userAssignedIdentity.name}] FROM EXTERNAL PROVIDER;',
  'ALTER ROLE db_owner ADD MEMBER [${userAssignedIdentity.name}];',
  // 给每个团队成员授予权限
  join(
    teamMemberEmails,
    (email) => concat('CREATE USER [${email}] FROM EXTERNAL PROVIDER; ALTER ROLE db_datareader ADD MEMBER [${email}]; ALTER ROLE db_datawriter ADD MEMBER [${email}]; ALTER ROLE db_ddladmin ADD MEMBER [${email}];')
  )
)

// 部署SQL脚本执行权限分配
resource sqlPermissionsScript 'Microsoft.Sql/servers/databases/sqlScripts@2022-05-01-preview' = {
  parent: sqlDatabase
  name: 'grant-permissions'
  properties: {
    scriptContent: grantSql
    continueOnError: false
  }
}

方式二：在CI/CD流水线中执行SQL命令

如果更倾向于在流水线阶段单独处理权限（比如权限列表需要动态获取），可以用Azure CLI执行以下命令（SP会自动用自身身份认证到SQL Server）：

# 设置变量
SERVER_NAME="your-sql-server-name"
DATABASE_NAME="your-db-name"
USER_ASSIGNED_IDENTITY_NAME="your-identity-name"
TEAM_MEMBERS=("member1@yourdomain.com" "member2@yourdomain.com")

# 给用户分配标识授权
az sql db execute-query \
  --server $SERVER_NAME \
  --database $DATABASE_NAME \
  --query-text "CREATE USER [$USER_ASSIGNED_IDENTITY_NAME] FROM EXTERNAL PROVIDER; ALTER ROLE db_owner ADD MEMBER [$USER_ASSIGNED_IDENTITY_NAME];"

# 给每个团队成员授权
for MEMBER in "${TEAM_MEMBERS[@]}"
do
  az sql db execute-query \
    --server $SERVER_NAME \
    --database $DATABASE_NAME \
    --query-text "CREATE USER [$MEMBER] FROM EXTERNAL PROVIDER; ALTER ROLE db_datareader ADD MEMBER [$MEMBER]; ALTER ROLE db_datawriter ADD MEMBER [$MEMBER]; ALTER ROLE db_ddladmin ADD MEMBER [$MEMBER];"
done

关键注意事项 #

• 权限粒度：根据实际需求调整角色，比如如果团队成员不需要建表权限，可以去掉db_ddladmin，只保留db_datareader和db_datawriter。
• SP权限：确保SP的IP在SQL Server的防火墙允许列表中，或者开启“允许Azure服务和资源访问此服务器”，否则SP无法连接执行SQL。
• 认证方式：由于启用了azureADOnlyAuthentication，所有连接都必须用Azure AD身份，传统SQL账号会被禁用。

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