I currently have an ARM Template that deploys a Virtual Network with a Subnet along with an Azure SQL Database instance.
The core resources related to the Subnet and SQL Firewall Rules are:
{
"name": "MyVirtualNetwork",
"type": "Microsoft.Network/virtualNetworks",
"apiVersion": "2019-11-01",
"location": "[resourceGroup().location]",
"dependsOn": [
"[resourceId('Microsoft.Network/networkSecurityGroups', variables('vmNSG'))]"
],
"properties": {
"addressSpace": {
"addressPrefixes": [
"10.0.0.0/16"
]
},
"subnets": [
{
"name": "Client-Subnet",
"properties": {
"addressPrefix": "10.0.0.0/24",
"networkSecurityGroup": {
"id": "[resourceId('Microsoft.Network/networkSecurityGroups', variables('vmNSG'))]"
}
}
}
]
}
},
{
"type": "Microsoft.Network/virtualNetworks/subnets",
"apiVersion": "2019-11-01",
"name": "NDC-VirtualNetwork/Client-Subnet",
"properties": {
"addressPrefix": "10.0.0.0/24"
},
"dependsOn": [
"[resourceId('Microsoft.Network/virtualNetworks', 'NDC-VirtualNetwork')]"
]
}
and
{
"type": "firewallRules",
"apiVersion": "2015-05-01-preview",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', variables('uniqueSQLName'))]"
],
"location": "[resourceGroup().location]",
"name": "AllowAllWindowsAzureIps",
"properties": {
"startIpAddress": "0.0.0.0",
"endIpAddress": "0.0.0.0"
}
},
{
"type": "firewallRules",
"apiVersion": "2015-05-01-preview",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', variables('uniqueSQLName'))]"
],
"location":"[resourceGroup().location]",
"name": "ClientIP",
"properties": {
"startIpAddress": "[parameters('clientIP')]",
"endIpAddress": "[parameters('clientIP')]"
}
}
I now want to update the Template to permit VNET Service Endpoints from this Subnet to access SQL and to remove the "AllowAllWindowsAzureIPs" and "ClientIP" firewall rules.
To achieve this, I remove both firewallRules resources from the SQL resource and add the following:
{
"name": "[concat(variables('uniqueSQLName'), '-Client-Subnet')]",
"type": "virtualNetworkRules",
"apiVersion": "2015-05-01-preview",
"properties": {
"virtualNetworkSubnetId": "[resourceId('Microsoft.Network/virtualNetworks/subnets', 'NDC-VirtualNetwork', 'Client-Subnet')]",
"ignoreMissingVnetServiceEndpoint": true
},
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', variables('uniqueSQLName'))]"
]
}
and then update the Networking resources to :
{
"name": "MyVirtualNetwork",
"type": "Microsoft.Network/virtualNetworks",
"apiVersion": "2019-11-01",
"location": "[resourceGroup().location]",
"dependsOn": [
"[resourceId('Microsoft.Network/networkSecurityGroups', variables('vmNSG'))]"
],
"properties": {
"addressSpace": {
"addressPrefixes": [
"10.0.0.0/16"
]
},
"subnets": [
{
"name": "Client-Subnet",
"properties": {
"addressPrefix": "10.0.0.0/24",
"networkSecurityGroup": {
"id": "[resourceId('Microsoft.Network/networkSecurityGroups', variables('vmNSG'))]"
},
"serviceEndpoints": [
{
"service": "Microsoft.Sql",
"locations": [
"australiaeast"
]
}
]
}
}
]
}
},
{
"type": "Microsoft.Network/serviceEndpointPolicies",
"apiVersion": "2019-11-01",
"name": "AllowVNETtoSQL",
"location": "[resourceGroup().location]",
"dependsOn": [
"[resourceId('Microsoft.Network/virtualNetworks', 'MyVirtualNetwork')]",
"[resourceId('Microsoft.Sql/servers', variables('uniqueSQLName'))]"
],
"properties": {
"serviceEndpointPolicyDefinitions": [
{
"name": "AllowVNETtoSQLPolicy",
"properties": {
"service": "Microsoft.Sql",
"serviceResources": [
"[resourceId('Microsoft.Sql/servers', variables('uniqueSQLName'))]"
]
}
}
]
}
},
{
"type": "Microsoft.Network/virtualNetworks/subnets",
"apiVersion": "2019-11-01",
"name": "MyVirtualNetwork/Client-Subnet",
"dependsOn": [
"[resourceId('Microsoft.Network/virtualNetworks','MyVirtualNetwork')]",
"[resourceId('Microsoft.Network/serviceEndpointPolicies','AllowVNETtoSQL')]"
],
"properties": {
"addressPrefix": "10.0.0.0/24",
"serviceEndpointPolicies": [
{
"id": "[resourceId('Microsoft.Network/serviceEndpointPolicies','AllowVNETtoSQL')]"
}
],
"serviceEndpoints": [
{
"service": "Microsoft.Sql",
"locations": [
"australiaeast"
]
}
]
}
}
I get two errors from this change:
- Azure SQL Server Virtual Network Rule encountered an user error: Cannot proceed with operation because subnets Client-Subnet of the virtual network /subscriptions//resourceGroups//providers/Microsoft.Network/virtualNetworks/MyVirtualNetwork are not provisioned. They are in Updating state.
- Service endpoint policy definition
/subscriptions//resourceGroups//providers/Microsoft.Network/serviceEndpointPolicies/AllowVNETtoSQL/serviceEndpointPolicyDefinitions/AllowVNETtoSQLPolicy references
an invalid service name Microsoft.Sql. Supported service names are: Microsoft.Storage, Microsoft.Sql, Microsoft.AzureActiveDirectory, Microsoft.AzureCosmosDB, Microsoft.Web, Microsoft.NetworkServiceEndpointTest, Microsoft.KeyVault, Microsoft.EventHub, Microsoft.ServiceBus, Microsoft.ContainerRegistry, Microsoft.CognitiveServices, Global. (Code: ServiceEndpointPolicyDefinitionHasServiceWithInvalidServiceName)
My questions are as follows:
- Can anyone explain the second error where it states Microsoft.Sql is invalid but then lists it as the supported service names?
- What am I missing with Dependencies to allow the Service Endpoints to complete deployment? I already have the SQL Virtual Network Rule with the property
"ignoreMissingVnetServiceEndpoint": trueMy understanding of this is that the SQL resource would create the Service Endpoint firewall rule OK and skip any checking of the Subnet state and the Subnet would then happily transition into the enabled state and future connections would be allowed.
