Issues trying to add VNET Service Endpoints in ARM Template

Question

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:

1. 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.
2. 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:

1. Can anyone explain the second error where it states Microsoft.Sql is invalid but then lists it as the supported service names?
2. 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": true My 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.

Answer 1

So, I got this to work as follows:

For the virtualNetworkRules I added a dependency to the subnet

{
            "type": "Microsoft.Sql/servers/virtualNetworkRules",
            "apiVersion": "2015-05-01-preview",
            "name": "[concat(variables('uniqueSQLName'), '/ClientSubnet')]",
            "dependsOn": [
                "[resourceId('Microsoft.Sql/servers', variables('uniqueSQLName'))]",
                "[resourceId('Microsoft.Network/virtualNetworks/subnets', 'MyVirtualNetwork', 'Client-Subnet')]"
            ],
            "properties": {
                "virtualNetworkSubnetId": "[resourceId('Microsoft.Network/virtualNetworks/subnets', 'MyVirtualNetwork', 'Client-Subnet')]",
                "ignoreMissingVnetServiceEndpoint": true
            }
        }

I then updated the virtualNetwork to:

{
            "type": "Microsoft.Network/virtualNetworks",
            "apiVersion": "2020-05-01",
            "name": "MyVirtualNetwork",
            "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": [
                                        "[resourceGroup().location]"
                                    ]
                                }
                            ],
                            "PrivateEndpointNetworkPolicies": "Disabled",
                            "PrivateLinkServiceNetworkPolicies": "Disabled"
                        }
                    }
                ]
            }
        }

and included a subnet resource:

{
            "type": "Microsoft.Network/virtualNetworks/subnets",
            "apiVersion": "2020-05-01",
            "name": "[concat('MyVirtualNetwork', '/Client-Subnet')]",
            "dependsOn": [
                "[resourceId('Microsoft.Network/virtualNetworks', 'MyVirtualNetwork')]",
                "[resourceId('Microsoft.Network/networkSecurityGroups', variables('vmNSG'))]"
            ],
            "properties": {
                "addressPrefix": "10.0.0.0/24",
                "networkSecurityGroup": {
                    "id": "[resourceId('Microsoft.Network/networkSecurityGroups', variables('vmNSG'))]"
                },
                "serviceEndpoints": [
                    {
                        "service": "Microsoft.Sql",
                        "locations": [
                            "[resourceGroup().location]"
                        ]
                    }
                ],
                "PrivateEndpointNetworkPolicies": "Disabled",
                "PrivateLinkServiceNetworkPolicies": "Disabled"
            }
        }

Everything seems to be happily working with that config.

NOTE - I also change the API version on some of these resources - not sure if that had an impact as well