Technical FAQs

Ask a Question

How to determine the port number used by SQL Server and configure a static port

Goals and Symptoms

Summary
When attempting to connect to a specific SQL 2005 instance from a custom or third party software program, it is common that the port number used by that SQL Server instance be specified in the application.

Causes and Fixes

Procedure

Named instances (e.g. <severname>\Powerlogic or <servername>\SMS3000) of the Database Engine and SQL Server are automatically configured for dynamic ports. This means that they select an available port when the SQL Server service is started, which may vary each time the service is started.

To determine the TCP/IP port number of the instance of SQL Server 2005

1. Open the SQL Server Configuration Manager (Start > All Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration Manager)
2. Expand SQL Server 2005 Network Configuration and highlight Protocols for <instance name> (e.g. Protocols for POWERLOGIC)



3. Double-click on TCP/IP in the right window
4. Click on the IP Addresses tab
5. If TCP Dynamic Ports are enabled, the current TCP port being used is listed at the bottom of the window: TCP Dynamic Ports


Note: TCP Dynamic Ports is set to 0 to enable dynamic ports


To determine the TCP/IP port number of the instance of SQL Server 2000

1. Start the Server Network Utility (Start > All Programs > Microsoft SQL Server > Server Network Utility)
2. Select the General tab and then select the instance name of interest (e.g. SMS3000) from the list of instances



3. Click on the TCP/IP and then select Properties.The TCP/IP port that is being using is listed





For certain situations it may be beneficial for the custom application to use a static port to connect to SQL

To configure a static port number for SQL Server 2005

1. Open SQL Server Configuration Manager and expand SQL Server 2005 Network Configuration
2. Expand Protocols for <instance name> and double-click TCP/IP
3. Select the IP Addresses tab and under IPALL, set the desired TCPPort
3. A value of 0 in the TCP Dynamic Ports dialog box indicates the use of dynamic ports. Delete the 0's



4. Click OK.
5. In the console pane, click SQL Server 2005 Services
6. In the details pane, right-click SQL Server (<instance name>) and then click restart, to stop and restart SQL Server




To configure a static port number for SQL Server 2000:

1. Start the Server Network Utility. (Start > All Programs > Microsoft SQL Server > Server Network Utility)
2. In the Server Network Utility dialog box, click the General tab
3. In the Instance(s) on this server list, select the instance of SQL Server (e.g. SMS3000)
-If TCP/IP protocol is disabled, enable it now. To do this, click TCP/IP in the Disabled Protocols list, and then click Enable
4. In the Enabled Protocols list, 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 number entered must be different from the current dynamic port number that is being used by the SQL Server instance.


6. Click OK, and then click OK again
7. Restart the instance of SQL Server
text
Was this helpful?
What can we do to improve the information ?