How can I tell whether SQL Server is able to listen to port 1433?

I want to know how to check whether SQL Server 2000 can listen to port 1433 or not. If I run netstat -a, am I suppose to see port 1433 regardless of what service pack I have applied to SQL Server 2000?Things get a bit complicated at this level.

If you run netstat without the -n parameter, you'll probably never see port 1433 because it has a named equivalent.

If you run a named instance of SQL Server, you'll find a redirector listening on port 1434 and SQL Server listening on an arbitrary free port (which the redirector will point incoming connections to when they arrive).

If you run an unnamed instance of SQL Server, you should find it listening on port 1433 as you expected.

-PatP|||Look for this line in SQL Server errorlog:

SQL server listening on <IP_Address>: <Port_Number>.|||Hi Pat,

I believe you mean I should see ms-sql-s but I don't see that one too. I'm running netstat -a on a machine with only the default instance of SQL Server 2000 which I can see port 1433 in the Server Network Utility.

I have a feeling that the issue may be related to XP SP2 RC2. I have tried to add port 1433 and 1434 to the Windows Firewall. I have also tried to add the program sqlservr to the Windows Firewall. I have also turned off Windows Firewall. But when I run netstat -a on all these combinations, I don't see port 1433 or ms-sql-s.|||Do any of the errorlogs (SQL, NT, or other) show anything interesting?

-PatP|||Hi Pat,

The interesting thing that I see is that SQL Server is now only listening to Shared Memory and Named Pipes in SQL Server Logs. But I have checked the SQL Server Logs before XP SP2 RC2 is applied, the SQL Server was listening to TCP, Shared Memory, and Named Pipes. I have checked that the current setting in Server Network Utility and in registry are both showing using TCP and port 1433. There is only the default instance in that machine. Since SQL Server is no longer listening to TCP, this explains why I don't see port 1433. However, the problem is why SQL Server is no longer listening to TCP.|||Try this (http://sqljunkies.com/Newsgroups/microsoft.public.sqlserver.connect/2004/4/27/188677.aspx).

-PatP|||Hi Pat,

I have just tried by switching the port to 0 (stop and start) and then switch back to 1433. (stop and start). The SQL Server is still listening to Shared Memory and Named Pipes.|||Boy, then that beats me. I'll try to bounce this off of some of the guys with real GQ (Geek Quotient) in the morning, but I may not get any response for about 24 hours. You've piqued my curiousity with this one!

-PatP|||If this is a problem of XP SP2 RC2, someone should have spotted this and I cannot imagine MS will release RC2 with this kind of issue. But if it is not, it is a mystery so far.

I'm going to listen to the XP SP2 and SQL Server webcast. May be it will tell me something.|||I believe I found out what happen. After I install XP SP2 RC2 on SQL Server 2000 Personal Edition with SP2, TCP/IP and UDP ports are automatically disabled according to Event Viewer log. I will install SP3a to see whether it will resolve this issue.|||Ahhh! A proactive Slammer-stopper. I hadn't thought of that!

-PatP|||I have applied SP3a and now TCP is open. I believe MS should do a better job in logging this information in Event Viewer. It is logged as INFORMATION entry so it will be easily be ignored.

Thank you for your help.|||Yeah, I'd fire that off to your ADC or TAM, or at the very least send it to mswish. The event message should be more informative.

-PatP|||I just read the FAQ: How Windows XP SP2 Affects SQL Server and MSDE. It has the information about this issue but it says it will create an error entry in the Windows Error log. So, MS just needs to change information to error.