Create Azure SQL MI failed with failed of "Prepares a subnet by applying necessary Network Policies"

Question

When creating Azure SQL MI, there is a failed issue on "Prepares a subnet by applying necessary Network Policies" operation with "ResourceOperationFailure" Error code.

What is the root cause?

Answer 1

Hi @Li, Jindong

As per the error message, it seems you tried to deploy Managed Instance in subnet that has conflicts.

The error indicate that, the subnet where you are trying to deploy the Azure SQL Managed Instance is not properly configured. Make sure that you have created a valid virtual network and subnet for Azure SQL Managed Instance.

Please ensure that before creating SQL Managed Instance it meets the networking requirements mentioned at Connectivity architecture - Azure SQL Managed Instance | Microsoft Learn.

Thank you!

Answer 2

Hi
Li, Jindong •,

Welcome to Microsoft Q&A forum and thanks for using Azure Services.

As I understand, you are getting error when creating Azure SQL MI on "Prepares a subnet by applying necessary Network Policies" operation with "ResourceOperationFailure" Error code.

The subnet in which SQL Managed Instance is deployed must have the following characteristics:

• Dedicated subnet: The subnet SQL Managed Instance uses can be delegated only to the SQL Managed Instance service. The subnet can't be a gateway subnet, and you can deploy only SQL Managed Instance resources in the subnet.
• Subnet delegation: The SQL Managed Instance subnet must be delegated to the Microsoft.Sql/managedInstances resource provider.
• Network security group: A network security group must be associated with the SQL Managed Instance subnet. You can use a network security group to control access to the SQL Managed Instance data endpoint by filtering traffic on port 1433 and ports 11000-11999 when SQL Managed Instance is configured for redirect connections. The service automatically provisions rules and keeps them current as required to allow uninterrupted flow of management traffic.
• Route table: A route table must be associated with the SQL Managed Instance subnet. You can add entries to this route table, for example to route traffic to premises through a virtual network gateway, or to add the default 0.0.0.0/0 route directing all traffic through a virtual network appliance such as a firewall. Azure SQL Managed Instance automatically provisions and manages its required entries in the route table.
• Sufficient IP addresses: The SQL Managed Instance subnet must have at least 32 IP addresses. For more information, see Determine the size of the subnet for SQL Managed Instance. You can deploy managed instances in the existing network after you configure it to satisfy the networking requirements for SQL Managed Instance. Otherwise, create a new network and subnet.
• Allowed by Azure policies: If you use Azure Policy to prevent resource creation or modification in a scope that includes a SQL Managed Instance subnet or virtual network, your policies must not prevent SQL Managed Instance from managing its internal resources. The following resources need to be excluded from policy deny effects for normal operation:
  • Resources of type Microsoft.Network/serviceEndpointPolicies, when resource name begins with \_e41f87a2\_
    • All resources of type Microsoft.Network/networkIntentPolicies
      • All resources of type Microsoft.Network/virtualNetworks/subnets/contextualServiceEndpointPolicies
• Locks on virtual network: Locks on the dedicated subnet's virtual network, its parent resource group, or subscription, might occasionally interfere with SQL Managed Instance management and maintenance operations. Take special care when you use resource locks.
• Replication traffic: Replication traffic for auto-failover groups between two managed instances should be direct and not routed through a hub network.
• Custom DNS server: If the virtual network is configured to use a custom DNS server, the DNS server must be able to resolve public DNS records. Using features like Microsoft Entra authentication might require resolving more fully qualified domain names (FQDNs). For more information, see Resolving private DNS names in Azure SQL Managed Instance.

Reference documentation: https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/connectivity-architecture-overview?view=azuresql&tabs=current#service-aided-subnet-configuration

If all above requirements meet in your case, I would recommend you to please file a support ticket for deeper investigation and in case if you don't have a support plan, do let us know here so that we can check on other options to unblock you.

Thanks