Mandatory security rules & routes required for Azure SQL Managed Instance in Bicep to enable repeatable template deployments without Network Intent policy violations.

main.bicep

// SQL MI NSG and Route table with mandatory subnet service-aided config: https://learn.microsoft.com/en-gb/azure/azure-sql/managed-instance/connectivity-architecture-overview?view=azuresql&tabs=current#mandatory-security-rules-with-service-aided-subnet-configuration
//
// where modules defined are copied locally from CARML/Azure Verified Modules GitHub Repositories. https://github.com/Azure/ResourceModules/tree/main/modules/network/network-security-group & https://github.com/Azure/ResourceModules/tree/main/modules/network/route-table
//
//
// Azure Bicep with complete list of current Azure SQL MI mandatory security rules & routes for repeatable template deployments without template violations
// Insert this to your Azure SQL MI Bicep templates to avoid Network Intent Policy errors when redeploying Bicep templates for Azure SQL MI and avoid erorrs like: https://github.com/Azure/azure-quickstart-templates/issues/6670
@description('Enter Azure SQL MI subnet address prefix.')
param subnetPrefix string = '10.1.2.0/24'

var subnetPrefixString = replace(replace(subnetPrefix, '.', '-'), '/', '-')

module nsg 'modules/network-security-group/main.bicep' = {
  name: '${uniqueString(deployment().name, location)}-nsg'
  params: {
    name: nsgName
    location: location
    securityRules: [
      {
        name: 'Microsoft.Sql-managedInstances_UseOnly_mi-healthprobe-in-${subnetPrefixString}-v11'
        properties: {
          description: 'Allow Azure Load Balancer inbound traffic'
          protocol: '*'
          sourcePortRange: '*'
          sourceAddressPrefix: 'AzureLoadBalancer'
          destinationAddressPrefix: subnetPrefix
          destinationPortRange: '*'
          access: 'Allow'
          priority: 100
          direction: 'Inbound'
        }
      }
      {
        name: 'Microsoft.Sql-managedInstances_UseOnly_mi-internal-in-${subnetPrefixString}-v11'
        properties: {
          description: 'Allow MI internal inbound traffic'
          protocol: '*'
          sourcePortRange: '*'
          destinationPortRange: '*'
          sourceAddressPrefix: subnetPrefix
          destinationAddressPrefix: subnetPrefix
          access: 'Allow'
          priority: 101
          direction: 'Inbound'
        }
      }
      {
        name: 'Microsoft.Sql-managedInstances_UseOnly_mi-aad-out-${subnetPrefixString}-v11'
        properties: {
          description: 'Allow communication with Azure Active Directory over https'
          protocol: 'Tcp'
          sourcePortRange: '*'
          destinationPortRange: '443'
          sourceAddressPrefix: subnetPrefix
          destinationAddressPrefix: 'AzureActiveDirectory'
          access: 'Allow'
          priority: 102
          direction: 'Outbound'
        }
      }
      {
        name: 'Microsoft.Sql-managedInstances_UseOnly_mi-onedsc-out-${subnetPrefixString}-v11'
        properties: {
          description: 'Allow communication with the One DS Collector over https'
          protocol: 'Tcp'
          sourcePortRange: '*'
          destinationPortRange: '443'
          sourceAddressPrefix: subnetPrefix
          destinationAddressPrefix: 'OneDsCollector'
          access: 'Allow'
          priority: 103
          direction: 'Outbound'
        }
      }
      {
        name: 'Microsoft.Sql-managedInstances_UseOnly_mi-internal-out-${subnetPrefixString}-v11'
        properties: {
          description: 'Allow MI internal outbound traffic'
          protocol: '*'
          sourcePortRange: '*'
          destinationPortRange: '*'
          sourceAddressPrefix: subnetPrefix
          destinationAddressPrefix: subnetPrefix
          access: 'Allow'
          priority: 104
          direction: 'Outbound'
        }
      }
      {
        name: 'Microsoft.Sql-managedInstances_UseOnly_mi-strg-p-out-${subnetPrefixString}-v11'
        properties: {
          description: 'Allow outbound communication with storage over HTTPS'
          protocol: '*'
          sourcePortRange: '*'
          destinationPortRange: '443'
          sourceAddressPrefix: subnetPrefix
          destinationAddressPrefix: 'Storage.uksouth'
          access: 'Allow'
          priority: 105
          direction: 'Outbound'
        }
      }
      {
        name: 'Microsoft.Sql-managedInstances_UseOnly_mi-strg-s-out-${subnetPrefixString}-v11'
        properties: {
          description: 'Allow outbound communication with storage over HTTPS'
          protocol: '*'
          sourcePortRange: '*'
          destinationPortRange: '443'
          sourceAddressPrefix: subnetPrefix
          destinationAddressPrefix: 'Storage.ukwest'
          access: 'Allow'
          priority: 106
          direction: 'Outbound'
        }
      }
      {
        name: 'Microsoft.Sql-managedInstances_UseOnly_mi-optional-azure-out-${subnetPrefixString}-v11'
        properties: {
          description: 'Allow AzureCloud outbound https traffic'
          protocol: '*'
          sourcePortRange: '*'
          destinationPortRange: '443'
          sourceAddressPrefix: subnetPrefix
          destinationAddressPrefix: 'AzureCloud'
          access: 'Allow'
          priority: 107
          direction: 'Outbound'
        }
      }
    ]
  }
}

module route 'modules/route-table/main.bicep' = {
  name: '${uniqueString(deployment().name, location)}-rt'
  params: {
    name: rtName
    location: location
    routes: [
        {
          name: 'Microsoft.Sql-managedInstances_UseOnly_subnet-${subnetPrefixString}-to-vnetlocal'
          properties: {
            addressPrefix: subnetPrefix
            nextHopType: 'VnetLocal'
            hasBgpOverride: false
          }
        }
        {
          name: 'Microsoft.Sql-managedInstances_UseOnly_mi-AzureActiveDirectory'
          properties: {
            addressPrefix: 'AzureActiveDirectory'
            nextHopType: 'Internet'
            hasBgpOverride: false
          }
        }
        {
          name: 'Microsoft.Sql-managedInstances_UseOnly_mi-OneDsCollector'
          properties: {
            addressPrefix: 'OneDsCollector'
            nextHopType: 'Internet'
            hasBgpOverride: false
          }
        }
        {
          name: 'Microsoft.Sql-managedInstances_UseOnly_mi-Storage.uksouth'
          properties: {
            addressPrefix: 'Storage.uksouth'
            nextHopType: 'Internet'
            hasBgpOverride: false
          }
        }
        {
          name: 'Microsoft.Sql-managedInstances_UseOnly_mi-Storage.ukwest'
          properties: {
            addressPrefix: 'Storage.ukwest'
            nextHopType: 'Internet'
            hasBgpOverride: false
          }
        }
        {
          name: 'Microsoft.Sql-managedInstances_UseOnly_optional-AzureCloud.uksouth'
          properties: {
            addressPrefix: 'AzureCloud.uksouth'
            nextHopType: 'Internet'
            hasBgpOverride: false
          }
        }
        {
          name: 'Microsoft.Sql-managedInstances_UseOnly_optional-AzureCloud.ukwest'
          properties: {
            addressPrefix: 'AzureCloud.ukwest'
            nextHopType: 'Internet'
            hasBgpOverride: false
          }
        }
    ]
  }
}