Sql-server – Connection issues with Azure DB from SSMS

azure-sql-databaseconnectivitysql serverssms

I am currently making a connection to Azure DB from SSMS using public wifi. For the past few days, I was able to connect SSMS to Azure DB without any issues by adding the current ip address into the server firewall. However, today the following error came up:

Error 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 the SQL Server is configured to allow remote connections.
(provider: TCP Provider, error: 0 -No connection could be made because the target machine actively refused it.)(Microsoft SQL Server Error: 10061)
No connection could be made because the target machine actively refused it.

Things that I have tried to correct it

  • Check and retyped server name, login and password
  • Enable all SQL Server Config protocol
  • Manually add ip address to azure SQL Server firewall
  • Enable SQL server through firewall

However the issue is still not resolved, so what should I do?

Results from pinging the name of the Azure SQL server

Pinging eastasia1-a.control.database.windows.net [191.234.2.139] with 32 bytes of data:

Request timed out.

Request timed out.

Ping statistics for 191.234.2.139:
Packets: Sent = 4, Received = 0, Lost = 4 (100% loss),

C:>telnet 191.234.2.139 1433

Connecting To 191.234.2.139…Could not open connection to the host, on port 1433: Connect failed

C:>tracert 191.234.2.139

Tracing route to 191.234.2.139 over a maximum of 30 hops

1 2 ms 1 ms 2 ms 10.27.0.2

2 2 ms 2 ms 2 ms 172.30.17.1

3 2 ms 1 ms 2 ms 172.30.2.189

4 * * * Request timed out.

5 * * * Request timed out.

*Rest is request timeout

Best Answer

Let start by making sure you are using the current IP of your Azure SQL Database server. Let's ping the name of the Azure SQL server. The ping will fail but it should also return the current IP of the SQL Azure Server if DNS resolution is working.

C:\>ping <myserver>.database.windows.net

Pinging data.sn1-1.database.windows.net [65.55.74.144] with 32 bytes of data:
Request timed out.
Request timed out.
Request timed out.
Request timed out.
Ping statistics for 65.55.74.144:
Packets: Sent = 4, Received = 0, Lost = 4 (100% loss)

Now try to telnet to port 1433 using the IP address returned.

C:\>telnet 65.55.74.144 1433

Connecting To 65.55.74.144...

If Telnet is successful, the window will change to a completely blank screen. Try connecting with that IP using SSMS again. If telenet is not successful, try tracert to identify at what step it fails to reach the Azure SQL Database server.

C:\>tracert 65.55.74.144

Tracing route to 65.55.74.144 over a maximum of 30 hops
1 2 ms 1 ms 1 ms 192.168.0.1
2 6 ms 5 ms 5 ms 10.33.34.50
3 5 ms 4 ms 5 ms 4-1-753.DLLSTX-LCR-07.verizon-gni.net [130.81.107.26]
4 8 ms 5 ms 5 ms so-6-2-0-0.DFW01-BB-RTR1.verizon-gni.net [130.81.28.208]
5 44 ms 43 ms 44 ms so-11-0-0-0.LCC1-RES-BB-RTR1-RE1.verizon-gni.net [130.81.17.40]
6 49 ms 44 ms 44 ms so-6-0-0-0.ASH-PEER-RTR1-re1.verizon-gni.net [130.81.10.90]
Related Question