Sql-server – “Network-related or instance-specific error” while attempting connect to a SQL Server on remote computer on home network

connectivitysql server

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.

Best Answer

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

Related Question