SQL 受控執行個體 - 建立或更新 REST API 可用於建立在佈建期間啟用僅限 Microsoft Entra 驗證的受控執行個體。
注意
指令碼需要建立虛擬網路和子網路作為必要條件。
下列指令碼會佈建受控執行個體，將 Microsoft Entra 管理員設定為 <MSEntraAccount>，並啟用僅限 Microsoft Entra 驗證。 執行個體 SQL 管理員也會自動建立，密碼會設定為隨機密碼。 由於此佈建已停用 SQL 驗證連線，因此不會使用 SQL 管理員登入。
佈建完成時，Microsoft Entra 管理員 <MSEntraAccount> 可用來管理執行個體。
取代範例中的下列值：
<tenantId>：前往 Azure 入口網站，然後前往您的 Microsoft Entra ID 資源，即可找到。 在 [概觀] 窗格中，應該會看到您的租用戶 ID<subscriptionId>您可以在 Azure 入口網站中找到訂閱識別碼<instanceName>：使用唯一的受控執行個體名稱<ResourceGroupName>：您邏輯伺服器的資源群組名稱<MSEntraAccount>：可以是 Microsoft Entra 使用者或群組。 例如， DummyLogin<Location>：伺服器的位置，例如 westus2 或 centralus<objectId>：前往 Azure 入口網站，然後前往您的 Microsoft Entra ID 資源，即可找到。 在 [使用者] 窗格中，搜尋 Microsoft Entra 使用者並尋找其物件識別碼。 如果您使用應用程式 (服務主體) 作為 Microsoft Entra 管理員，請將此值替換為應用程式 ID。 您還需要更新 principalType。subnetId 參數需要以 <ResourceGroupName>、Subscription ID、<VNetName> 和 <SubnetName> 更新
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.windows.net/.default"
Login-AzAccount -tenantId $tenantId
# 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.azure.com/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.azure.com/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')]"
}
}
}
]
}
Azure SQL 資料庫 ![建立伺服器並啟用 [使用僅限 Microsoft Entra 驗證] 的螢幕擷取畫面。](media/authentication-azure-ad-only-authentication-create-server/azure-ad-portal-create-server.png?view=azuresql-mi)
![建立伺服器並啟用 [使用僅限 Microsoft Entra 驗證] 的螢幕擷取畫面。](media/authentication-azure-ad-only-authentication-create-server/azure-ad-portal-create-server.png?view=azuresql-mi#lightbox)
![[建立 SQL 受控執行個體] 基本索引標籤的 Azure 入口網站螢幕擷取畫面。](media/authentication-azure-ad-only-authentication-create-server/azure-ad-only-managed-instance-create-basic.png?view=azuresql-mi)
![[建立 SQL 受控執行個體] 基本索引標籤的 Azure 入口網站螢幕擷取畫面，其中已選取使用者僅限 Microsoft Entra 驗證。](media/authentication-azure-ad-only-authentication-create-server/azure-ad-only-managed-instance-create-basic-choose-authentication.png?view=azuresql-mi)
![[建立 SQL 受控執行個體] 基本索引標籤的 Azure 入口網站螢幕擷取畫面。](media/authentication-azure-ad-only-authentication-create-server/azure-ad-only-managed-instance-create-basic.png?view=azuresql-mi#lightbox)
![[建立 SQL 受控執行個體] 基本索引標籤的 Azure 入口網站螢幕擷取畫面，其中已選取使用者僅限 Microsoft Entra 驗證。](media/authentication-azure-ad-only-authentication-create-server/azure-ad-only-managed-instance-create-basic-choose-authentication.png?view=azuresql-mi#lightbox)
