By: Jeremy Kadlec | Comments (1) | Related: > SQL Server Configurations
Problem
We have noticed some connection issues on a variety of our web based applications when the corresponding SQL Server is rebooted. We have noticed the issue with SQL Server 2005 Express edition as well as the SQL Server 2005 Standard edition. It seems like after the reboot we have to change our application connection strings and firewall settings in some circumstances in order to correct the situation. We know in some of the circumstances the applications are in a transient state, but others have been deployed and unchanged for a while. When we have to reboot servers, the whole team is on guard to test the applications and ready to troubleshoot the issues. We having found that in some circumstances SQL Server 2005 is listening on different ports. Should we write this issue off to gremlins or is there a real solution for our problem?
Solution
Based on the issue described, it sounds like the dynamic ports configuration could be the culprit for SQL Server 2005 Express edition installations as well as the named instances of SQL Server 2005 Standard edition. If the default instances are also having a problem, then more research is needed. If that is not the case, then let's walk through configuring a SQL Server 2005 named instance to a static port setting to address the issue once and for all!
|
Start the SQL Server 2005 Configuration Manager |
| Navigate to Start | All Programs | Microsoft SQL Server 2005 | Configuration Tools | SQL Server Configuration Manager |
|
|
|
TCP/IP Properties |
| Navigate to root | Expand SQL Server 2005 Network Configuration | Click on the correct 'Protocols for instance name' entry | In the right pane, double click on the TCP\IP entry | Once the TCP/IP Properties window loads click on the IP Addresses tab |
|
|
| In this example, the 2 IP Addresses are bound to the NIC card. First (IP1) is a public address. Second (IP2) is the loopback address. Third (IPAll) is an option for all IP Addresses. |
|
Configuring the SQL Server Port |
| Under these circumstances, let's set the public IP address to a fixed port (1501), retain the loop back configuration (0) and also specify a all port configuration (1503). |
|
|
| *** Note *** - When a static port is configured for any of the NICs, it is necessary to stop and restart the SQL Server instance in order for the change to be implemented. |
|
SQL Server Error Log - Original configuration |
|
|
| In this example, port 2082 is the static port the SQL Server instance is listening on for non loop back scenarios. |
|
SQL Server Error Log - Final configuration |
|
|
| In this example, port 1503 is the static port the SQL Server instance is listening on for non loop back scenarios. |
Changes to Connection Strings
With this sort of change, be sure to specify the port in your connection strings for your application and in your Management Studio connection to the SQL Server. To reference the SQL Server instance, the format of the connection string should be either:
- <ServerName>[\InstanceName],<Port>
- <IP Address>[\InstanceName],<Port>
Next Steps
- Based on my experience with computers in general and SQL Server specifically, nothing happens due to gremlins. Everything should have a logic answer. At times you might not have all of the information needed or understand the data points you have found, but at some point a logic answer should surface.
- Most times when an issue occurs the last change to the system is responsible. So either keep a log of your changes or rewind your memory to find the issue. Keep in mind that some changes do not take affect until SQL Server or Windows is restarted, so a change a day, week or month ago might be to blame for the post reboot issue you are experiencing now.
- Although the debate for dynamic versus static ports could be viewed from a few different perspectives (positive, disaster waiting to happen, indifferent, etc.), this feature should be understood in case you need to leverage it or troubleshoot it.
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
