Unable to connect to SQL Server on azure VM due to an extra NSG applied to subnet

If you need to open up your SQL Server on an Azure VM to public internet access, you need to look no further than this document Connect to a SQL Server Virtual Machine on Azure (Resource Manager). It has very detailed step-by-step instructions.

On a very high level, here are steps

1. In your VM, create an inbound rule for port like 1433 SQL Server listens to in Windows Firewall configuration.
2. In Azure portal, you create an inbound rule in the Network Security Group (NSG) associated with the network interface on that VM
3. configure a public IP/DNS

This will enable you to access your SQL Server from internet.  We got a customer call who was very puzzled.   He did exactly that but couldn’t get it to work and got a very generic message like below.  So he decided to contact us.

“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)”

 

We started to verify to make sure everything was correct.  First we made sure that the inbound rule in the network interface NSG had port 1433 configured.  Secondly, we verified that he was able to connect to the port/SQL from a different VM in side the same VNET. netstat –aon clearly showed that SQL Server process was listening on port 1433.  But we still couldn’t connect from outsize Azure/VNET.   We couldn’t telnet either to the port.  It couldn’t have been the VM itself because he could remote in with the RDP port.

Solution

 

After help from our networking team, we realized what the problem was. Another NSG was applied to the subnet in which the VM resided. His teammates must have done that without tell him!  That NSG didn’t allow port 1433.  The most restrictive rule applied because this subnet NSG doesn’t have port 1433 defined.  Though this is not common, it can happen.  So make sure you check NSG at network interface and subnet level for connectivity issues.

Screenshot 1 shows that the VM sql2008R2 doesn’t not have NSG defined for subnet default because the “Network Security Group” shows “None”

Screenshot 2 shows that the VM sql2008R2 has an NSG defined for subnet default because the “Network Security Group” shows “testNSG_subnet”

Screenshot 3 shows how you can get the the network  security group to add inbound rules.

 

screenshot 1

 

 

screenshot 2

 

screenshot 3

 

Jack Li |Senior Escalation Engineer | Microsoft SQL Server

twitter| pssdiag |Sql Nexus

Comments

• Anonymous
  September 18, 2016
  thank you, do you think its secure?
• Anonymous
  September 18, 2016
  hello , im sorry i had a mistake. its very secure.thank youگرانیت مشکی سرکان
• Anonymous
  September 18, 2016
  Well i have been trying to fix this thing for long, finally got it fixed thanks to you.