SQL 托管实例 - 创建或更新 REST API 可用于在预配期间创建启用了“仅限 Microsoft Entra 的身份验证”的托管实例。
注意
作为先决条件,该脚本要求创建虚拟网络和子网。
以下脚本将预配托管实例,将 Microsoft Entra 管理员设置为 <MSEntraAccount>
,并启用“仅限 Microsoft Entra 身份验证”。 此外,还将自动创建实例 SQL 管理员,并将密码设置为随机密码。 由于在预配过程中禁用了 SQL 身份验证连接,因此不会使用 SQL 管理员登录名。
Microsoft Entra 管理员 <MSEntraAccount>
可用于在预配完成时管理实例。
替换示例中的以下值:
<tenantId>
:可通过转到 Azure 门户并进入 Microsoft Entra ID 资源找到。 在“概述”窗格中,应会看到“租户 ID”
<subscriptionId>
:可以在 Azure 门户中找到订阅 ID
<instanceName>
:使用唯一的托管实例名称
<ResourceGroupName>
:逻辑服务器的资源组名称
<MSEntraAccount>
:可以是 Microsoft Entra 用户或组。 例如: DummyLogin
<Location>
:服务器的位置,例如 chinaeast2
或 chinanorth2
<objectId>
:可以通过转到 Azure 门户,然后转到 Microsoft Entra ID 资源来找到此值。 在“用户”窗格中,搜索 Microsoft Entra 用户并查找其“对象 ID”。 如果使用应用程序(服务主体)作为 Microsoft Entra 管理员,请将此值替换为应用程序 ID。 还需要更新 principalType
。
- 需要使用
<ResourceGroupName>
、Subscription ID
、<VNetName>
和 <SubnetName>
更新 subnetId
参数
Import-Module Azure
Import-Module MSAL.PS
$tenantId = '<tenantId>'
$clientId = '1950a258-227b-4e31-a9cf-717495945fc2' # Static Microsoft client ID used for getting a token
$subscriptionId = '<subscriptionId>'
$uri = "urn:ietf:wg:oauth:2.0:oob"
$instanceName = "<instanceName>"
$resourceGroupName = "<ResourceGroupName>"
$scopes ="https://management.core.chinacloudapi.cn/.default"
Login-AzAccount -tenantId $tenantId -Environment AzureChinaCloud
# Login as an Microsoft Entra user with permission to provision a managed instance
$result = Get-MsalToken -RedirectUri $uri -ClientId $clientId -TenantId $tenantId -Scopes $scopes
$authHeader = @{
'Content-Type'='application\json; '
'Authorization'=$result.CreateAuthorizationHeader()
}
$body = '{
"name": "<instanceName>", "type": "Microsoft.Sql/managedInstances", "identity": { "type": "SystemAssigned"},"location": "<Location>", "sku": {"name": "GP_Gen5", "tier": "GeneralPurpose", "family":"Gen5","capacity": 8},
"properties": {"administrators":{ "login":"<MSEntraAccount>", "sid":"<objectId>", "tenantId":"<tenantId>", "principalType":"User", "azureADOnlyAuthentication":true },
"subnetId": "/subscriptions/<subscriptionId>/resourceGroups/<ResourceGroupName>/providers/Microsoft.Network/virtualNetworks/<VNetName>/subnets/<SubnetName>",
"licenseType": "LicenseIncluded", "vCores": 8, "storageSizeInGB": 2048, "collation": "SQL_Latin1_General_CP1_CI_AS", "proxyOverride": "Proxy", "timezoneId": "UTC", "privateEndpointConnections": [], "storageAccountType": "GRS", "zoneRedundant": false
}
}'
# To provision the instance, execute the `PUT` command
Invoke-RestMethod -Uri https://management.chinacloudapi.cn/subscriptions/$subscriptionId/resourceGroups/$resourceGroupName/providers/Microsoft.Sql/managedInstances/$instanceName/?api-version=2020-11-01-preview -Method PUT -Headers $authHeader -Body $body -ContentType "application/json"
要检查结果,请执行 GET
命令:
Invoke-RestMethod -Uri https://management.chinacloudapi.cn/subscriptions/$subscriptionId/resourceGroups/$resourceGroupName/providers/Microsoft.Sql/managedInstances/$instanceName/?api-version=2020-11-01-preview -Method GET -Headers $authHeader | Format-List
若要预配新的托管实例、虚拟网络和子网,为实例设置 Microsoft Entra 管理员并启用“仅限 Microsoft Entra 身份验证”,请使用以下模板。
使用 Azure 门户中的自定义部署,在编辑器中生成自己的模板。 接下来,粘贴到示例中后保存配置。
{
"$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentParameters.json#",
"contentVersion": "1.0.0.1",
"parameters": {
"managedInstanceName": {
"type": "String",
"metadata": {
"description": "Enter managed instance name."
}
},
"aad_admin_name": {
"type": "String",
"metadata": {
"description": "The name of the Azure AD admin for the SQL managed instance."
}
},
"aad_admin_objectid": {
"type": "String",
"metadata": {
"description": "The Object ID of the Azure AD admin. The Object ID of the Azure AD admin if the admin is a user or group. For Applications, use the Application ID."
}
},
"aad_admin_tenantid": {
"type": "String",
"defaultValue": "[subscription().tenantId]",
"metadata": {
"description": "The Tenant ID of the Azure Active Directory"
}
},
"aad_admin_type": {
"defaultValue": "User",
"allowedValues": [
"User",
"Group",
"Application"
],
"type": "String"
},
"aad_only_auth": {
"defaultValue": true,
"type": "Bool"
},
"location": {
"defaultValue": "[resourceGroup().location]",
"type": "String",
"metadata": {
"description": "Enter location. If you leave this field blank resource group location would be used."
}
},
"virtualNetworkName": {
"type": "String",
"defaultValue": "SQLMI-VNET",
"metadata": {
"description": "Enter virtual network name. If you leave this field blank name will be created by the template."
}
},
"addressPrefix": {
"defaultValue": "10.0.0.0/16",
"type": "String",
"metadata": {
"description": "Enter virtual network address prefix."
}
},
"subnetName": {
"type": "String",
"defaultValue": "ManagedInstances",
"metadata": {
"description": "Enter subnet name. If you leave this field blank name will be created by the template."
}
},
"subnetPrefix": {
"defaultValue": "10.0.0.0/24",
"type": "String",
"metadata": {
"description": "Enter subnet address prefix."
}
},
"skuName": {
"defaultValue": "GP_Gen5",
"allowedValues": [
"GP_Gen5",
"BC_Gen5"
],
"type": "String",
"metadata": {
"description": "Enter sku name."
}
},
"vCores": {
"defaultValue": 16,
"allowedValues": [
8,
16,
24,
32,
40,
64,
80
],
"type": "Int",
"metadata": {
"description": "Enter number of vCores."
}
},
"storageSizeInGB": {
"defaultValue": 256,
"minValue": 32,
"maxValue": 8192,
"type": "Int",
"metadata": {
"description": "Enter storage size."
}
},
"licenseType": {
"defaultValue": "LicenseIncluded",
"allowedValues": [
"BasePrice",
"LicenseIncluded"
],
"type": "String",
"metadata": {
"description": "Enter license type."
}
}
},
"variables": {
"networkSecurityGroupName": "[concat('SQLMI-', parameters('managedInstanceName'), '-NSG')]",
"routeTableName": "[concat('SQLMI-', parameters('managedInstanceName'), '-Route-Table')]"
},
"resources": [
{
"type": "Microsoft.Network/networkSecurityGroups",
"apiVersion": "2020-06-01",
"name": "[variables('networkSecurityGroupName')]",
"location": "[parameters('location')]",
"properties": {
"securityRules": [
{
"name": "allow_tds_inbound",
"properties": {
"description": "Allow access to data",
"protocol": "Tcp",
"sourcePortRange": "*",
"destinationPortRange": "1433",
"sourceAddressPrefix": "VirtualNetwork",
"destinationAddressPrefix": "*",
"access": "Allow",
"priority": 1000,
"direction": "Inbound"
}
},
{
"name": "allow_redirect_inbound",
"properties": {
"description": "Allow inbound redirect traffic to SQL Managed Instance inside the virtual network",
"protocol": "Tcp",
"sourcePortRange": "*",
"destinationPortRange": "11000-11999",
"sourceAddressPrefix": "VirtualNetwork",
"destinationAddressPrefix": "*",
"access": "Allow",
"priority": 1100,
"direction": "Inbound"
}
},
{
"name": "deny_all_inbound",
"properties": {
"description": "Deny all other inbound traffic",
"protocol": "*",
"sourcePortRange": "*",
"destinationPortRange": "*",
"sourceAddressPrefix": "*",
"destinationAddressPrefix": "*",
"access": "Deny",
"priority": 4096,
"direction": "Inbound"
}
},
{
"name": "deny_all_outbound",
"properties": {
"description": "Deny all other outbound traffic",
"protocol": "*",
"sourcePortRange": "*",
"destinationPortRange": "*",
"sourceAddressPrefix": "*",
"destinationAddressPrefix": "*",
"access": "Deny",
"priority": 4096,
"direction": "Outbound"
}
}
]
}
},
{
"type": "Microsoft.Network/routeTables",
"apiVersion": "2020-06-01",
"name": "[variables('routeTableName')]",
"location": "[parameters('location')]",
"properties": {
"disableBgpRoutePropagation": false
}
},
{
"type": "Microsoft.Network/virtualNetworks",
"apiVersion": "2020-06-01",
"name": "[parameters('virtualNetworkName')]",
"location": "[parameters('location')]",
"dependsOn": [
"[variables('routeTableName')]",
"[variables('networkSecurityGroupName')]"
],
"properties": {
"addressSpace": {
"addressPrefixes": [
"[parameters('addressPrefix')]"
]
},
"subnets": [
{
"name": "[parameters('subnetName')]",
"properties": {
"addressPrefix": "[parameters('subnetPrefix')]",
"routeTable": {
"id": "[resourceId('Microsoft.Network/routeTables', variables('routeTableName'))]"
},
"networkSecurityGroup": {
"id": "[resourceId('Microsoft.Network/networkSecurityGroups', variables('networkSecurityGroupName'))]"
},
"delegations": [
{
"name": "miDelegation",
"properties": {
"serviceName": "Microsoft.Sql/managedInstances"
}
}
]
}
}
]
}
},
{
"type": "Microsoft.Sql/managedInstances",
"apiVersion": "2020-11-01-preview",
"name": "[parameters('managedInstanceName')]",
"location": "[parameters('location')]",
"dependsOn": [
"[parameters('virtualNetworkName')]"
],
"sku": {
"name": "[parameters('skuName')]"
},
"identity": {
"type": "SystemAssigned"
},
"properties": {
"subnetId": "[resourceId('Microsoft.Network/virtualNetworks/subnets', parameters('virtualNetworkName'), parameters('subnetName'))]",
"storageSizeInGB": "[parameters('storageSizeInGB')]",
"vCores": "[parameters('vCores')]",
"licenseType": "[parameters('licenseType')]",
"administrators": {
"login": "[parameters('aad_admin_name')]",
"sid": "[parameters('aad_admin_objectid')]",
"tenantId": "[parameters('aad_admin_tenantid')]",
"principalType": "[parameters('aad_admin_type')]",
"azureADOnlyAuthentication": "[parameters('aad_only_auth')]"
}
}
}
]
}