如何在YAML流水线中连接Azure SQL DB并执行权限分配？

核心实现思路 #

借助Azure CLI内置的az sql db execute命令，直接在流水线步骤中完成Azure SQL数据库的连接与指定SQL语句执行，依托流水线服务连接的身份（服务主体/系统分配托管标识）进行Azure AD认证，无需手动管理SQL账号密码。

前置要求 #

• 流水线使用的服务主体（或系统分配托管标识）需具备：
  • Azure SQL数据库的db_owner角色权限（或至少拥有创建外部用户、修改角色成员的权限）
  • 已完成AAD组创建及托管标识添加的操作权限（此部分你已实现）

YAML流水线完整步骤示例 #

steps:
# 你已完成的AAD组创建及托管标识添加步骤
- task: AzureCLI@2
  displayName: '创建AAD组并关联托管标识'
  inputs:
    azureSubscription: '<你的Azure服务连接名称>'
    scriptType: 'bash'
    scriptLocation: 'inlineScript'
    inlineScript: |
      # 替换为你的实际组及托管标识信息
      az ad group create --display-name "SQL-Reader-Group" --mail-nickname "sql-reader-group"
      az ad group member add --group "SQL-Reader-Group" --member-id "<托管标识对象ID>"

# 新增的SQL权限分配步骤
- task: AzureCLI@2
  displayName: '为AAD标识分配SQL数据库权限'
  inputs:
    azureSubscription: '<你的Azure服务连接名称>'
    scriptType: 'bash'
    scriptLocation: 'inlineScript'
    inlineScript: |
      # 定义资源变量，替换为你的实际信息
      SQL_SERVER="your-sql-server-name"
      SQL_DB="target-db-name"
      IDENTITY_NAME="SQL-Reader-Group"

      # 执行权限分配SQL语句
      az sql db execute \
        --server $SQL_SERVER \
        --database $SQL_DB \
        --querytext "CREATE USER [$IDENTITY_NAME] FROM EXTERNAL PROVIDER; ALTER ROLE db_datareader ADD MEMBER [$IDENTITY_NAME];"

关键细节说明 #

• 身份认证：AzureCLI任务会自动复用服务连接的身份完成Azure AD认证，无需额外传入SQL账号密码，提升安全性。
• 变量管理：建议将资源名称（如SQL服务器名、数据库名）配置为流水线变量，避免硬编码，便于后续维护。
• 权限排查：若执行时报权限错误，可先通过以下CLI命令为服务主体配置SQL服务器AD管理员权限：

  az sql server ad-admin create --server-name <SQL服务器名称> --resource-group <资源组名称> --display-name "<服务主体显示名>" --object-id "<服务主体对象ID>"
  

备选方案：使用sqlcmd工具执行复杂脚本 #

如果需要执行多段复杂SQL脚本，可使用sqlcmd结合Azure AD认证：

- task: AzureCLI@2
  displayName: '用sqlcmd执行SQL权限配置'
  inputs:
    azureSubscription: '<你的Azure服务连接名称>'
    scriptType: 'bash'
    scriptLocation: 'inlineScript'
    inlineScript: |
      SQL_SERVER_FQDN="your-sql-server-name.database.windows.net"
      SQL_DB="target-db-name"
      IDENTITY_NAME="SQL-Reader-Group"

      # 通过Azure AD认证连接SQL并执行命令
      sqlcmd -S $SQL_SERVER_FQDN -d $SQL_DB -G -Q "CREATE USER [$IDENTITY_NAME] FROM EXTERNAL PROVIDER; ALTER ROLE db_datareader ADD MEMBER [$IDENTITY_NAME];"

注：微软托管代理默认已预装sqlcmd，自托管代理需自行安装该工具。

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