如何通过IaC自动化为用户分配托管标识授予Azure SQL数据库读写权限？

问题概述 #

通过GitHub Actions（使用联合凭据实现无密钥认证）+Bicep/Terraform从零创建Azure SQL Server、SQL数据库、用户分配托管标识后，执行以下SQL脚本进行权限授予时失败：

CREATE USER [MyManagedIdentity] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [MyManagedIdentity];
ALTER ROLE db_datawriter ADD MEMBER [MyManagedIdentity];

失败核心原因是Azure SQL Server无法读取Azure AD中的托管标识信息。手动将SQL Server加入Azure AD内置的「目录读取者」角色可解决问题，但需避免针对大量数据库重复手动操作；同时要求全程无密钥，拒绝使用AD账号密码作为GitHub密钥的方案。

核心原因 #

执行CREATE USER FROM EXTERNAL PROVIDER语句时，Azure SQL Server需要读取Azure AD中的标识信息以验证身份合法性。默认情况下，SQL Server的系统分配托管标识不具备此AD读取权限，且GitHub Actions使用的服务主体即使拥有目录读取者权限，也无法替代SQL Server自身完成AD信息读取操作。

可行解决方案（仅需少量初始化手动操作） #

1. 一次性手动配置：创建Azure AD自定义角色与自动分配规则 #

• 创建最小权限的Azure AD自定义角色，仅包含所需的AD读取权限：ServicePrincipal.Read.All（用于读取用户分配托管标识），按需可添加User.Read.All。
• 创建Azure AD动态安全组，设置成员规则自动包含所有新创建的Azure SQL Server系统分配托管标识，示例规则：

  (servicePrincipalType eq 'ManagedIdentity') and (startsWith(displayName, 'sql-'))
  

  可根据你的SQL Server命名前缀调整规则内容。
• 将自定义角色分配给该动态安全组，分配范围设为Azure AD租户或目标订阅。此后新创建的SQL Server系统标识会自动加入组并获得AD读取权限。

2. IaC中配置SQL Server启用系统分配托管标识 #

在Bicep/Terraform代码中为Azure SQL Server启用系统分配托管标识，示例Bicep代码：

resource sqlServer 'Microsoft.Sql/servers@2023-02-01-preview' = {
  name: sqlServerName
  location: location
  properties: {
    administratorLogin: sqlAdminLogin
    administratorLoginPassword: sqlAdminPassword
    version: '12.0'
  }
  identity: {
    type: 'SystemAssigned'
  }
}

3. GitHub Actions工作流中执行SQL脚本 #

利用GitHub Actions的联合凭据身份，通过Azure CLI调用az sql db execute-script命令执行授权脚本，示例工作流片段：

- name: Azure Login (Federated Credentials)
  uses: azure/login@v1
  with:
    client-id: ${{ secrets.AZURE_CLIENT_ID }}
    tenant-id: ${{ secrets.AZURE_TENANT_ID }}
    subscription-id: ${{ secrets.AZURE_SUBSCRIPTION_ID }}

- name: Run Permission Grant Script
  run: |
    az sql db execute-script \
      --resource-group ${{ env.RESOURCE_GROUP }} \
      --server ${{ env.SQL_SERVER_NAME }} \
      --database ${{ env.SQL_DB_NAME }} \
      --file ./sql/grant-permissions.sql

替代方案（若动态组不可用） #

如果无法使用Azure AD动态组，可在IaC中添加步骤：创建SQL Server后，通过Azure CLI或Bicep将其系统分配托管标识加入预先手动创建的Azure AD安全组（该组已被授予目录读取者权限）。此步骤无需后续手动操作，仅需预先完成安全组与角色分配的初始化配置。

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