Download articleAzure SQL Managed Instance is a fully managed PaaS SQL Server Database Engine hosted in Azure cloud that is placed in Azure Virtual network, and you need to ensure that your network is properly configured. In this post your will see a PowerShell script that can validate the Azure network that you prepared for the Managed Instance.
In the previous posts we saw how to create a new Azure Network environment where Managed instance will be deployed via PowerShell or ARM. Often customers want to deploy Managed Instance to an existing Virtual Network (Vnet) & Subnet in their subscription. However the deployment shall fail if the subnet does not meet requirements outlined here.
In this post we share a PowerShell script authored by Rohit Nayak that checks a specific subnet within your Vnet against the pre-requisites for hosting Managed Instance. Please ensure you have installed Azure RM PowerShell .
The following parameters are required for the script below to run:
- Subscription ID where you placed the Azure VNet where you want to deploy Managed Instances
- Resource group name – This is the resource group that contains the Virtual network – and where your Managed Instance will be eventually deployed
- Virtual Network (Vnet) Name – the name of the Azure VNet where your want to deploy your SQL Managed Instance
- Subnet Name – This is the subnet within your Virtual network which you plan to use for Managed Instance
Validation can be done using a simple PowerShell function call where you provide parameters described above:
1 2 3 4 5 6 7 | ##Inputs that end user needs to fill in manually $azSubscriptionName = "Replace with your subscription id" $azResourceGroupName = "Replace with your Resource group name" $azVnetName = "Replace with your Virtual network name" $azSubnetName = "Replace with your Subnet name" Validate-Subnet-For-MI -inSubscriptionName $azSubscriptionName -inResourceGroupName $azResourceGroupName -inVnetName $azVnetName -inSubnetName $azSubnetName |
The output will be something like below, listing out all the checks that were done against the subnet and which ones passed (in green) vs failed( in red)
Here is the PowerShell script that performs these validation checks:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 | function Validate-Subnet-For-MI { [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [String] $inSubscriptionName, [Parameter(Mandatory = $true)] [String] $inResourceGroupName, [Parameter(Mandatory = $true)] [String] $inVnetName, [Parameter(Mandatory = $true)] [String] $inSubnetName ) begin { #Login to Azure Login-AzureRMAccount | Out-null Select-AzureRmSubscription -SubscriptionName $inSubscriptionName | Out-null $vnet = Get-AzureRmVirtualNetwork -ResourceGroupName $inResourceGroupName -Name $inVnetName #Start with getting the vnet details Write-Host("--------------------- Vnet Details ------------------------------------- ") -ForegroundColor Yellow Write-Host ("Vnet ID = {0}" -f $vnet.ID.ToString()) -ForegroundColor Yellow Write-Host ("Vnet Address Space = {0}" -f $vnet.AddressSpaceText.ToString()) -ForegroundColor Yellow Write-Host("------------------------------------------------------------------------ ") -ForegroundColor Yellow } process { $subnets = $vnet.Subnets.Name if ( $true -eq $subnets.contains($azSubnetName)) { Write-Host("--- Begin validation of subnet {0} in {1} ---" -f $inSubnetName, $vnet.Name) #Get the subnet object $idx = $subnets.IndexOf($azSubnetName) $sn = $vnet.Subnets[$idx] $bIsSubnetValidForMI = $true Write-Host("") Write-Host("---------- Subnet Name {0} [ Subnet Address Range {1} ] --------------- " -f $sn.Name, $sn.AddressPrefix) -ForegroundColor Yellow Write-Host("") # Check that subnet is not a Gateway subnet if ($azSubnetName -eq "GatewaySubnet") { Write-Host ("Warning - Subnet {0} in virtual network {1} is possibly Gateway Subnet. Please check subnet configuration in Portal before continuing with deployment" -f $sn.Name, $vnet.Name ) -ForegroundColor Yellow } # Check that subnet does not have a Network Security Group associated with it. $sn.NetworkSecurityGroup| Format-List | Out-Host if ($true -eq [System.String]::IsNullOrEmpty($sn.NetworkSecurityGroup)) { Write-Host("Passed Validation - NSG is empty subnet {0} in virtual network {1}" -f $sn.Name, $vnet.Name) -ForegroundColor Green Write-Host("") } else { Write-Host ("Failed Validation - Found NSG associated with subnet {0} in virtual network {1}" -f $sn.Name, $vnet.Name ) -ForegroundColor Red $bIsSubnetValidForMI = $false } ## Route table specific checks # Check that subnet is associated with a route table $rt = $sn.RouteTable if ($false -eq [System.String]::IsNullOrEmpty($rt)) { $rtName = ($rt.Id).Split("{/}")[-1].Trim() Write-Host("Passed Validation - Route table {0} is associated with subnet {1} in virtual network {2}" -f $rtName, $sn.Name, $vnet.Name) -ForegroundColor Green Write-Host("") Write-Host("---------- Route Table {0} Details --------------- " -f $rtName) -ForegroundColor Yellow Write-Host("") $tblRoutes = Get-AzureRmRouteTable -ResourceGroupName $inResourceGroupName -Name $rtName $tblRoutes.Routes| Select-Object -Property Name, AddressPrefix, NextHopType | Format-Table| Out-Host Write-Host("") # Check if route table has BGP route propagation set to Enabled if ($false -eq $tblRoutes.DisableBgpRoutePropagation) { Write-Host("Passed Validation - Route table {0} has BGP Propagation = Enabled" -f $tblRoutes.Name) -ForegroundColor Green Write-Host("") } else { Write-Host("Warning - Route table {0} has BGP Propagation = Disabled. This could affect connectivity to resources behind VPN Gateway" -f $tblRoutes.Name) -ForegroundColor Yellow } #Check if route table contains more than one rule if ($tblRoutes.Routes.count -eq 1) { Write-Host("Passed Validation - Route table {0} has only one rule" -f $tblRoutes.Name) -ForegroundColor Green Write-Host("") } else { Write-Host("Failed Validation - Route table {0} has more than one rule " -f $tblRoutes.Name) -ForegroundColor Red $bIsSubnetValidForMI = $false } # Check route table has a route that is 0.0.0.0/0 Next Hop Internet $nextHopRule = $tblRoutes.Routes| Where-Object { $_.AddressPrefix -eq "0.0.0.0/0" -and $_.NextHopType -eq "Internet" }| Select-Object -Property Name, AddressPrefix, NextHopType if ($false -eq [System.String]::IsNullOrEmpty($nextHopRule)) { Write-Host("Passed Validation - Route table {0} has 0.0.0.0/0 rule" -f $tblRoutes.Name) -ForegroundColor Green Write-Host("") } else { Write-Host("Failed Validation - Route table {0} is missing 0.0.0.0/0 rule " -f $tblRoutes.Name) -ForegroundColor Red $bIsSubnetValidForMI = $false } } else { Write-Host ("Failed Validation -No Route table is associated with subnet {0} in virtual network {1}" -f $sn.Name, $vnet.Name ) -ForegroundColor Red $bIsSubnetValidForMI = $false } # Check that subnet does not have a Service endpoint (Storage or Sql) associated to it $sn.ServiceEndpoints| Format-List | Out-Host if ($true -eq [System.String]::IsNullOrEmpty($sn.ServiceEndpoints)) { Write-Host("Passed Validation - Service endpoints are empty for subnet {0} in virtual network {1}" -f $sn.Name, $vnet.Name ) -ForegroundColor Green Write-Host("") } else { Write-Host ("Failed Validation - Found Service Endpoint associated with subnet {0} in virtual network {1}" -f $sn.Name, $vnet.Name ) -ForegroundColor Red $bIsSubnetValidForMI = $false } # Optional custom DNS: If custom DNS is specified on the VNET, Azure's recursive resolvers IP address (such as 168.63.129.16) must be added to the list If ($vnet.DhcpOptions.DnsServers.Count -eq 0 ) { Write-Host("Passed Validation - No custom DNS is defined for virtual network {0}" -f $vnet.Name ) -ForegroundColor Green Write-Host("") } else { Write-Host("Custom DNS is defined for virtual network {0}" -f $vnet.Name ) -ForegroundColor Yellow #Write-Host("Please add Azure's recursive resolvers IP address (such as 168.63.129.16) to the list of DNS Servers for virtual network {0}" -f $vnet.Name ) -ForegroundColor Yellow $isAzureRRAdded = $false Write-Host("---------- DnsServers ----------") -ForegroundColor Yellow foreach ($entry in $vnet.DhcpOptions.DnsServers) { Write-Host($entry.ToString()) if ($entry.ToString() -eq "168.63.129.16") { $isAzureRRAdded = $true } } if ($isAzureRRAdded -eq $true) { Write-Host("Passed Validation - Azure's Recursive resolver IP address 168.63.129.16 is added to DNS Servers for virtual network {0}" -f $vnet.Name ) -ForegroundColor Green Write-Host("") } else { Write-Host("Failed Validation - Azure's Recursive resolver IP address 168.63.129.16 is not added to DNS Servers for virtual network {0}" -f $vnet.Name ) -ForegroundColor Red $bIsSubnetValidForMI = $false } } if ($bIsSubnetValidForMI -eq $true) { Write-Host("---------- Subnet Name {0} in Vnet {1} passed all pre-requisite checks for SQL Managed Instance --------------- " -f $sn.Name, $vnet.Name) -ForegroundColor Green } else { Write-Host("---------- Subnet Name {0} in Vnet {1} did not pass all pre-requisite checks for SQL Managed Instance --------- " -f $sn.Name, $vnet.Name) -ForegroundColor Red } Write-Host("--- End validation of subnet {0} in {1} ---" -f $inSubnetName, $vnet.Name) } else { Write-Host("--- Subnet{0} does not exist in vnet {1} ---" -f $inSubnetName, $vnet.Name) -ForegroundColor Red } } end { } }<span><span> </span></span> |