Microsoft.Sql/managedInstances: cannot create idempotent template

### Resource Type

Microsoft.Sql/managedInstances

### Api Version

2022-11-01-preview

### Issue Type

Other

### Other Notes

I've built a SQL managed instance using bicep, once I attempt to rerun the bicep template I get a number of warnings about properties that will be removed (and if I apply, errors), I've managed to cater for most of them except for:

* subnet: properties.networkIntentPolicies
* sql manage instance: properties.virtualClusterId

_These properties are being populated by the SQL manage instance resource, not explicitly referenced by the template._

### Bicep Repro

**main.bicep**
```bicep
param environmentSuffix string = 'dev'
param location string = resourceGroup().location
var basename = 'sharepoint-${environmentSuffix}'

module vnet 'virtualNetwork.bicep' = {
name: 'sharepoint-vnet'
params: {
name: 'vnet-${basename}'
location: location
addressPrefix: '10.0.0.0/16'
}
}

module sql 'sqlManagedInstance.bicep' = {
name: 'sharepoint-sqlmi'
params: {
managedInstanceName: 'sqlmi-${basename}'
location: location
virtualNetworkName: vnet.outputs.virtualNetworkName
subnetAddressPrefix: '10.0.0.0/24'
administratorLogin: 'xxx'
administratorPrincipalType: 'User'
administratorSid: 'xxx'
}
}
```

**virtualNetwork.bicep**
```bicep
@description('Enter virtual network name. If you leave this field blank name will be created by the template.')
param name string

@description('Enter location. If you leave this field blank resource group location would be used.')
param location string = resourceGroup().location

@description('Enter virtual network address prefix.')
param addressPrefix string //= '10.0.0.0/16'

resource virtualNetwork 'Microsoft.Network/virtualNetworks@2022-11-01' = {
name: name
location: location
properties: {
addressSpace: {
addressPrefixes: [
addressPrefix
]
}
}
}

output virtualNetworkName string = virtualNetwork.name
```

**sqlManagedInstance.bicep**
```bicep
@description('Enter managed instance name.')
param managedInstanceName string

@description('Enter location. If you leave this field blank resource group location would be used.')
param location string = resourceGroup().location

@description('SID (object ID) of the server administrator.')
param administratorSid string

@description('Principal Type of the sever administrator.')
@allowed([ 'Application', 'Group', 'User' ])
param administratorPrincipalType string

@description('Login name of the server administrator.')
param administratorLogin string

@description('Enter sku name.')
@allowed([
'GP_Gen5'
'BC_Gen5'
])
param skuName string = 'GP_Gen5'

@description('Enter number of vCores.')
@allowed([
4
8
16
24
32
40
64
80
])
param vCores int = 4

@description('Enter storage size.')
@minValue(32)
@maxValue(8192)
param storageSizeInGB int = 32

@description('Enter license type.')
@allowed([
'BasePrice' // Bring your own licence
'LicenseIncluded' // Add licence
])
param licenseType string = 'BasePrice'

@description('Time zone of the virtual machines. Type "[TimeZoneInfo]::GetSystemTimeZones().Id" in PowerShell to get the list.')
@minLength(2)
@allowed([
'New Zealand Standard Time'
])
param timeZone string = 'New Zealand Standard Time'

@description('Virtual network to attach SQL Instance to')
param virtualNetworkName string

@description('AddressPrefix')
param subnetAddressPrefix string

resource sqlNSG 'Microsoft.Network/networkSecurityGroups@2022-11-01' = {
name: 'nsg-${managedInstanceName}'
location: location
properties: {
// securityRules: [
// // Inbound
// {
// name: 'Microsoft.Sql-managedInstances_UseOnly_mi-healthprobe-in-10-0-0-0-24-v11'
// type: 'Microsoft.Network/networkSecurityGroups/securityRules'
// properties: {
// description: 'Allow Azure Load Balancer inbound traffic'
// protocol: '*'
// sourcePortRange: '*'
// destinationPortRange: '*'
// sourceAddressPrefix: 'AzureLoadBalancer'
// destinationAddressPrefix: '10.0.0.0/24'
// access: 'Allow'
// priority: 100
// direction: 'Inbound'
// }
// }
// {
// name: 'Microsoft.Sql-managedInstances_UseOnly_mi-internal-in-10-0-0-0-24-v11'
// type: 'Microsoft.Network/networkSecurityGroups/securityRules'
// properties: {
// description: 'Allow MI internal inbound traffic'
// protocol: '*'
// sourcePortRange: '*'
// destinationPortRange: '*'
// sourceAddressPrefix: '10.0.0.0/24'
// destinationAddressPrefix: '10.0.0.0/24'
// access: 'Allow'
// priority: 101
// direction: 'Inbound'
// }
// }
// {
// 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 Managed Instance inside the virtual network'
// protocol: 'Tcp'
// sourcePortRange: '*'
// destinationPortRange: '11000-11999'
// sourceAddressPrefix: 'VirtualNetwork'
// destinationAddressPrefix: '*'
// access: 'Allow'
// priority: 1100
// direction: 'Inbound'
// }
// }
// // {
// // name: 'allow_geodr_inbound'
// // type: 'Microsoft.Network/networkSecurityGroups/securityRules'
// // properties: {
// // description: 'Allow inbound GeoDR traffic inside the virtual network'
// // protocol: 'Tcp'
// // sourcePortRange: '*'
// // destinationPortRange: '5022'
// // sourceAddressPrefix: 'VirtualNetwork'
// // destinationAddressPrefix: '10.0.0.0/24'
// // access: 'Allow'
// // priority: 1200
// // direction: 'Inbound'
// // }
// // }
// {
// name: 'deny_all_inbound'
// properties: {
// description: 'Deny all other inbound traffic'
// protocol: '*'
// sourcePortRange: '*'
// destinationPortRange: '*'
// sourceAddressPrefix: '*'
// destinationAddressPrefix: '*'
// access: 'Deny'
// priority: 4096
// direction: 'Inbound'
// }
// }

// // Outbound
// {
// name: 'Microsoft.Sql-managedInstances_UseOnly_mi-aad-out-10-0-0-0-24-v11'
// type: 'Microsoft.Network/networkSecurityGroups/securityRules'
// properties: {
// description: 'Allow communication with Azure Active Directory over https'
// protocol: 'Tcp'
// sourcePortRange: '*'
// destinationPortRange: '443'
// sourceAddressPrefix: '10.0.0.0/24'
// destinationAddressPrefix: 'AzureActiveDirectory'
// access: 'Allow'
// priority: 100
// direction: 'Outbound'
// }
// }
// {
// name: 'Microsoft.Sql-managedInstances_UseOnly_mi-onedsc-out-10-0-0-0-24-v11'
// type: 'Microsoft.Network/networkSecurityGroups/securityRules'
// properties: {
// description: 'Allow communication with the One DS Collector over https'
// protocol: 'Tcp'
// sourcePortRange: '*'
// destinationPortRange: '443'
// sourceAddressPrefix: '10.0.0.0/24'
// destinationAddressPrefix: 'OneDsCollector'
// access: 'Allow'
// priority: 101
// direction: 'Outbound'
// }
// }
// {
// name: 'Microsoft.Sql-managedInstances_UseOnly_mi-internal-out-10-0-0-0-24-v11'
// type: 'Microsoft.Network/networkSecurityGroups/securityRules'
// properties: {
// description: 'Allow MI internal outbound traffic'
// protocol: '*'
// sourcePortRange: '*'
// destinationPortRange: '*'
// sourceAddressPrefix: '10.0.0.0/24'
// destinationAddressPrefix: '10.0.0.0/24'
// access: 'Allow'
// priority: 102
// direction: 'Outbound'
// }
// }
// {
// name: 'Microsoft.Sql-managedInstances_UseOnly_mi-strg-p-out-10-0-0-0-24-v11'
// type: 'Microsoft.Network/networkSecurityGroups/securityRules'
// properties: {
// description: 'Allow outbound communication with storage over HTTPS'
// protocol: '*'
// sourcePortRange: '*'
// destinationPortRange: '443'
// sourceAddressPrefix: '10.0.0.0/24'
// destinationAddressPrefix: 'Storage.australiacentral'
// access: 'Allow'
// priority: 103
// direction: 'Outbound'
// }
// }
// {
// name: 'Microsoft.Sql-managedInstances_UseOnly_mi-strg-s-out-10-0-0-0-24-v11'
// type: 'Microsoft.Network/networkSecurityGroups/securityRules'
// properties: {
// description: 'Allow outbound communication with storage over HTTPS'
// protocol: '*'
// sourcePortRange: '*'
// destinationPortRange: '443'
// sourceAddressPrefix: '10.0.0.0/24'
// destinationAddressPrefix: 'Storage.australiacentral2'
// access: 'Allow'
// priority: 104
// direction: 'Outbound'
// }
// }

// // These were in portal generated, may be needed
// // {
// // name: 'allow_linkedserver_outbound'
// // type: 'Microsoft.Network/networkSecurityGroups/securityRules'
// // properties: {
// // description: 'Allow outbound linked server traffic inside the virtual network'
// // protocol: 'Tcp'
// // sourcePortRange: '*'
// // destinationPortRange: '1433'
// // sourceAddressPrefix: '10.0.0.0/24'
// // destinationAddressPrefix: 'VirtualNetwork'
// // access: 'Allow'
// // priority: 1000
// // direction: 'Outbound'
// // }
// // }
// // {
// // name: 'allow_redirect_outbound'
// // type: 'Microsoft.Network/networkSecurityGroups/securityRules'
// // properties: {
// // description: 'Allow outbound TDS redirect traffic from Managed Instance inside the virtual network'
// // protocol: 'Tcp'
// // sourcePortRange: '*'
// // destinationPortRange: '11000-11999'
// // sourceAddressPrefix: '10.0.0.0/24'
// // destinationAddressPrefix: 'VirtualNetwork'
// // access: 'Allow'
// // priority: 1100
// // direction: 'Outbound'
// // }
// // }
// // {
// // name: 'allow_geodr_outbound'
// // type: 'Microsoft.Network/networkSecurityGroups/securityRules'
// // properties: {
// // description: 'Allow outbound GeoDR traffic inside the virtual network'
// // protocol: 'Tcp'
// // sourcePortRange: '*'
// // destinationPortRange: '5022'
// // sourceAddressPrefix: '10.0.0.0/24'
// // destinationAddressPrefix: 'VirtualNetwork'
// // access: 'Allow'
// // priority: 1200
// // direction: 'Outbound'
// // }
// // }
// // {
// // name: 'allow_privatelink_outbound'
// // type: 'Microsoft.Network/networkSecurityGroups/securityRules'
// // properties: {
// // description: 'Allow outbound Private Link traffic inside the virtual network'
// // protocol: 'Tcp'
// // sourcePortRange: '*'
// // destinationPortRange: '443'
// // sourceAddressPrefix: '10.0.0.0/24'
// // destinationAddressPrefix: 'VirtualNetwork'
// // access: 'Allow'
// // priority: 1300
// // direction: 'Outbound'
// // }
// // }
// // {
// // name: 'allow_azurecloud_outbound'
// // type: 'Microsoft.Network/networkSecurityGroups/securityRules'
// // properties: {
// // description: 'Allow outbound traffic to Azure Cloud, port 443'
// // protocol: 'Tcp'
// // sourcePortRange: '*'
// // destinationPortRange: '443'
// // sourceAddressPrefix: 'VirtualNetwork'
// // destinationAddressPrefix: 'AzureCloud'
// // access: 'Allow'
// // priority: 1400
// // direction: 'Outbound'
// // }
// // }
// {
// name: 'deny_all_outbound'
// properties: {
// description: 'Deny all other outbound traffic'
// protocol: '*'
// sourcePortRange: '*'
// destinationPortRange: '*'
// sourceAddressPrefix: '*'
// destinationAddressPrefix: '*'
// access: 'Deny'
// priority: 4096
// direction: 'Outbound'
// }
// }
// ]
}
}

resource routeTable 'Microsoft.Network/routeTables@2022-11-01' = {
name: 'rt-${managedInstanceName}'
location: location
properties: {
disableBgpRoutePropagation: false
// routes: [
// {
// name: 'Microsoft.Sql-managedInstances_UseOnly_subnet-10-0-0-0-24-to-vnetlocal'
// properties: {
// addressPrefix: '10.0.0.0/24'
// nextHopType: 'VnetLocal'
// hasBgpOverride: false
// }
// type: 'Microsoft.Network/routeTables/routes'
// }
// {
// name: 'Microsoft.Sql-managedInstances_UseOnly_mi-AzureActiveDirectory'
// properties: {
// addressPrefix: 'AzureActiveDirectory'
// nextHopType: 'Internet'
// hasBgpOverride: false
// }
// type: 'Microsoft.Network/routeTables/routes'
// }
// {
// name: 'Microsoft.Sql-managedInstances_UseOnly_mi-OneDsCollector'
// properties: {
// addressPrefix: 'OneDsCollector'
// nextHopType: 'Internet'
// hasBgpOverride: false
// }
// type: 'Microsoft.Network/routeTables/routes'
// }
// {
// name: 'Microsoft.Sql-managedInstances_UseOnly_mi-Storage.australiacentral'
// properties: {
// addressPrefix: 'Storage.australiacentral'
// nextHopType: 'Internet'
// hasBgpOverride: false
// }
// type: 'Microsoft.Network/routeTables/routes'
// }
// {
// name: 'Microsoft.Sql-managedInstances_UseOnly_mi-Storage.australiacentral2'
// properties: {
// addressPrefix: 'Storage.australiacentral2'
// nextHopType: 'Internet'
// hasBgpOverride: false
// }
// type: 'Microsoft.Network/routeTables/routes'
// }

// // From portal generated sqlmi - keeping for future reference
// // {
// // name: 'SqlManagement_0'
// // properties: {
// // addressPrefix: '65.55.188.0/24'
// // nextHopType: 'Internet'
// // }
// // type: 'Microsoft.Network/routeTables/routes'
// // }
// // {
// // name: 'SqlManagement_1'
// // properties: {
// // addressPrefix: '207.68.190.32/27'
// // nextHopType: 'Internet'
// // }
// // type: 'Microsoft.Network/routeTables/routes'
// // }
// // {
// // name: 'SqlManagement_2'
// // properties: {
// // addressPrefix: '13.106.78.32/27'
// // nextHopType: 'Internet'
// // }
// // type: 'Microsoft.Network/routeTables/routes'
// // }
// // {
// // name: 'SqlManagement_3'
// // properties: {
// // addressPrefix: '13.106.174.32/27'
// // nextHopType: 'Internet'
// // }
// // type: 'Microsoft.Network/routeTables/routes'
// // }
// // {
// // name: 'SqlManagement_4'
// // properties: {
// // addressPrefix: '13.106.4.96/27'
// // nextHopType: 'Internet'
// // }
// // type: 'Microsoft.Network/routeTables/routes'
// // }
// // {
// // name: 'SqlManagement_5'
// // properties: {
// // addressPrefix: '104.214.108.80/32'
// // nextHopType: 'Internet'
// // }
// // type: 'Microsoft.Network/routeTables/routes'
// // }
// // {
// // name: 'SqlManagement_6'
// // properties: {
// // addressPrefix: '52.179.184.76/32'
// // nextHopType: 'Internet'
// // }
// // type: 'Microsoft.Network/routeTables/routes'
// // }
// // {
// // name: 'SqlManagement_7'
// // properties: {
// // addressPrefix: '52.187.116.202/32'
// // nextHopType: 'Internet'
// // }
// // type: 'Microsoft.Network/routeTables/routes'
// // }
// // {
// // name: 'SqlManagement_8'
// // properties: {
// // addressPrefix: '52.177.202.6/32'
// // nextHopType: 'Internet'
// // }
// // type: 'Microsoft.Network/routeTables/routes'
// // }
// // {
// // name: 'SqlManagement_9'
// // properties: {
// // addressPrefix: '20.36.105.0/32'
// // nextHopType: 'Internet'
// // }
// // type: 'Microsoft.Network/routeTables/routes'
// // }
// // {
// // name: 'SqlManagement_10'
// // properties: {
// // addressPrefix: '20.36.108.0/27'
// // nextHopType: 'Internet'
// // }
// // type: 'Microsoft.Network/routeTables/routes'
// // }
// // {
// // name: 'SqlManagement_11'
// // properties: {
// // addressPrefix: '20.36.75.75/32'
// // nextHopType: 'Internet'
// // }
// // type: 'Microsoft.Network/routeTables/routes'
// // }
// // {
// // name: 'SqlManagement_12'
// // properties: {
// // addressPrefix: '20.36.46.220/32'
// // nextHopType: 'Internet'
// // }
// // type: 'Microsoft.Network/routeTables/routes'
// // }
// // {
// // name: 'SqlManagement_13'
// // properties: {
// // addressPrefix: '13.72.242.159/32'
// // nextHopType: 'Internet'
// // }
// // type: 'Microsoft.Network/routeTables/routes'
// // }
// // {
// // name: 'SqlManagement_14'
// // properties: {
// // addressPrefix: '20.36.46.90/32'
// // nextHopType: 'Internet'
// // }
// // type: 'Microsoft.Network/routeTables/routes'
// // }
// ]
}
}

resource sqlSubnet 'Microsoft.Network/virtualNetworks/subnets@2022-11-01' = {
name: '${virtualNetworkName}/ManagedInstance'
properties: {
addressPrefix: subnetAddressPrefix
networkSecurityGroup: {
id: sqlNSG.id
}
routeTable: {
id: routeTable.id
}

privateEndpointNetworkPolicies: 'Disabled'
delegations: [
{
name: 'managedInstanceDelegation'
properties: {
serviceName: 'Microsoft.Sql/managedInstances'
}
}
]
}
}

resource managedInstance 'Microsoft.Sql/managedInstances@2022-11-01-preview' = {
name: managedInstanceName
location: location
sku: {
name: skuName
}
identity: {
type: 'SystemAssigned'
}
properties: {
administrators: {
administratorType: 'ActiveDirectory'
azureADOnlyAuthentication: true
login: administratorLogin
principalType: administratorPrincipalType
sid: administratorSid
}
subnetId: sqlSubnet.id
storageSizeInGB: storageSizeInGB
vCores: vCores
licenseType: licenseType
publicDataEndpointEnabled: false
minimalTlsVersion: '1.2'
collation: 'SQL_Latin1_General_CP1_CI_AS'
timezoneId: timeZone
}
}

output managedInstanceId string = managedInstance.id
```

### Confirm

- [X] I have read the troubleshooting guide and looked for duplicates.

Reproduce the behavior by deploying main.bicep and rerunning it, then inspect virtualNetwork.bicep and sqlManagedInstance.bicep around the subnet and managed-instance resources. Compare the warnings and deployment errors for networkIntentPolicies and virtualClusterId; done means the template can be rerun without those warnings or errors.

Written by the indexing model from the issue text.