How to manually enable TCP/IP for SQL Server 7.0 on a computer that is running Windows XP Service Pack 2 (841823)


The information in this article applies to:

  • Microsoft SQL Server 7.0
  • Microsoft Windows XP Professional SP2
Important This article contains information about how to modify the registry. Make sure to back up the registry before you modify it. Make sure that you know how to restore the registry if a problem occurs. For more information about how to back up, restore, and modify the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows registry

INTRODUCTION

Microsoft Windows XP Service Pack 2 (SP2) includes Windows Firewall. Windows Firewall is an enhanced version of Internet Connection Firewall (ICF). Windows Firewall is a host-based, stateful, filtering firewall that discards unsolicited incoming traffic through TCP/IP version 4 (IPv4) connections, and through TCP/IP version 6 (IPv6) connections. By default, Windows Firewall is enabled on computers that are running Windows XP SP2.

Because Windows Firewall is enabled, Microsoft SQL Server cannot listen to the network, even if it was previously configured to do this.

This article describes how to manually enable TCP/IP on computers that are running Microsoft Windows XP Service Pack 2 (SP2) for Microsoft SQL Server 7.0, and how to configure Windows Firewall in Windows XP SP2 to enable SQL Server 7.0 to listen for TCP/IP traffic on a static port.

MORE INFORMATION

How to enable TCP/IP with a static port

To enable TCP/IP connectivity for SQL Server that is listening on a static port, you must first know the number of the port that SQL Server is using. To find the port, either use the Server Network Utility or use the Regkey method.

How to use the Server Network Utility to find the port that SQL Server is using

Note To determine what port SQL Server is using, follow these steps on the computer that has the SQL Server 7.0 service installed. This method works for a default instance of SQL Server or for a named instance of SQL Server.

To determine what port SQL Server is using, follow these steps:
  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, click Run, type Svrnetcn.exe, and then click OK.
  2. In the SQL Network Utility, click the General tab. You will see a list of disabled protocols and a list of enabled protocols.
  3. In the Instance{s} on this server list box, select the instance that you want to examine.
  4. Select the protocol that you want to find the port number for, and then click Properties.
  5. Make a note of the port number.

How to 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, follow these steps:

  1. Click Start, click Run, type regedit, and then click OK. This will start Registry Editor.
  2. Locate the HKLM/Software/Microsoft/MSSQLServer/MSSQLServer/SuperSocketNetLib/Tcp/TcpPort registry key. This key contains the actual TCP port that SQL Server is configured to use.
  3. Make a note of this port number.
  4. Quit Registry Editor.

How to 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, click Run, type regedit, and then click OK. This will start Registry Editor.
  2. Locate the HKLM/Software/Microsoft/Microsoft SQL Server/mssql$<Instance_name>/MSSQLServer/SuperSocketNetLib/Tcp/ registry key. This key contains the actual TCP port that SQL Server is configured to use.
  3. Make a note of this port number.
  4. 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 and to enable SQL Server to listen on that port.
  1. Click Start, click Run, type Firewall.cpl, and then click OK.
  2. In the Windows Firewall dialog box, click Add Port on the Exceptions tab.
  3. In the Port Number dialog box, type Your port number, and then click TCP.
  4. Type a name, such as SQL Server, for the port in the Name box, and then click OK.
  5. 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 to communicate with applications such as Microsoft Visual Basic. If you are running an application that requires connectivity with SQL Server, you will have to enable UDP port 1434 for SQL Server to listen on. To do this, follow these steps:
  1. Click Start, click Run, type Firewall.cpl, and then click OK.
  2. In the Windows Firewall dialog box, click Add Port on the Exceptions tab.
  3. In the Port Number dialog box, type 1434, and then click UDP.
  4. Type a name, such as SQL Server UDP, for the port in the Name box, and then click OK.
  5. 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.
  6. Click OK.

REFERENCES

You can also enable connectivity for SQL Server on a computer that is running Windows XP Service Pack 2 by setting up an exception in Windows Firewall for SQL Server. For more information about how to enable connectivity 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 enable connectivity for SQL Server by using a script. For additional information about using a script to enable connectivity for SQL Server, 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, and for steps about how to configure dynamic or static ports on SQL Server, 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 additional information about how to configure SQL Server 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

Modification Type:MinorLast Reviewed:8/31/2006
Keywords:kbinfo kbhowto KB841823 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.