Here’s what more than 2,500 business decision-makers around the world say.Download the report
...with the Facility Hero app in hand! Discover how you can optimize your maintenance efforts today.Learn more
Access tailored services, 24/7 self-service and expert help. Anywhere, anytime, any project.Discover now
Everything you need to know about our IoT technology backbone.Learn more
Explore our global offerings or select your country from one of our five regions.Global
Découvrez nos offres globales ou sélectionnez votre pays dans l’une de nos cinq régions.
Goals and Symptoms
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
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
Sign up for all the latest solutions, offers and best practices straight to your inbox.
We are excited that you have joined the group. You will receive your welcome email soon describing the program and what to expect in the upcoming weeks. Enjoy!