Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2003
    Posts
    54

    Unanswered: 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?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Look for this line in SQL Server errorlog:

    SQL server listening on <IP_Address>: <Port_Number>.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Sep 2003
    Posts
    54
    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.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Do any of the errorlogs (SQL, NT, or other) show anything interesting?

    -PatP

  6. #6
    Join Date
    Sep 2003
    Posts
    54
    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.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Try this.

    -PatP

  8. #8
    Join Date
    Sep 2003
    Posts
    54
    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.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  10. #10
    Join Date
    Sep 2003
    Posts
    54

    Smile

    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.

  11. #11
    Join Date
    Sep 2003
    Posts
    54
    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.

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ahhh! A proactive Slammer-stopper. I hadn't thought of that!

    -PatP

  13. #13
    Join Date
    Sep 2003
    Posts
    54
    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.

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  15. #15
    Join Date
    Sep 2003
    Posts
    54
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •