如何通过PowerShell使用服务主体认证连接Azure Synapse专用SQL池并部署SQL脚本

一、使用Azure AD服务主体认证并部署 #

1. 前置准备 #

• 确认服务主体已拥有Azure Synapse专用SQL池的db_owner或足够执行部署脚本的权限
• 安装所需PowerShell模块：

  Install-Module -Name Az -Force -AllowClobber
  Install-Module -Name SqlServer -Force -AllowClobber
  

2. 获取服务主体访问令牌 #

# 填入服务主体信息
$tenantId = "你的Azure租户ID"
$clientId = "服务主体应用ID"
$clientSecret = "服务主体密钥"

# 登录服务主体
Connect-AzAccount -ServicePrincipal -Tenant $tenantId -ApplicationId $clientId -Credential (ConvertTo-SecureString $clientSecret -AsPlainText -Force)

# 获取针对Synapse SQL的访问令牌
$token = (Get-AzAccessToken -ResourceUrl "https://database.windows.net/").Token

3. 执行SQL脚本部署 #

# 填入Synapse SQL池信息和脚本路径
$serverName = "你的synapse服务器名.database.windows.net"
$databaseName = "专用SQL池名称"
$sqlScriptPath = "C:\scripts\your-deploy-script.sql"

# 执行脚本
Invoke-Sqlcmd -ServerInstance $serverName -Database $databaseName -AccessToken $token -InputFile $sqlScriptPath -QueryTimeout 3600

二、使用托管标识认证并部署 #

1. 系统托管标识（适用于Azure VM/Function等已启用系统标识的环境） #

步骤1：配置权限

在Azure Synapse专用SQL池中，将系统托管标识添加为db_owner或对应权限的数据库用户。

步骤2：获取令牌并执行脚本

# 获取系统托管标识的访问令牌
$token = (Get-AzAccessToken -ResourceUrl "https://database.windows.net/").Token

# 执行脚本
$serverName = "你的synapse服务器名.database.windows.net"
$databaseName = "专用SQL池名称"
$sqlScriptPath = "C:\scripts\your-deploy-script.sql"

Invoke-Sqlcmd -ServerInstance $serverName -Database $databaseName -AccessToken $token -InputFile $sqlScriptPath -QueryTimeout 3600

2. 用户托管标识 #

步骤1：配置权限

确保用户托管标识已创建，并被授予Synapse SQL池的对应权限。

步骤2：登录并执行脚本

# 填入用户托管标识的资源ID
$managedIdentityId = "/subscriptions/你的订阅ID/resourceGroups/资源组名/providers/Microsoft.ManagedIdentity/userAssignedIdentities/标识名称"

# 登录用户托管标识
Connect-AzAccount -Identity -AccountId $managedIdentityId

# 获取令牌并执行脚本
$token = (Get-AzAccessToken -ResourceUrl "https://database.windows.net/").Token

$serverName = "你的synapse服务器名.database.windows.net"
$databaseName = "专用SQL池名称"
$sqlScriptPath = "C:\scripts\your-deploy-script.sql"

Invoke-Sqlcmd -ServerInstance $serverName -Database $databaseName -AccessToken $token -InputFile $sqlScriptPath -QueryTimeout 3600

注意事项 #

• 要求SqlServer模块版本≥21.1.18256，该版本开始支持-AccessToken参数
• 可根据脚本复杂度调整-QueryTimeout的超时值（单位：秒）
• 遵循最小权限原则，避免给服务主体/托管标识分配超出需求的权限

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