Microsoft KB Archive/823938

From BetaArchive Wiki

Article ID: 823938

Article Last Modified on 10/16/2007


APPLIES TO

  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup Edition


IN THIS TASK

SUMMARY

This article describes static and dynamic port allocation in Microsoft SQL Server 2000 or in Microsoft SQL Server 2005. It also discusses how to configure an instance of SQL Server to use either a static port or a dynamic port.

back to the top

Static port allocation

If you configure an instance of SQL Server to use a static port, and you restart the instance of SQL Server, the instance of SQL Server listens only on the specified static port. The SQL Server clients must send all the requests only to the static port where the instance of SQL Server is listening.

However, if an instance of SQL Server is configured to listen on a static port, and another program that is running on the computer is already using the specified static port when SQL Server is started, SQL Server does not listen on the specified static port.

By default, the default instance of SQL Server listens for requests from SQL Server clients on static port 1433. Therefore, the client network libraries assume that either port 1433 or the global default port that is defined for that client computer is used to connect to the default instance of SQL Server.

If a default instance of SQL Server is listening on a port other than port 1433, you must either define a server alias name or change the global default port by using the Client Network Utility. However, you can also make the default instance of SQL Server listen on multiple static ports.

For additional information about how to set up SQL Server 2000 to listen on multiple static TCP ports, click the following article number to view the article in the Microsoft Knowledge Base:

294453 How to set up SQL Server 2000 to listen on multiple static TCP ports


The default instance of SQL Server does not support dynamic port allocation. However, the named instances of SQL Server support allocation of both static and dynamic ports. By default, a named instance of SQL Server listens on a dynamic port.

back to the top

Dynamic port allocation

Only named instances of SQL Server can use the dynamic port allocation process. In the dynamic port allocation process, when you start the instance of SQL Server for the first time, the port is set to zero (0). Therefore, SQL Server requests a free port number from the operating system. As soon as a port number is allocated to SQL Server, SQL Server starts listening on the allocated port.

The allocated port number is written to the Windows registry. Every time that you start that named instance of SQL Server, it uses that allocated port number. However, if another program that is already running on the computer is using that allocated port number when you start SQL Server, SQL Server chooses another port.

When you start the named instances of SQL Server the second time, SQL Server opens the listening port number that was started the first time, as follows:

  • If the port is opened without errors, SQL Server listens on the port.
  • If the port is not opened, and errors occur, SQL Server behaves as follows:
    • You receive the following error message:

      Error ID 10048 (WSAEADDRINUSE)

      When you receive this error message, SQL Server determines that the port is being used. Then, the port number is set to zero (0) again. Therefore, an available port is assigned. And, SQL Server waits for the client connection request on the port.
    • If you receive an error message that does not mention error 10048, SQL Server 2000 determines that it is impossible to wait for the connection request on the port. Therefore, the port is not opened.

Notes

  • In SQL Server 2005, when you receive the following error message, the port number is set to zero (0) and is opened.

    Error ID 10013 (WSAEACCES)

  • In Windows Server 2003 or in Windows XP, you may receive the 10013 error message instead of the 10048 error message when the port that is trying to open is used exclusively.

When an instance of SQL Server uses dynamic port allocation, the connection string that is built at the SQL Server client does not specify the destination TCP/IP port unless the user or the programmer explicitly specifies the port. Therefore, the SQL Server client library queries the server computer on UDP port 1434 to collect the information about the destination instance of SQL Server. When SQL Server returns the information, the SQL Server client library sends the data to the appropriate instance of SQL Server.

If UDP port 1434 is disabled, the SQL Server client cannot dynamically determine the port of the named instance of SQL Server. Therefore, the SQL Server client may not be able to connect to the named instance of SQL Server. In this situation, the SQL Server client must specify the dynamically allocated port where the named instance of SQL Server 2000 is listening.

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

265808 How to connect to a named instance of SQL Server 2005 or SQL Server 2000 by using the client tools in the earlier version of SQL Server


back to the top

Verifying the port configuration of an instance of SQL Server

Note The default instances of SQL Server 2000 always use a static port.

To find the TCP/IP port where your instance of SQL Server is listening, follow these steps:

  1. Start SQL Query Analyzer, and then connect to the instance of SQL Server.

  2. Run the following Transact-SQL statement in SQL Query Analyzer:

    Use master
Go
Xp_readerrorlog
  3. In the Results pane, locate the following text (where X.X.X.X is the IP address of the instance of SQL Server and Y is the TCP/IP port where SQL Server is listening):

    SQL server listening on X.X.X.X: Y

    Note: For example, if you locate the "SQL server listening on 10.150.158.246: 1433" text in the Results pane, 10.150.158.246 is the IP address of the SQL Server and 1433 is the TCP/IP port where the instance of SQL Server is listening.

To verify the port configuration of an instance of SQL Server, follow these steps:

  1. Start Registry Editor.
  2. In Registry Editor, locate the following registry key:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName>\MSSQLServer\SuperSocketNetLib\Tcp

    Note If you are using SQL Server 2005, locate the following registry subkey:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<MSSQL.x>\MSSQLServer\SuperSocketNetLib\Tcp\IPAll

    Notice the TCPDynamicPorts value and the TCPPort value. These values appear as follows, depending on your port allocation method:

    • Static Port Allocation
      If you configure an instance of SQL Server to use a static port, and you have not yet restarted the instance of SQL Server, the registry values are set as follows:

      TCPDynamicPorts = Last port used
      TCPPort = New static port to be used after the next restart; new static port that you set by using the Server Network Utility

      However, if you configure an instance of SQL Server to use a static port, and you restart the instance of SQL Server, the registry values are set as follows:

      TCPDynamicPorts = Blank
      TCPPort = New static port that you set by using the Server Network Utility

    • Dynamic Port Allocation
      If you configure an instance of SQL Server to use dynamic port allocation, and you have not yet restarted the instance of SQL Server, the registry values are set as follows:

      TCPDynamicPorts = Blank
      TCPPort = 0

      However, if you configure an instance of SQL Server to use dynamic port allocation, and you restart the instance of SQL Server, the registry values are set as follows:

      TCPDynamicPorts = Current port used
      TCPPort = Current port used

back to the top

Configuring an instance of SQL Server to use a static port

SQL Server 2005

To configure an instance of SQL Server 2005 to use a static port, follow the steps described in the "How to: Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager)" topic in SQL Server 2005 Books Online.

SQL Server 2000

To configure an instance of SQL Server to use a static port, follow these steps:

  1. Start the Server Network Utility. To do this, do either one of the following:
    • Click Start, point to Programs, point to Microsoft SQL Server, and then click Server Network Utility.
    • Click Start, and then click Run. In the Open box, type svrnetcn.exe, and then click OK.
    The SQL Server Network Utility dialog box appears.
  2. In the SQL Server Network Utility dialog box, click the General tab.
  3. In the Instance(s) on this server list, select your instance of SQL Server.

    Note If TCP/IP protocol is disabled, enable it now. To do this, click TCP/IP in the Disabled Protocols list box, and then click Enable.
  4. In the Enabled Protocols list box, click TCP/IP, and then click Properties.
  5. In the Default port box, type a static port number, and then click OK.

    Note The static port that you specify must not be the same as the dynamic port that your instance of SQL Server is currently listening on. For example, if an instance of SQL Server is currently listening on dynamic TCP/IP port 1400, type 1500 for the new static port.
  6. Click OK, and then click OK again.
  7. Restart the instance of SQL Server.
  8. View the SQL Server error logs to verify whether the instance of SQL Server is currently using the static port.

    Note If you have a clustered instance of SQL Server, and you follow the specified steps on a cluster node, you may notice that the TCPDynamicPorts registry values and the TCPPort registry values on other cluster nodes still hold the old values. When you move the SQL Server group to the corresponding cluster node, and then bring SQL Server online on the cluster node, the registry values on the cluster nodes will reflect the correct values.

You may want to set the static port of your instance of SQL Server to the same port number as the dynamic port that it used earlier. To do this, follow these steps:

  1. View the TCPDynamicPorts registry value and the TCPPort registry value to determine the dynamic port number that the earlier instance of SQL Server used.
  2. In the Server Network Utility, set the static port to a different port number than the registry value that you determined in step 1.
  3. Restart the instance of SQL Server.
  4. In the Server Network Utility, set the static port to the registry value that you determined in step 1.
  5. Restart the instance of SQL Server.

back to the top

Configuring an instance of SQL Server to use a dynamic port

SQL Server 2005

To configure your instance of SQL Server 2005 to use a dynamic port, use the similar method described in the "How to: Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager)" topic in SQL Server 2005 Books Online. For more information, see the "Server Network Configuration" topic in SQL Server 2005 Books Online.

SQL Server 2000

To configure your instance of SQL Server to use a dynamic port, follow these steps:

  1. Start the Server Network Utility. To do this, do either one of the following:
    • Click Start, point to Programs, point to Microsoft SQL Server, and then click Server Network Utility.
    • Click Start, and then click Run. In the Open box, type svrnetcn.exe, and then click OK.
    The SQL Server Network Utility dialog box appears.
  2. In the SQL Server Network Utility dialog box, click the General tab.
  3. In the Instance(s) on this server list, select your instance of SQL Server.

    Note If TCP/IP protocol is disabled, enable it now. To do this, click TCP/IP in the Disabled Protocols list box, and then click Enable.
  4. In the Enabled Protocols list box, click TCP/IP, and then click Properties.
  5. In the Default port box, type 0, and then click OK.
  6. Click OK, and then click OK again.
  7. Restart the instance of SQL Server.
  8. View the SQL Server error logs to verify whether the instance of SQL Server is currently using the dynamic port.

    Note If you have a clustered instance of SQL Server, and you follow the specified steps on a cluster node, you may notice that the TCPDynamicPorts registry values and the TCPPort registry values on other cluster nodes still hold the old values. When you move the SQL Server group to the corresponding cluster node, and then bring SQL Server online on the cluster node, the registry values on the cluster nodes will reflect the correct values.

back to the top

Troubleshooting

If the SQL Server clients cannot access an instance of SQL Server after you have configured it to use a static TCP/IP port, the following causes may exist:

  • A firewall may be blocking the specified TCP/IP port.
    If the instance of SQL Server is listening on a static port, and a firewall is blocking the port, the SQL Server client may not be able to connect to the instance of SQL Server through the firewall. In this case, you must either release the port from the firewall or allocate a new static port to the instance of SQL Server.

    If the instance of SQL Server is listening on a dynamic port, and a firewall is blocking UDP port 1434, the client connections for a named instance of SQL Server must specify the currently allocated port. If the instance of SQL Server is restarted, a new dynamic port may be allocated. Therefore, the SQL Server client configuration must be changed to use the newly allocated port to connect to the same instance of SQL Server again.

    However, if the firewall releases UDP port 1434, the SQL Server clients automatically retrieve the information about the instance of SQL Server. Therefore, the SQL Server client configuration must be changed every time that the instance of SQL Server is restarted.

    For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

    287932 TCP ports needed for communication to SQL Server through a firewall


    318432 BUG: Cannot connect to a clustered named instance through a firewall

  • Another program may already be using the specified TCP/IP port.
    If another program is already using the specified TCP/IP port, the port is not available to the instance of SQL Server and SQL Server clients may not be able to connect to the instance of SQL Server.

    This problem is specific to an instance of SQL Server that is configured to use a static TCP/IP port. This problem does not occur for an instance of SQL Server that is configured to use dynamic port allocation. In dynamic port allocation, if another program is already using the specified TCP/IP port when you start the instance of SQL Server, the instance of SQL Server selects a new port.

    For more information, click the following article number to view the article in the Microsoft Knowledge Base:

    293107 Clients cannot communicate to SQL Server through port 1433 or the port SQL Server is listening on

back to the top

REFERENCES

For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

286303 Behavior of SQL Server 2000 network library during dynamic port detection


273673 Description of SQL Virtual Server client connections


328383 SQL Server clients may change protocols when the client computers try to connect to an instance of SQL Server


back to the top

Keywords: kbsql2005cluster kbconnectivity kbupdate kbsqlclient kbnetwork kbfirewall kbconfig kbregistry kbhowtomaster KB823938



Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.


Retrieved from ‘https://www.betaarchive.com/wiki/index.php?title=Microsoft_KB_Archive/823938&oldid=333886’
the Creative Commons 3.0 ShareAlike (except the Microsoft KB Archive).
Powered by MediaWiki