Unable to connect to SQL Server on Azure Virtual Machine–VNET Problem

  • Article

When moving your SQL Servers from on-premises to Azure cloud, minimal efforts are required to do the move. However, there are some subtle differences that you need to pay attention. One thing you need to plan is the networking piece.  Azure Virtual Networks (VNET) serves as a level of isolation that secures your resources and also can be a bridge to your on-premises resources. We have seen repeated mistakes by SQL Server users where they took all the effort creating SQL Server VMs and client VMs (like IIS) but they ended up not able to talk to each other.  They created the VMs in different VNETs. VMs in different VNET can’t communicate with each other unless you create VPN using Dynamic gateway.    They will receive the following error.

A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and i SQL Server is configured to allow remote connections. For more information see SQL Server Books Online

It’s very easy to make such mistake.  In Azure Resource Manager deployment mode, the new portal defaults to create a new VNET for new VM creation.   Here are a couple of ways to solve the problem.

 

Option 1:  create Site-to-Site (VNET to VNET) VPN connection using gateway.

For classic deployment,  you can use https://azure.microsoft.com/en-us/documentation/articles/virtual-networks-configure-vnet-to-vnet-connection/.  For resource manager deployment, you can use https://azure.microsoft.com/en-us/documentation/articles/vpn-gateway-vnet-vnet-rm-ps/.

Be aware that gateway created/used will incur ongoing monthly charges.

 

Option 2: move client VM to the same VNET as SQL Server

Moving a VM to a different VNET is not just a setting change.  You basically have to physically provision a new VM.

For classic deployment VMs, you can use this steps

  1. Shutdown your VM, and make a copy of your VHD
  2. Delete your VM but do NOT delete the VHD
  3. use old portal to create VM.  “New”—>“Compute”->”Virtual Machine”—>“from Gallery”—>  choose “My Disks”.  Then following instructions and make sure to choose the right VNET to create the new VM.

 

For Azure Resource Manager(ARM) deployment VMs, there is a very detailed article (https://blogs.technet.microsoft.com/espoon/2016/03/11/moving-an-arm-vm-to-a-new-vnet/) using powershell documented by our networking colleagues Eric Ashton and Eric Spooner. 

 

Note that you can’t mix VNETs created between ARM and classic though.

 

 

Jack Li |Senior Escalation Engineer | Microsoft SQL Server

twitter| pssdiag |Sql Nexus