如何通过ARM脚本将Azure AD组添加到Azure SQL数据库

算力阿峰

2026-5-29

配置Azure SQL弹性池及Azure AD认证的ARM脚本方案

当然可以直接在ARM脚本里完成Azure AD配置和AD组用户的添加！我帮你梳理了具体的实现方案，包括修改后的模板和关键要点：

现有部署脚本（已扩展Azure AD功能）

主模板文件（azure.deploy.ps1）

{
  "$schema": "http://schema.management.azure.com/schemas/2014-04-01-preview/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "administratorLogin": {
      "type": "string",
      "metadata": {
        "description": "The SQL Server administrator login"
      }
    },
    "administratorLoginPassword": {
      "type": "securestring",
      "metadata": {
        "description": "The SQL Server administrator login password."
      }
    },
    "serverName": {
      "type": "string",
      "metadata": {
        "description": "The SQL Server name."
      }
    },
    "elasticPoolName": {
      "type": "string",
      "metadata": {
        "description": "The Elastic Pool name."
      }
    },
    "edition": {
      "type": "string",
      "defaultValue": "Standard",
      "allowedValues": [
        "Basic",
        "Standard",
        "Premium"
      ],
      "metadata": {
        "description": "The Elastic Pool edition."
      }
    },
    "poolDtu": {
      "type": "int",
      "metadata": {
        "description": "The Elastic Pool DTU."
      }
    },
    "databaseDtuMin": {
      "type": "int",
      "defaultValue": 0,
      "metadata": {
        "description": "The Elastic Pool database DTU min."
      }
    },
    "databaseDtuMax": {
      "type": "int",
      "metadata": {
        "description": "The Elastic Pool database DTU max."
      }
    },
    "databasesNames": {
      "type": "array",
      "defaultValue": [
        "db1",
        "db2"
      ],
      "metadata": {
        "description": "The SQL Databases names."
      }
    },
    "databaseCollation": {
      "type": "string",
      "defaultValue": "SQL_Latin1_General_CP1_CI_AS",
      "metadata": {
        "description": "The SQL Database collation."
      }
    },
    "location": {
      "type": "string",
      "defaultValue": "[resourceGroup().location]",
      "metadata": {
        "description": "Location for all resources."
      }
    },
    "aadAdminLogin": {
      "type": "string",
      "metadata": {
        "description": "Azure AD administrator login name (user or group UPN)"
      }
    },
    "aadAdminObjectId": {
      "type": "string",
      "metadata": {
        "description": "Azure AD administrator object ID"
      }
    },
    "aadGroupName": {
      "type": "string",
      "metadata": {
        "description": "Azure AD group name to add as database user"
      }
    },
    "aadGroupObjectId": {
      "type": "string",
      "metadata": {
        "description": "Azure AD group object ID"
      }
    }
  },
  "variables": {
    "sqlServerFqdn": "[reference(parameters('serverName')).fullyQualifiedDomainName]"
  },
  "resources": [
    {
      "apiVersion": "2014-04-01-preview",
      "location": "[parameters('location')]",
      "name": "[parameters('serverName')]",
      "properties": {
        "administratorLogin": "[parameters('administratorLogin')]",
        "administratorLoginPassword": "[parameters('administratorLoginPassword')]",
        "version": "12.0"
      },
      "type": "Microsoft.Sql/servers"
    },
    {
      "apiVersion": "2014-04-01",
      "dependsOn": [
        "[concat('Microsoft.Sql/servers/', parameters('serverName'))]"
      ],
      "location": "[parameters('location')]",
      "name": "[concat(parameters('serverName'), '/', parameters('elasticPoolName'))]",
      "properties": {
        "edition": "[parameters('edition')]",
        "dtu": "[parameters('poolDtu')]",
        "databaseDtuMin": "[parameters('databaseDtuMin')]",
        "databaseDtuMax": "[parameters('databaseDtuMax')]"
      },
      "type": "Microsoft.Sql/servers/elasticPools"
    },
    {
      "type": "Microsoft.Sql/servers/databases",
      "name": "[concat(parameters('serverName'), '/', parameters('databasesNames')[copyIndex()])]",
      "location": "[parameters('location')]",
      "apiVersion": "2014-04-01-preview",
      "dependsOn": [
        "[concat('Microsoft.Sql/servers/', parameters('serverName'))]",
        "[concat('Microsoft.Sql/servers/', parameters('serverName') ,'/elasticpools/', parameters('elasticPoolName'))]"
      ],
      "properties": {
        "collation": "[parameters('databaseCollation')]",
        "requestedServiceObjectiveName": "ElasticPool",
        "elasticPoolName": "[parameters('elasticPoolName')]"
      },
      "copy": {
        "name": "addDatabasesInElasticPool",
        "count": "[length(parameters('databasesNames'))]"
      }
    },
    {
      "apiVersion": "2014-04-01-preview",
      "dependsOn": [
        "[concat('Microsoft.Sql/servers/', parameters('serverName'))]"
      ],
      "location": "[parameters('location')]",
      "name": "[concat(parameters('serverName'), '/', 'AllowAllWindowsAzureIps')]",
      "properties": {
        "endIpAddress": "0.0.0.0",
        "startIpAddress": "0.0.0.0"
      },
      "type": "Microsoft.Sql/servers/firewallrules"
    },
    // 配置Azure AD服务器管理员
    {
      "type": "Microsoft.Sql/servers/administrators",
      "apiVersion": "2017-03-01-preview",
      "name": "[concat(parameters('serverName'), '/ActiveDirectory')]",
      "dependsOn": [
        "[resourceId('Microsoft.Sql/servers', parameters('serverName'))]"
      ],
      "properties": {
        "administratorType": "ActiveDirectory",
        "login": "[parameters('aadAdminLogin')]",
        "sid": "[parameters('aadAdminObjectId')]",
        "tenantId": "[subscription().tenantId]"
      }
    },
    // 为每个数据库添加AD组用户的部署脚本
    {
      "type": "Microsoft.Resources/deploymentScripts",
      "apiVersion": "2020-10-01",
      "name": "[concat('AddAadGroupToDb-', parameters('databasesNames')[copyIndex()])]",
      "location": "[parameters('location')]",
      "dependsOn": [
        "addDatabasesInElasticPool",
        "[resourceId('Microsoft.Sql/servers/administrators', parameters('serverName'), 'ActiveDirectory')]"
      ],
      "kind": "AzurePowerShell",
      "properties": {
        "forceUpdateTag": "[utcNow()]",
        "azPowerShellVersion": "7.2",
        "scriptContent": @"
          $serverName = '${variables('sqlServerFqdn')}'
          $databaseName = '${parameters('databasesNames')[copyIndex()]}'
          $aadGroupName = '${parameters('aadGroupName')}'
          
          # 使用Azure AD默认身份连接数据库
          $connectionString = ""Server=tcp:$serverName,1433;Database=$databaseName;Authentication=Active Directory Default;Encrypt=True""
          $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
          $connection.Open()
          
          # 创建AD组用户
          $createUserQuery = ""CREATE USER [$aadGroupName] FROM EXTERNAL PROVIDER;""
          $command = New-Object System.Data.SqlClient.SqlCommand($createUserQuery, $connection)
          $command.ExecuteNonQuery()
          
          # 赋予基础读写权限（可根据需求调整）
          $grantPermissionsQuery = ""ALTER ROLE db_datareader ADD MEMBER [$aadGroupName]; ALTER ROLE db_datawriter ADD MEMBER [$aadGroupName];""
          $command = New-Object System.Data.SqlClient.SqlCommand($grantPermissionsQuery, $connection)
          $command.ExecuteNonQuery()
          
          $connection.Close()
          Write-Host ""Successfully added Azure AD group $aadGroupName to database $databaseName""
        ",
        "cleanupPreference": "OnSuccess",
        "timeout": "PT30M"
      },
      "copy": {
        "name": "addAadGroupToDatabases",
        "count": "[length(parameters('databasesNames'))]"
      }
    }
  ]
}

参数文件（azure.deploy.parameters.ps1）

{
  "$schema": "http://schema.management.azure.com/schemas/2014-04-01-preview/deploymentParameters.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "administratorLogin": {
      "value": "bogblogsqldbadmin"
    },
    "administratorLoginPassword": {
      "value": "<你的安全密码>"
    },
    "serverName": {
      "value": "azrsqlsrv1"
    },
    "elasticPoolName": {
      "value": "azrsqlsrve1"
    },
    "poolDtu": {
      "value": 100
    },
    "databaseDtuMax": {
      "value": 100
    },
    "databasesNames": {
      "value": [
        "asqldb11",
        "asqldb12"
      ]
    },
    "aadAdminLogin": {
      "value": "sqladmin@yourdomain.com"
    },
    "aadAdminObjectId": {
      "value": "<Azure AD管理员对象ID>"
    },
    "aadGroupName": {
      "value": "SQLDBReadOnlyGroup"
    },
    "aadGroupObjectId": {
      "value": "<Azure AD组对象ID>"
    }
  }
}

关键实现说明

1. 配置Azure AD服务器管理员

通过Microsoft.Sql/servers/administrators资源类型，直接在ARM中指定Azure AD用户或组作为SQL Server的管理员：

需要提供管理员的UPN（login参数）和对象ID（sid参数）
租户ID自动引用当前订阅的租户ID，无需手动填写

2. 添加AD组作为数据库用户

由于ARM本身无法直接执行T-SQL命令，我们使用Microsoft.Resources/deploymentScripts资源运行PowerShell脚本：

脚本通过Azure AD默认身份认证连接数据库
执行CREATE USER FROM EXTERNAL PROVIDER命令创建AD组用户
为用户赋予指定的数据库权限（示例中是读写权限，可根据需求修改）
通过copy属性自动为所有数据库批量添加用户

3. 依赖关系保障

通过dependsOn属性确保部署顺序：

先创建SQL Server
再配置弹性池和数据库
然后设置Azure AD管理员
最后执行脚本添加数据库用户

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

如何通过ARM脚本将Azure AD组添加到Azure SQL数据库

现有部署脚本（已扩展Azure AD功能） #

主模板文件（azure.deploy.ps1） #

参数文件（azure.deploy.parameters.ps1） #

关键实现说明 #

1. 配置Azure AD服务器管理员 #

2. 添加AD组作为数据库用户 #

3. 依赖关系保障 #