如何用Bicep实现Azure Functions托管身份连接Azure SQL部署配置

核心思路 #

借助Bicep的Microsoft.Resources/deploymentScripts部署脚本资源，搭配托管身份自动执行SQL权限配置语句，替代手动SSMS操作。

步骤1：配置Azure SQL服务器的AD管理员 #

必须先给SQL服务器设置Azure AD管理员，否则无法通过托管身份执行CREATE USER操作。Bicep配置示例：

resource sqlServer 'Microsoft.Sql/servers@2022-05-01-preview' = {
  name: sqlServerName
  location: location
  properties: {
    administratorLogin: sqlAdminLogin
    administratorLoginPassword: sqlAdminPassword
    version: '12.0'
    administrators: {
      administratorType: 'ActiveDirectory'
      login: aadAdminLogin
      sid: aadAdminObjectId
      tenantId: subscription().tenantId
    }
  }
}

步骤2：用部署脚本自动执行SQL配置 #

创建用户托管身份并分配SQL服务器的SQL DB Contributor权限，再通过PowerShell脚本执行用户创建和角色添加操作。

完整Bicep示例片段 #

// 用于执行部署脚本的用户托管身份
resource deploymentScriptIdentity 'Microsoft.ManagedIdentity/userAssignedIdentities@2023-01-31' = {
  name: 'sql-deployment-script-identity'
  location: location
}

// 给身份分配SQL DB Contributor角色（权限足够执行目标SQL操作）
resource sqlServerRoleAssignment 'Microsoft.Authorization/roleAssignments@2022-04-01' = {
  name: guid(sqlServer.id, deploymentScriptIdentity.id, 'b24988ac-6180-42a0-ab88-20f7382dd24c')
  scope: sqlServer
  properties: {
    roleDefinitionId: resourceId('Microsoft.Authorization/roleDefinitions', 'b24988ac-6180-42a0-ab88-20f7382dd24c')
    principalId: deploymentScriptIdentity.properties.principalId
    principalType: 'ServicePrincipal'
  }
}

// 部署脚本：自动执行SQL配置
resource sqlSetupScript 'Microsoft.Resources/deploymentScripts@2023-08-01' = {
  name: 'sql-setup-script'
  location: location
  identity: {
    type: 'UserAssigned'
    userAssignedIdentities: {
      '${deploymentScriptIdentity.id}': {}
    }
  }
  kind: 'AzurePowerShell'
  properties: {
    azPowerShellVersion: '7.4'
    scriptContent: '''
      $serverName = "${sqlServer.name}"
      $databaseName = "${sqlDatabase.name}"
      $functionIdentityId = "${functionApp.identity.principalId}"

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

      # 建立SQL连接
      $connectionString = "Server=tcp:$serverName.database.windows.net,1433;Database=$databaseName;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
      $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
      $connection.AccessToken = $token

      try {
        $connection.Open()
        # 创建托管身份对应的DB用户
        $createUserCmd = New-Object System.Data.SqlClient.SqlCommand("CREATE USER [$functionIdentityId] FROM EXTERNAL PROVIDER", $connection)
        $createUserCmd.ExecuteNonQuery()
        # 添加db_datareader角色
        $addRoleCmd = New-Object System.Data.SqlClient.SqlCommand("ALTER ROLE db_datareader ADD MEMBER [$functionIdentityId]", $connection)
        $addRoleCmd.ExecuteNonQuery()
        Write-Host "数据库权限配置完成"
      }
      catch {
        Write-Error "SQL执行失败: $_"
        throw
      }
      finally {
        $connection.Close()
      }
    '''
    cleanupPreference: 'OnSuccess'
    timeout: 'PT10M'
  }
  dependsOn: [
    sqlServerRoleAssignment
    sqlDatabase
    functionApp
  ]
}

关键注意事项 #

• 权限控制：避免使用过于宽泛的Contributor角色，SQL DB Contributor已满足操作需求。
• 依赖顺序：部署脚本必须依赖SQL数据库、Function App和角色分配完成，确保资源就绪后再执行。
• 安全优势：通过访问令牌连接SQL，无需存储SQL管理员密码，降低泄露风险。
• 异常处理：脚本内置错误捕获，部署失败时可直接定位问题。

替代方案：SQL扩展脚本 #

若偏好直接执行SQL文件，可使用Microsoft.Sql/servers/databases/extensions资源，将SQL脚本存储在存储账户并通过SAS密钥访问：

resource sqlDbExtension 'Microsoft.Sql/servers/databases/extensions@2022-05-01-preview' = {
  parent: sqlDatabase
  name: 'sqlcmd'
  properties: {
    storageKeyType: 'SharedAccessKey'
    storageKey: '<存储账户SAS密钥>'
    storageUri: '<SQL脚本文件URI>'
    administratorLogin: sqlAdminLogin
    administratorLoginPassword: sqlAdminPassword
    operationMode: 'RunOnce'
  }
}

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