SQL SERVER – FIX: Cannot Connect to SQL in Azure Virtual Machine From Laptop

SQL
1 Comment

To keep up with the market trend, I have already done my hands-on with SQL in Azure Virtual Machine. In this blog, I would share my learning about a connectivity issue which I faced recently.

Here were the steps I followed to get my server up and running:

  1. Took an image from Azure gallery for Windows Server 2016 with no SQL installed on it.
  2. Downloaded SQL Server 2019 (CTP as if now) and installed it.
  3. Made sure I am able to connect to SQL Server locally and it was working great.

Now, I opened SSMS on my laptop, provided Public IP Address of this Azure Virtual Machine (taken from the portal)

SQL SERVER - FIX: Cannot Connect to SQL in Azure Virtual Machine From Laptop azure-vm-conn-01

As soon as I hit connect, I was not able to connect and getting below error.

Cannot connect to XXX.YYY.219.81.
ADDITIONAL INFORMATION:
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)
The network path was not found

This error is explained in one of my most visited blogs:

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

The only thing which is missing in that blog is Azure part because that’s the only difference in SQL in On-Premise and SQL in Azure VM.

When I checked some blogs, they referred to “SQL Server Configuration” blade like below which can be used to allow remote connections.

SQL SERVER - FIX: Cannot Connect to SQL in Azure Virtual Machine From Laptop azure-vm-conn-02

In my case, this was a Windows VM and SQL Server was installed manually so I don’t have that option.

WORKAROUND/SOLUTION

Based on my troubleshooting, I knew that somewhere port 2433 (I generally change the port of SQL from a default (1433) to something else) is getting blocked.

Later, I learned that there is something called as “Network Security Group” in Azure which is like a firewall to allow/deny the connectivity to ports.  Securing Azure Virtual Machines using Network Security Groups (NSGs)

Here is where I have added 2433 inbound.

SQL SERVER - FIX: Cannot Connect to SQL in Azure Virtual Machine From Laptop azure-vm-conn-03

.. and I was able to connect to SQL Server from my laptop now.

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

, , , , , ,
Previous Post
SQL SERVER – Unable to Set Cloud Witness. Error: The Client and Server Cannot Communicate, Because They do Not Possess a Common Algorithm
Next Post
SQL SERVER – FIX: Msg 9514 – XML Data Type is Not Supported in Distributed Queries

Related Posts

1 Comment. Leave new

  • hey Pinal i would really appreciate your help on this

    After migrating Databases from SQL server to AZure SQLdatabases using DMA logins and users cannot connect to database using SSMS. FYI I already enabled all the logins after the migration

    Reply

Leave a Reply

Menu