SQL SERVER – Unable to Connect to SQL Server in Azure Virtual Machine from SSMS on On-Premise Machine

From past few months, I am seeing a few of my client moving their development workload to Cloud. When I asked the reason, it was mostly cost reduction in terms of hardware lifetime, licensing and connectivity advantage and their developer can work from anywhere. In this blog post, we will learn about how to fix error related to Azure Virtual Machine.

One standard issue which I have heard many times is that users are unable to connect to SQL Server from their local machine. Most of the time they get an error.

Here is the text of the message.

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: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

I have written a blog long ago about this error. You can go through it by following below link.

SQL SERVER – FIX : ERROR : (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: )

There are some additional steps needed when SQL Server is in Azure VM and SSMS is local. To fix this issue, I have created below checklist based on my research on the internet and various forums. This checklist starts with elementary checks.

1. Check if SQL Server service is running in the Virtual Machine. If not, start it. If unable to start, then follow below blog.

Top 3 Reasons Your SQL Server Service Is Not Starting

Essentially, you need to look at ERRORLOG and Event Logs to find and fix the issue.

2. Check if SQL Server is listening on TCP protocol.
3. Check and make a note of SQL Server port on which SQL is listening.

SQL SERVER – Unable to Start SQL Service – Server TCP provider failed to listen on [‘any’ 1433]. Tcp port is already in use.

4. This is the most common mistake I have seen from my few engagements. You need to check Network Security Group attached to Azure VM. Also, check the port allowed in networking interface (NIC Card) for this VM in Azure Portal. Here is the place to check.

On above, we need to choose the right port in NSG.

5. Make sure windows firewall in the virtual machine is allowing inbound connection on the port where SQL is listening. Refer step # 3 in this blog
6. If you are connecting the named instance, then check if browser service is running.

Most of the times, I followed above 6 items and solve the issue. If you have more steps, please share via comments to help others.

Reference: Pinal Dave (https://blog.sqlauthority.com)