如何在Azure DevOps管道中使用MSI访问Azure SQL Server并插入数据？

1. 为Azure DevOps管道身份配置SQL数据库权限 #

首先要确保执行管道的身份（Azure DevOps服务连接对应的服务主体/托管标识）拥有数据库的插入权限：

• 登录Azure SQL数据库（用SSMS或Azure门户的查询编辑器），执行以下SQL语句：

  -- 1. 基于DevOps服务主体/托管标识的对象ID创建登录
  CREATE LOGIN [DevOps-SP-or-MSI-Name] FROM EXTERNAL PROVIDER;
  -- 2. 在目标数据库中创建用户
  CREATE USER [DevOps-SP-or-MSI-User] FOR LOGIN [DevOps-SP-or-MSI-Name];
  -- 3. 赋予插入权限（可以用db_datawriter角色，或更细粒度的权限）
  ALTER ROLE db_datawriter ADD MEMBER [DevOps-SP-or-MSI-User];
  -- 或者仅赋予特定表的INSERT权限
  -- GRANT INSERT ON [dbo].[YourTargetTable] TO [DevOps-SP-or-MSI-User];
  

• 注意替换语句中的DevOps-SP-or-MSI-Name为你的Azure DevOps服务连接对应的服务主体名称或托管标识名称，YourTargetTable为目标表名。

2. 配置Azure DevOps管道 #

根据需求选择合适的任务实现插入操作：

选项1：使用Azure SQL Database Deployment任务 #

这是最简便的内置任务：

• 在管道中添加Azure SQL Database Deployment任务
• 配置以下参数：
  • Azure Subscription：选择已授权的Azure服务连接（确保该连接的身份已完成步骤1的权限配置）
  • Server Name：填写你的Azure SQL Server名称（格式：xxx.database.windows.net）
  • Database Name：选择目标数据库
  • SQL Script：上传或指定包含INSERT语句的SQL脚本文件，例如：

    INSERT INTO [dbo].[YourTargetTable] (Column1, Column2)
    VALUES ('Value1', 'Value2'), ('Value3', 'Value4');
    

  • Authentication Type：选择Service Principal（如果服务连接用的是服务主体），任务会自动使用服务连接的托管标识完成身份验证

选项2：使用PowerShell任务自定义脚本 #

如果需要更灵活的逻辑，用PowerShell任务：

• 在管道中添加Azure PowerShell任务（关联已授权的Azure服务连接）
• 编写PowerShell脚本，通过MSI获取令牌连接数据库并执行插入：

  # 获取SQL数据库的访问令牌
  $token = (Get-AzAccessToken -ResourceUrl https://database.windows.net/).Token
  # 构建连接字符串
  $serverName = "<your-sql-server>.database.windows.net"
  $dbName = "<your-database>"
  $connectionString = "Server=tcp:$serverName,1433;Database=$dbName;"
  # 建立连接并执行插入
  $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
  $connection.AccessToken = $token
  $connection.Open()
  
  $insertQuery = @"
  INSERT INTO [dbo].[YourTargetTable] (Column1, Column2)
  VALUES ('Value1', 'Value2'), ('Value3', 'Value4')
  "@
  
  $command = $connection.CreateCommand()
  $command.CommandText = $insertQuery
  $command.ExecuteNonQuery()
  
  $connection.Close()
  

• 替换脚本中的<your-sql-server>、<your-database>和YourTargetTable为实际信息

3. 验证管道执行结果 #

• 运行管道，查看任务日志确认无报错
• 登录SQL数据库，执行SELECT语句验证目标表中是否已插入预期记录

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