"Network-related or instance-specific error" while attempting connect to a SQL Server on remote computer on home network

Question

I have two laptops (both running Windows 10, 64-bit) connected to my home network using wifi. Here are details about each laptop (please ask if you need more info):

Laptop #1

2017 Microsoft SQL Server Express (64-bit)
SQL Server Management Studio v17.9.1
Engine: Named Instance (SQLEXPRESS)
Local IP: 10.0.0.5

Laptop #2

2019 Microsoft SQL Server Developer (64-bit)
SQL Server Management Studio v18.4
Engine: Unnamed/Default Instance (MSSQLSERVER)
Local IP: 10.0.0.53

I'd like Laptop #1 to be the server and Laptop #2 to be the client connecting to that server.

Following things I made sure on Laptop #1:

1. I tried pinging 10.0.0.53 which SUCCEEDED.
2. Enable TCP/IP in SQL Server Configuration Manager.
3. Inside TCP/IP Properties, under IPAll, TCP Dynamic Ports = [Blank] (I cleared it) and TCP Port = 1433. I went into SQL Server Services and restarted the SQL Server service.
4. Inside TCP/IP Properties, under IP8, Active = Yes, Enabled = Yes, TCP Dynamic Ports = [Blank], TCP Port = 1433.
5. Created an inbound Windows-Firewall rule for TCP Port 1433 with "Private" group.
6. In Laptop #1's SQL Server Management Studio, under Server Properties > Security, Server authentication = SQL Server and Windows Authentication mode.
7. In Laptop #1's SQL Server Management Studio, under Server Properties > Connections, Remote server connections = Allow remote connections to this server [ticked].
8. I've added a user to my database and allowed him to read/write to tables.

Following things I tried from Laptop #2:

1. I tried pinging 10.0.0.5 which SUCCEEDED (all packets came through)
2. Server name: 10.0.0.5\SQLEXPRESS,1433, Authentication = SQL Server Authentication, Login = ..., Password = ...
3. No windows firewall rules created. In SQL Server Configuration Manager (I enabled TCP/IP and noted that IPAll::TCP Port = 1433 and IPAll::TCP Dynamic Ports = [Blank].

But still getting following error:

Cannot connect to 10.0.0.5\SQLEXPRESS,1433. 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: TCP Provider, error: 0 - The wait operation timed out.) (Microsoft SQL Server, Error: 258)

Troubleshooting History

At first I was able to ping Laptop #1 from Laptop #2 but not the other way around. As it turns out, Laptop #2 (being new) had McAfee which configured my firewall settings and disabled ICMP IPv4. I uninstalled McAfee, rebooted, and made sure inbound rules "File and Printer Sharing (Echo Request - ICMPv4-In)" for "private/public/domain groups" (and for "*-ICMPv6-In") are "enabled". Afterwards, I was able to ping Laptop #2 from Laptop #1. I still get the same error when trying to log in.

Following some DAOverflow member's advice, I checked SQL Server Logs to see which ports the database Engine listened on. The logs (which can be found under Management > SQL Server Logs) contained the following:

  Date    Source    Message
  ...   | spid16s | Server is listening on ['any' <ipv4> 1433]
  ...   | spid16s | Server is listening on ['any' <ipv6> 1433]

I enabled Telnet Client on Laptop #2's powershell but could not connect to the host 10.0.0.5:1433.

Update

I talked to a friend who suggested turning off Windows Firewall on Laptop #1. I did so and afterwards was able to telnet to Laptop #1 from #2. I was also able to locate the SQL server on Laptop #1, login, and view tables. The issue appears to be with my Windows Firewall Settings.

Solution

On Laptop #1 I had to create two inbound rules: TCP Port 1433 and UDP Port 1434. Both rules apply to Domain, Private, and Public profiles. After enabling both rules, I was able to connect to the SQL Server. I also did some further testing:

I disabled UDP 1434 (leaving TCP 1433 enabled) and sure enough I couldn't log in (nor see the server be listed on the network when "Browsing for more [servers]" on the Connect to Server dialog window).

I disabled TCP 1433 (leaving UDP 1434 enabled) and sure enough I couldn't log in though the server was listed on the Browse for Servers dialog window.

Answer 1

What ports are permitted for named instances? SQL Server Logs (under Management > SQL Server Logs) has two entries from Source = spid16s with the same messages: Message = Server is listening on ['any' 1433]

By default named instances are bounded with dynamic ports and SQL Browser service is the one redirects the client connections to named instances. For this to happen you need ensure following.

• Ensure SQL Browser service is running
• Create rule in windows fire-wall (where SQL Service and SQL Browser services are installed) to accept in-bond communication on port numbers UDP: 1434 and TCP:1433

You could also make connections without relaying on SQL browser service, for that you must configure custom port and use port number in connection string instead of instance name (example: 10.0.0.5,1435)

Once you got this message (['any' 1433]) in SQL Error log, the SQL server is ready to accept connections, but in your case there is something else blocking your connections. Most probably it's firewall, Create a rule (as mentioned above), do telnet again, if it works, you should be able to make connection. If your unable after firewall entry, make-sure there is no network level blocking happening on those specific port numbers.

I enabled Telnet Client on Laptop #2's powershell but could not connect to the host 10.0.0.5:1433.

Until telnet succeeded the connections cannot be established

Answer 2

Make sure the below requirements are met.

1. Do a ping from laptop 1 to laptop 2 and vice versa. Make sure it's able to ping

2. Check the ports are open, so no firewall blocking is there. Use TELNET command for this.

3. if it fails on that we have to check SQL server network configuration settings.

Answer 3

Once you start configuring the TCP/IP settings for a SQL Server instance, there are multiple things you have to be aware of.

SQL Server Browser Service

The SQL Server Browser Service is responsible for redirecting SQL Server Connection Requests to the corresponding instance.

Is the SQL Server Browser Service running on the server?

Yes

You should be able to connect to the SQL Server instance using the following connection strings:

• 10.0.0.53\SQLExpress
• 10.0.0.53\SQLExpress,1433

This is because the Browser service knows the instances running (i.e. \SQLExpress) and will redirect requests to the corresponding instance-specific sqlserver.exe process.

No

You should be able to connect to the SQL Server instance using the following connection strings:

• 10.0.0.53
• 10.0.0.53,1433

This is because the instance has been configured to listen on the dedicated TCP Port 1433 for a specific IP address. No redirection via the SQL Server Browser service.

If all else fails...

Check the ERRORLOG of your SQL Server instance on the "server". It will contain information on which port and IP the instance is currently listening. Use this information to connect to your server.

2019-12-02 15:23:05.17 spid15s     Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2019-12-02 15:23:05.17 spid15s     Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
2019-12-02 15:23:05.18 Server      Server is listening on [ ::1 <ipv6> 1434].
2019-12-02 15:23:05.18 Server      Server is listening on [ 127.0.0.1 <ipv4> 1434].
2019-12-02 15:23:05.18 Server      Dedicated admin connection support was established for listening locally on port 1434.
2019-12-02 15:23:05.18 spid15s     SQL Server is now ready for client connections. This is an informational message; no user action is required.

Good luck.

Further Reading

I have written a Q&A What are valid connection strings for SSMS login box? on how to determine which connection strings you can use for various IP configurations. It contains links to various other web pages with additional information.