Sql ошибка 258

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.

September 1, 2015 by Sameer

Possible Root Cause: Unstable Network connectivity to database server or problems with a server in the route

Steps Taken To Confirm Root Cause: 

Setup traceroute at regular intervals using the batch file below

@ECHO OFF

set SERVER=DBServerName.domainname.com

set INTERVAL=60

:TRACEINTERVAL

tracert %SERVER% >> C:\DBServerNametracert.txt

timeout %INTERVAL%

GOTO TRACEINTERVAL

In my case, no issues were found, was able to connect to DBServerName continously. This would indicate there are no network connectivity issues.

Possible Root Cause: Issues connecting to Active Directory Server

Steps Taken To Confirm Root Cause: Tried connecting via a SQL id using SSMS & SQLCMD encountered the same issue.

Possible Root Cause: DNS Problems

Steps Taken To Confirm Root Cause: Tried connecting via IP, still encountered the same issue

Possible Root Cause: Issues connectiving via IPv4 or IPv6 address

Steps Taken To Confirm Root Cause:

Checked TCP/IP properties in configuration manager on database server; server was listening to all IP’s.

Tried connecting manually using the IPv4 addresses as well as IPv6 addresses; Encountered the same error in both, so issue was not related to this cause.

C:\Users\sameer\Desktop>sqlcmd -S DBServerName.domainname.com -E

Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : TCP Provider: Timeout error [258].

Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login timeout expired.

Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Unable to complete login process due to delay in prelogin response.

C:\Users\sameer\Desktop>sqlcmd -S fe40::d0f8:6600:8b2:625b%14 -E

Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Named Pipes Provider: Could not open a connection to SQL Server [53]. .

Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login timeout expired.

Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.

Possible Root Cause: TCP/IP Port Exhaustion

Steps Taken To Confirm Root Cause:n netstat -ao on the database server while connecting to it from a client server. Consistently observed a TCP connection was being established between both servers, even when the SQL error message was occuring. This means the TCP connection is fine, and there is no TCP/IP port exhaustion.

 Possible Root Cause: Resource Constraints on DBServerName server can cause login denials

Steps Taken To Confirm Root Cause:Checked RAM, CPU usage on the database server; CPU was under 10% and RAM was around 60%

Possible Root Cause: AntiVirus software running on database server

Steps Taken To Confirm Root Cause:

Trend Micro was running on the database server. Removing this fixed the issue