SQL Server – Network Connectivity – Error – Connection Failed – SQLState – 08001 // SQL Server Error – 17

 

Ticket

Received a very descriptive ticket this morning.  It read

Short Description: 2 computers are not able to access the SQL Server

Ticket Summary: There are 2 computers that are trying to access the SQL Server – OGDBvm
IP addresses of computers are: 10.0.4.24, 10.0.4.23.

When trying to connect to the SQL server, they receive the error – attached.

If a SQL user “maple,” uses the odbc connection from computer 10.0.4.22 – it can connect successfully.

If the SQL user “maple,” tries to connect from the computers 10.0.4.24 and 10.0.4.23 – they receive and error – see attached.

We have had the network team verify that there is nothing blocking the connection. Can you check the server to see if connections are allowed from these systems.

This issue originally started happening on 1/27/2017 – ticket. INC0891787.

This is a critical application for the department and currently they can only access it from one computer.

TroubleShooting

Client Computer

ODBC & Netstat

On client computer, accessed ODBC Control Panel applet and tried connecting to Data Source.

On another window on same machine, launched DOS Command Shell and issued netstat -an | find “SYN_SENT”.

Immediately confirmed that we are seeing noticed SYN_SENT on port 1433 targeting DB Server.

 

 

DB Server

Query Analyzer ( ISQLW)

It is a SQL Server 2000 Server and so issued isqlw and even had problems connecting locally.

Image

tcpipsockets_20170217_1214pm

Textual

Server: Msg 17, Level 16, State 1, Line 0

[Microsoft] [ODBC SQL Server Driver] [TCP/IP Sockets] SQL Server does not exist or access denied.

Server: Msg 10061, Level 16, State 1, Line 0

[Microsoft] [ODBC SQL Server Driver] [TCP/IP Sockets] ConnectionOpen (Connect()).

 

Task Manager

Using Task manager noticed High Memory Consumption by SQL Server Process.

Stop DB Server hoping it is just a runway DB Engine, but no not that easy.

 

SQL Server – Error Log

Reviewed SQL Server Error Log

Image

sqlserverlogs__20170112_2118

Image – Listening On

sqlserverlogs__listeningon_20170112_2118

Textual

SQL server listening on Shared Memory, Named Pipes.

 

Explanation

Confirmed that we listening on Shared Memory and Named Pipes.

But, I do really need to be listening on TCP/IP.

 

SQL Server Network Configuration

Launch SQL Server Network Configuration and confirmed that TCP is enabled and it is listening on SQL Server Default TCP Port of 1433.

TCP/IP – Port – 1433

tcpip-port-1433-cropped-up

TCP/IP – Port – 50000

Temporarily changed to another port, 50000, that I know is not being used

tcpip-port-50000-cropped-up

 

SQL Server Restarted

Restarted SQL Server so that the new TCP/IP can be effectual, but still not listening on TCP.

Confirmed by issuing “netstat -anb“.  Noticed our SQL Server Port is not one of the Listening Ports.

Code

netstat -anb

 

Networking

When bac to Control Panel and accessed Networking and TCP/IP Protocol.

 

tcpipfiltering_20170217_1248pm

Ensured that TCP/IP Filtering is not enabled.

Resolution

Took to the net to determine what can cause SQL Server not to listen on its assigned TCP-Port.

Now thinking back to yester years, or is Wonder years, before Blaster Worm.

And, all the havoc that occurred that Friday night.

Validate Current Version

Connect to box insisting on np and ran “select @@version“.

Image

version_20170217_1210pm-croppedup

Textual

Microsoft SQL Server 2000 – 8.00.194 ( Intel X86)

SQL Server 2000 Version Matrix

Here is the Version Matrix for SQL Server 2000

versioning_v2000

Explanation

Mapping Our Version # ( 8.00.194 ) to the matrix confirms that we are running SQL Server 200 Release To Manufacturing ( RTM).

 

Download SQL Server 2000 SP4

The fix of course is to download Microsoft SQL Server 2000 SP4.

Applied Patched

Applied patched.

Restarted Box

Restarted box.

 

Validation

SQL Server Error Log

Launched SQL Server Enterprise Manager and reviewed error log

sqlserverlogs__20170117_0307pm

SQL Server Error Log – Listening On

Image

listeningon

Textual

SQL server listening on TCP, Shared Memory, Named Pipes.

 

Dedication

Dedicated to the Network Firewall Team

Our Network Firewall guy says that even he were to run monitoring all day, the problem is still occurring at the Application Layer.

He continued that he is seeing traffic originate from the client workstation, but the Server is rejecting the request right away.

And, that the server is not even listening in on the port we are targeting.

The moral of the story is that when a Network Engineer is offering you a bet, don’t take it.  Go do your homework.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s