如何无需密码实现Azure App Service与Azure SQL的身份验证？

前提条件 #

• 已部署Azure SQL Server及目标数据库
• 已部署Azure App Service（需启用系统分配MSI或用户分配MSI）
• 本地/构建服务器已安装：
  • Az PowerShell模块
  • dbatools模块
  • Azure CLI（可选，用于辅助获取令牌）

步骤1：配置Azure SQL Server的AD管理员 #

创建AD身份的外部用户前，必须先给SQL Server指定Azure AD管理员（否则无权限操作AD身份相关的SQL对象）。

Bicep示例（嵌入现有SQL Server定义） #

resource sqlServer 'Microsoft.Sql/servers@2022-05-01-preview' = {
  name: sqlServerName
  location: location
  properties: {
    administratorLogin: 'sqladmin' // 仅保留，实际不使用SQL认证
    administrators: {
      administratorType: 'ActiveDirectory'
      login: 'your-ad-admin-upn@domain.com' // 你的Azure AD管理员UPN
      sid: 'your-ad-admin-object-id' // 管理员的Azure AD对象ID
      tenantId: subscription().tenantId
    }
  }
}

PowerShell示例（Az模块） #

$resourceGroupName = "your-resource-group"
$sqlServerName = "your-sql-server-name"
$adAdminUpn = "admin@your-domain.com"
$adAdminObjectId = "your-admin-object-id"

Set-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName $resourceGroupName `
  -ServerName $sqlServerName `
  -DisplayName $adAdminUpn `
  -ObjectId $adAdminObjectId

步骤2：获取App Service的MSI标识 #

要给App Service的MSI创建SQL外部用户，需先获取MSI的UPN（系统分配MSI的UPN格式为{app-service-name}@{tenant-id}.onmicrosoft.com）或对象ID。

Bicep输出MSI信息（部署App Service时添加） #

resource appService 'Microsoft.Web/sites@2022-03-01' = {
  name: appServiceName
  location: location
  identity: {
    type: 'SystemAssigned' // 若用用户分配MSI，需指定userAssignedIdentities
  }
  // 其他App Service配置...
}

// 输出MSI关键信息
output appServiceMsiObjectId string = appService.identity.principalId
output appServiceMsiUpn string = '${appService.name}@${subscription().tenantId}.onmicrosoft.com'

PowerShell获取MSI信息 #

$resourceGroupName = "your-resource-group"
$appServiceName = "your-app-service-name"

$appService = Get-AzWebApp -ResourceGroupName $resourceGroupName -Name $appServiceName
$msiUpn = "$appServiceName@$((Get-AzContext).Tenant.Id).onmicrosoft.com"

步骤3：本地/构建服务器上创建SQL外部用户 #

整合令牌获取、SQL连接与用户创建流程，确保本地和构建服务器都能执行（构建服务器需用服务主体登录Azure）。

完整PowerShell脚本（dbatools实现） #

# 登录Azure：本地用交互式登录，构建服务器用服务主体登录
# 构建服务器执行示例：
# Connect-AzAccount -ServicePrincipal -Tenant $tenantId -ApplicationId $appId -CertificateThumbprint $thumbprint
Connect-AzAccount

# 配置参数
$resourceGroupName = "your-resource-group"
$sqlServerName = "your-sql-server-name"
$databaseName = "your-db-name"
$appServiceName = "your-app-service-name"

# 获取SQL Server完整域名
$sqlServer = Get-AzSqlServer -ResourceGroupName $resourceGroupName -ServerName $sqlServerName
$sqlFqdn = "$($sqlServer.FullyQualifiedDomainName),1433"

# 获取App Service MSI的UPN
$appService = Get-AzWebApp -ResourceGroupName $resourceGroupName -Name $appServiceName
$msiUpn = "$appServiceName@$((Get-AzContext).Tenant.Id).onmicrosoft.com"

# 获取SQL Server的Azure AD访问令牌
$token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token

# 连接SQL并创建外部用户
$connectParams = @{
  SqlInstance = $sqlFqdn
  Database = $databaseName
  AccessToken = $token
}

# 创建外部用户
Invoke-DbaQuery @connectParams -Query "CREATE USER [$msiUpn] FROM EXTERNAL PROVIDER;"

# 分配数据库权限（按需调整，示例为读写权限）
Invoke-DbaQuery @connectParams -Query @"
ALTER ROLE db_datareader ADD MEMBER [$msiUpn];
ALTER ROLE db_datawriter ADD MEMBER [$msiUpn];
"@

运行注意事项 #

• 本地环境：确保当前登录Azure的用户是SQL Server的AD管理员，或被授予ALTER ANY USER权限
• 构建服务器：需提前配置Azure服务主体，且该主体需拥有SQL Server的AD管理员权限，或被授予SQL Server的ALTER ANY USER权限；同时预安装Az和dbatools模块

步骤4：配置App Service用MSI访问SQL #

在App Service的连接字符串中指定MSI认证方式，无需密码。

Bicep示例（添加到App Service配置） #

resource appService 'Microsoft.Web/sites@2022-03-01' = {
  name: appServiceName
  location: location
  identity: {
    type: 'SystemAssigned'
  }
  properties: {
    siteConfig: {
      connectionStrings: [
        {
          name: 'DefaultConnection'
          connectionString: "Server=tcp:${sqlServer.fullyQualifiedDomainName},1433;Initial Catalog=${databaseName};Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication=Active Directory Managed Identity;"
          type: 'SQLAzure'
        }
      ]
    }
  }
  // 其他App Service配置...
}

常见问题排查 #

• 令牌无效：检查Get-AzAccessToken的ResourceUrl是否为https://database.windows.net，且当前Azure上下文用户有SQL Server访问权限
• 创建用户失败：确认SQL Server已配置AD管理员，且执行语句的用户拥有ALTER ANY USER权限
• App Service连接失败：检查MSI是否被授予数据库的相应权限，连接字符串中的Authentication=Active Directory Managed Identity是否正确

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