MS SQL Server Error – SuperSocket Info: Bind failed on TCP port 1433

MS SQL Server Error – SuperSocket Info: Bind failed on TCP port 1433

Last Friday night, I had a bear of a time with MS SQL Server not listening on its assigned TCP Port.

In the last few weeks I had being communicating effortlessly with a MS SQL Server Instance.  That changed, as these things do, one Friday afternoon.  A co-worker IMed and asked for the name of one of our MS Sharepoint database server.  I gave him the name and he replied a bit later that he could not connect.  We passed it off as a Network firewall issue.

I went back to what I was doing.  But, then a bit later I tried a Data Collection job that relies on a Linked Server Connection to that same MS Sharepoint server and the job started to fail, as well.

Tried everything but no joy.

I then did a remote desktop connection to the server and reviewed the SQL Instance Network bindings.  It was using the Traditional TCP 1433 port.  Launched a command line applet (cmd) and did a “netstat -anb” — No luck.  Narrowed the search a bit with “netstat -anb  | find “1433”.  Still no MS SQL Server Process listening on 1433.

As the server is a MS SQL Server 2000, used SQL Server Network App to bind to a different port 2516 — which I believed is not one of the well known ports.  Tried another innocuous port 3012 – no luck.

Fumbled with the servers for the next couple of hours —

a] Tried starting MS SQL Server from the command line (sqlserv -c -m)
b] Switched the Active MS SQL Server Instances back and forth
c] Disabled Cluster Checkpoint using http://support.microsoft.com/kb/953504

Made plans to work the weekend:

a] Plans included MS SQL Server re-install
b] Upgrade to MS SQL Server v2005

But, as the Heavens will have it, finally “lucked” upon finding this web gem:

SQL Server Forums – SQL 2000 SP4 on Win2K3 Cluster
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97645

rmiao wrote “Also check virtual sql ip address in registry -> hklm\software\microsoft\microsoft sql server\cluster, ensure it’s 10.1.172.7 as well”

I checked the registry entries on both cluster nodes and found it to be consistently wrong and pointing to an IP Address that has been re-assigned to a different host — Basically by doing “ping -a x.x.x.x”

I corrected the entry and stamped it with the actual SQL IP Address noted in Cluster Administrator.  I heard that call “go forward young man”, or shall I say “go forward old man” –  Using Cluster Administrator, failed the SQL Cluster resource, brought it back online, and watched Event Viewer and we were golden — No more stupid errors about “Bind failed on TCP port 1433”.

Thank goodness can finally go home — Who really wants to work on Superbowl weekend.

1] SQL Server Forums – SQL 2000 SP4 on Win2K3 Cluster
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97645

2] Error message when you change the IP address on a SQL Server failover cluster
node: “Bind failed”
http://support.microsoft.com/kb/319578

3] SQL Server cannot bind to the TCP/IP port on which SQL Server is
listening when the TCP\IP port is used by another application
http://support.microsoft.com/kb/307197

4] BUG: Hide Server Option Cannot Be Used on Multiple Instances of
SQL Server 2000
http://support.microsoft.com/kb/308091/EN-US/

5] HOW TO CHANGE THE DYNAMIC PORT OF THE SQL SERVER NAMED INSTANCE TO THE STATIC PORT IN A SQL SERVER 2005 CLUSTERED INSTANCE
http://www.sqlserverfaq.net/2008/05/17/how-to-change-the-dynamic-port-of-the-sql-server-named-instance-to-the-static-port-in-a-sql-server-2005-clustered-instance/

6] FIX: SQL Server fails to bind TCP/IP port at startup
http://support.microsoft.com/kb/312935

 

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