Azure DevOps发布管道如何通过AAD免密认证执行数据库SQL脚本

该方案完全适配你开启了「仅支持Azure Active Directory身份验证」的SQL实例配置，全程无需硬编码或存储用户名、密码。

1. 配置管道访问用的AAD身份 #

二选一即可，优先推荐第一个：

• 自动服务主体：在Azure DevOps项目设置中新建Azure Resource Manager类型服务连接，选择自动创建服务主体模式，授权范围覆盖你存放SQL实例的订阅/资源组，保存后在Azure AD企业应用列表中找到该服务主体，记录其名称。
• 用户分配托管标识（UAMI）：单独创建一个UAMI专门用于数据库部署，在DevOps服务连接中配置使用该UAMI的身份做资源访问，适合需要跨多个项目复用数据库部署身份的场景。

2. 给AAD身份授予数据库操作权限 #

用你自己的MFA账号登录目标SQL数据库，执行以下SQL语句完成授权，遵循最小权限原则即可：

-- 将<>内替换为你上一步选的服务主体/UAMI的名称
CREATE USER [<devops-deploy-identity>] FROM EXTERNAL PROVIDER;
-- 授予DDL变更权限，足够执行schema更新操作
ALTER ROLE db_ddladmin ADD MEMBER [<devops-deploy-identity>];
-- 若脚本包含数据初始化操作，可按需追加数据写入权限，纯schema更新无需配置
-- ALTER ROLE db_datawriter ADD MEMBER [<devops-deploy-identity>];

3. 配置发布管道执行步骤 #

在发布管道中添加Azure CLI任务，选择你之前创建的Azure Resource Manager服务连接，脚本类型选PowerShell，填入以下执行脚本，替换对应参数即可：

# 替换为你的实际配置
$sqlServerFqdn = "<your-sql-instance>.database.windows.net"
$targetDbName = "<your-database-name>"
$sqlScriptLocalPath = "$(System.DefaultWorkingDirectory)/<artifact-path>/schema-update.sql"

# 从当前CLI登录上下文获取Azure SQL的临时访问令牌，无持久化凭据
$accessToken = az account get-access-token --resource https://database.windows.net --query accessToken -o tsv

# 执行SQL脚本
Invoke-SqlCmd -ServerInstance $sqlServerFqdn `
              -Database $targetDbName `
              -AccessToken $accessToken `
              -InputFile $sqlScriptLocalPath `
              -ConnectionTimeout 60 `
              -ErrorAction Stop

配置注意事项 #

• 若使用自托管代理，提前在代理机器安装最新版SqlServer PowerShell模块，低于21.1.18256版本的模块不支持-AccessToken参数；微软托管代理默认预装符合要求的模块版本，无需额外配置。
• 不要为了方便给部署身份授予db_owner或服务器级管理员权限，db_ddladmin已覆盖所有schema变更的操作需求。
• 若需要做发布管控，可以在执行SQL前增加脚本校验步骤，拦截DROP TABLE、TRUNCATE TABLE等高危操作。

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