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
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
2] Error message when you change the IP address on a SQL Server failover cluster
node: “Bind failed”
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
4] BUG: Hide Server Option Cannot Be Used on Multiple Instances of
SQL Server 2000
5] 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