9

I get the following message when trying to connect to SQL database from a remote machine using MS Management Studio with Server Name [ XX.XXX.XXX.XXX,1433\MyServerName ] and SQL authentication:

Cannot connect to XX.XXX.XXX.XXX,1433\MyServerName.

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)

The wait operation timed out

I have done the following:

  • Allowed remote connection in Server Properties window in SQL Management Studio
  • Configured server machine to have static IPv4/DNS addresses.
  • Enabled TCP/IP in SQL Configuration Manager (in SQL Server Network Configuration)
  • In TCP/IP properties, adjusted [ Protocol -> Listen All -> No ] & set IP[1-8] to TCP port 1433 and IPAll port to 1433 (TCP dynamic ports left blank)
  • Set Inbound/Outbound enable connection (Domain, public, private) rules in Windows Firewall for ports TCP 1433, UDP 1434 (should make no difference, since port is specified), service executables: sqlbrowser.exe in C:\Program Files (x86)\Microsoft SQL Server\90\Shared (not used I think because port is specified) and sqlservr.exe in C:\Program Files\Microsoft SQL Server\MSSQL13.MyServerName \MSSQL\Binn
  • Set NAT actions in server-side router for ports 1433 (TCP), 1434 (UDP) (again, should not matter) to server's local IPv4 address and ports 1433, 1434 accordingly.
  • Made sure SQL service is running, ports are open and listening on server machine.

I can connect to the database on server machine using Windows or Sql credentials with either PC name, or machine's local IPv4.

Interesting thing is, when >telnet XX.XXX.XXX.XXX 1433 is run, it takes about 10-12 seconds to fail (Could not open connection to the host, on port 1433: Connect failed), but I can see the 2 packets coming to the router itself. Same with the login using SQL Manager - packets come to the router, but either server machine refuses (doesn't resolve?) the connection, or it doesn't even get to the machine.

Appreciate your suggestions.

Versions used:

  • MS SQL Server 2016 Express, MS SQL Management Studio 17, SQL Server 2016 Configuration Manager, Windows 10 Pro.
L. Rendagan
  • 91
  • 1
  • 1
  • 4
  • This part "and sqlservr.exe in C:\Program Files\Microsoft SQL Server\MSSQL13.MyServerName \MSSQL\Binn" save my day...thanks dude! – Cabuxa.Mapache Nov 24 '22 at 11:29

1 Answers1

3

Have you tried telnet on 1433 locally?

Are you sure your local connection is using TCP/IP? By default, local connections use DBNMPNTW (named pipes) which uses shared memory on the back end for local connections and is faster than TCP/IP.

When SQL Server is working correctly on TCP/IP telnet will connect and leave you blinking waiting for protocol-- it doesn't hang for 10-12 seconds and fail.

If telnet works locally but not remotely, then you know the problem is in the networking layer (firewall, NAT, routing, etc.). If it does not, then the problem is in the SQL configuration setup. Did you restart the service after changing the connection settings?

Tim
  • 5,940
  • 1
  • 12
  • 18
  • 1
    Thanks for the reply. 1) Yes, I tried `>telnet [local IPv4 1433]` and `>telnet [localhost 1433]` on the server: both succeeded. 2) I'm not completely sure how to check if connection uses named pipes, or is it a factor for remote connections 3) Services are restarted every time anything is changed. – L. Rendagan Aug 25 '17 at 09:58
  • Did you ever get this solved? I have the same issue now. – Paula Pipkin Aug 09 '23 at 21:56