How to manually enable TCP/IP on Windows XP Service Pack 2 for SQL Server 2000

Article translations Article translations
Article ID: 841252 - View products that this article applies to.
Expand all | Collapse all

INTRODUCTION

This article describes how to determine if SQL Server is using a static or a dynamic port, and how to manually enable TCP/IP on Microsoft Windows XP Service Pack 2 for Microsoft SQL Server 2000.

By default, Windows Firewall is enabled on computers that are running Windows XP Service Pack 2. Windows Firewall closes ports such as 445 that are used for file and printer sharing to prevent Internet computers from connecting to file and print shares on your computer or to other resources. When SQL Server is configured to listen for incoming client connections by using named pipes over a NetBIOS session, SQL Server communicates over TCP ports and these ports must be open. SQL Server clients that are trying to connect to SQL Server will be not be able to connect until SQL Server is set as an exception in Windows Firewall. To configure Windows Firewall in Windows XP Service Pack 2 to allow SQL Server 2000 to listen for TCP/IP traffic on a static port, use the steps that are listed in the "More Information" section.

MORE INFORMATION

Warning Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall your operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk.

How to determine if you are using a static or a dynamic port

If you are not sure if you are using a dynamic port, follow these steps:
  1. Click Start, and then click Run.
  2. In the Run dialog box, type regedit, and then click OK. This will start Registry Editor.
  3. Locate the
    HKLM/Software/Microsoft/MSSQLServer/MSSQLServer/SuperSocketNetLib/Tcp/TcpDynamicPorts
    registry key. Use this key to determine if dynamic ports are enabled. If it lists a numeric value, that value is the last dynamic port value that was used by SQL Server. If it is blank, you are using a static TCP port.
  4. Quit Registry Editor.

How to enable TCP/IP with dynamic ports

If you are using dynamic ports, you must create an exception for the SQL Server program in Windows Firewall.

For more information about how to create an exception for the SQL Server program in Windows Firewall, click the following article number to view the article in the Microsoft Knowledge Base:
841251 How to enable SQL Server connectivity on Windows XP Service Pack 2

How to enable TCP/IP with a static port

To enable TCP/IP connectivity for SQL Server listening on a static port, you must first know the number of the port that SQL Server is using. To find the port, you can either use the Server Network Utility or use the Regkey method.
Use the Server Network Utility to find the port that SQL Server is using
Note This method works for either a default instance or for a named instance.
  1. If you are using the Server Network Utility, click Start, point to All Programs, point to Microsoft SQL Server, and then click Server Network Utility. If you are using MSDE or the command line, click Start, and then click Run. In the Run dialog box, type svrnetcn.exe, and then click OK.
  2. In the Server Network Utility dialog box, you will see a list of the disabled protocols and a list of the enabled protocols on the General tab.
  3. In the Instances on this server list box, select the instance that you want to examine.
  4. Click to select the protocol that you want to find the port number for, and then click the Properties button.
  5. Make a note of the port number.
Use the Regkey method with a default instance to find the port that SQL Server is using
Warning Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall your operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk.

To determine what port SQL Server is using for a default instance by using the Regkey method, follow these steps:
  1. Click Start, and then click Run.
  2. In the Run dialog box, type regedit, and then click OK. This will start Registry Editor.
  3. Locate the
    HKLM/Software/Microsoft/MSSQLServer/MSSQLServer/SuperSocketNetLib/Tcp/TcpPort
    registry key. This key will house the actual TCP port that SQL Server is configured to use.

    Note If you are using dynamic ports, this will be the port that SQL Server is currently using.
  4. Make a note of this port number.
  5. Quit Registry Editor.
Use the Regkey method with a named instance to find the port that SQL Server is using
Warning Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall your operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk.

To determine what port SQL Server is using for a named instance, follow these steps:
  1. Click Start, and then click Run.
  2. In the Run dialog box, type regedit, and then click OK. This will start Registry Editor.
  3. Locate the
    HKLM/Software/Microsoft/Microsoft SQL Server/mssql$<instancename>/MSSQLServer/SuperSocketNetLib/Tcp/
    registry key.
    This key will house the actual TCP port that SQL Server is configured to use.
  4. Make a note of this port number.
  5. Quit Registry Editor.

How to configure Windows Firewall for TCP

When you know what port SQL Server is using, you can follow these steps to configure Windows Firewall to enable SQL Server to listen on that port.
  1. Click Start, and then click Run.
  2. In the Run dialog box, type Firewall.cpl, and then click OK.
  3. In the Windows Firewall dialog box, click Add Port on the Exceptions tab.
  4. In the Add a Port dialog box, type your port number in the Port number box, and then click the TCP button.
  5. Type a name for the port in the Name box such as SQL, and then click OK.
  6. On the Exceptions tab, you will see the new service. To enable the port, click to select the check box next to your new service, and then click OK.


If you decide to disable the port, you can clear the check box next to your new service.

How to configure Windows Firewall for User Datagram Protocol (UDP)

SQL Server uses UDP Port 1434 for communication with applications such as Microsoft Visual Basic. If you are running an application that requires connectivity with SQL Server, you must enable port 1434 for SQL Server to listen on. To do this, follow these steps:
  1. Click Start, and then click Run.
  2. In the Run dialog box, type Firewall.cpl, and then click OK.
  3. In the Windows Firewall dialog box, click Add Port on the Exceptions tab.
  4. In the Port number box, type 1434, and then click the UDP button.
  5. Type a name in the Name box for the port such as SQL Server UDP, and then click OK.
  6. On the Exceptions tab, you will see the new service. To enable the port, click to select the check box next to your new service, and then click OK.

REFERENCES

You can also enable connectivity for SQL Server on Windows XP Service Pack 2 by setting up an exception in Windows Firewall for SQL Server.

For more information about setting up an exception in Windows Firewall for SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:
841251 How to enable SQL Server connectivity on Windows XP Service Pack 2
You can also use a script to enable connectivity for SQL Server on Windows XP Service Pack 2.

For more information, click the following article number to view the article in the Microsoft Knowledge Base:
839980 How to use a script to programmatically open ports for SQL Server to use on systems that are running Windows XP Service Pack 2
For more information about dynamic and static port allocation, in addition to steps to configure static or dynamic ports on SQL Server 2000, click the following article number to view the article in the Microsoft Knowledge Base:
823938 How to configure an instance of SQL Server to listen on a specific TCP port or a dynamic port
For more information about configuring SQL Server 2000 and Windows XP Service Pack 2, click the following article number to view the article in the Microsoft Knowledge Base:
841249 How to configure Windows XP Service Pack 2 for use with SQL Server

Properties

Article ID: 841252 - Last Review: April 18, 2006 - Revision: 2.6
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
  • the operating system: Microsoft Windows XP SP2
Keywords: 
kbhowto kbinfo KB841252

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com