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:
- Took an image from Azure gallery for Windows Server 2016 with no SQL installed on it.
- Downloaded SQL Server 2019 (CTP as if now) and installed it.
- 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)
As soon as I hit connect, I was not able to connect and getting below error.
Cannot connect to XXX.YYY.219.81.
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.
In my case, this was a Windows VM and SQL Server was installed manually so I don’t have that option.
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.
.. and I was able to connect to SQL Server from my laptop now.
Reference: Pinal Dave (https://blog.sqlauthority.com)
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