Unable to connect to SQL Server AlwaysOn Listener on Azure due to misconfigured subnet mask
When it comes to troubleshooting, the more you know the better troubleshooter you are (in my option). Sometimes knowing more actually gets in the way. We repeatedly get calls where customers couldn’t connect to AlwaysOn Listener on Azure because of the misconfiguration of the subnet mask for the listener IP.
They receive this error
“A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)”
In Configure an ILB listener for Always On Availability Groups in Azure, there is a step where you customize the following Powershell script to configure the listener before bringing it online.
# Define variables
$ClusterNetworkName = "<MyClusterNetworkName>" # the cluster network name (Use Get-ClusterNetwork on Windows Server 2012 of higher to find the name)
$IPResourceName = "<IPResourceName>" # the IP Address resource name
$ILBIP = “<X.X.X.X>” # the IP Address of the Internal Load Balancer (ILB)
Import-Module FailoverClusters
Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{"Address"="$ILBIP";"ProbePort"="59999";"SubnetMask"="255.255.255.255";"Network"="$ClusterNetworkName";"EnableDhcp"=0}
In the last line above, the subnet mask is 255.255.255.255 for the IP resource. But many users assume that they need to change to match the subnet mask of the IP for the listener. For example, they would put 255.255.255.192 etc. Subnet mask 255.255.255.255 is the correct one and shouldn’t be changed. In fact https://support.microsoft.com/en-us/kb/2854082 specifically provides the fix to make this work for cluster in Windows 2012 and 2008 servers. A user who doesn’t have that much knowledge in networking might leave it alone but a user who knows networking may change it assuming it needs to ‘match’ the subnet where the IP address is chosen from. But you should not change the subnet mask. If you change it, you will have difficulty connecting to the listener.
Jack Li |Senior Escalation Engineer | Microsoft SQL Server