Thursday, June 24, 2004

What UDP port does SQL Server listen on?

SQL Server's default TCP/IP port is 1433 and uses the UDP port of 1434. You cannot change the UDP port from 1434 to another, which is what helped the SQL Slammer virus spread so quickly. The UDP port is constantly listening on port 1434 and when ping responds with the TCP/IP port SQL Server listens on.

Default instances of SQL Server use TCP port 1433 by default. 1433 is the official Internet Assigned Number Authority (IANA) socket number for SQL Server. Named instances, however, dynamically assign an unused TCP port number the first time the instance is started. The named instance can also dynamically change its TCP port address on a subsequent startup if the original TCP port number is being used by another application. SQL Server only dynamically changes to an unused TCP port if the port it is currently listening on was dynamically selected. That is, if the port was manually selected, SQL Server will display an error and continue to listen on other ports.
The error messages are held in:
%systemroot%\program files\microsoft sql server\mssql\log
Any port errors are recorded in this file, which can be opened with notepad.
However, It is important to point out that dynamic port detection is ONLY available for named instances of SQL. The network libraries assume either 1433 or the global default port established with the Client Configuration Utility. If a default instance is listening on a port other than the standard 1433 port, you must provide an alias or alter the global default port. To change or allocate ports manually, you can either run server network utility, or edit the following Registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQLServer\instance_name\MSSQLServer\SuperSocketNetLib\Tcp
BUT you need to make sure that the now port is not in use by any other process. (Type
netstat -a -n
at the command prompt to get a list of the port numbers in use.)
The implications of changing the default ports are that the clients may not be able to see the server. SQL clients use DBNETLIB to detect the ports. DBNETLIB is always loaded by the ODBC or SQLOLEDB components. DBNETLIB is responsible for making either direct IP/SPX calls or forwarding requests directly to the Shared Memory, Named Pipes or other network libraries. The Client Configuration Utility has been extended in SQL 2000 to provide an option for dynamic port detection. When you enable the Client Configuration Utility, no port number is stored for the alias entry and DBNETLIB attempts to contact the server through a known UDP port to obtain the proper connection information.