Friday, 27 April 2012

SQL Server login timeout on first attempt, second immediate attempt success

Was having a login issue today with SQL Server after the installation.
There're 2 servers, one for AOS and another one for SQL Server.
Both server running Windows Server 2008 R2.
The SQL Server has 2 named instance (one SQL Server 2005 and another one SQL Server 2008 R2), firewall enabled, Named Pipes & TCP/IP has been enabled, port for SQL Server has been opened.
SQL Server Browser service is running.

When starting up the AOS, it failed with the error message below: 
Object Server 12: Fatal SQL condition during login. Error message: "[Microsoft][ODBC SQL Server Driver]Timeout expired"

Trying to connect to the SQL Server using SSMS (from another server) gives this error:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2)

First attempt to connect using SSMS failed with the above message, but on second attempt, it immediately connected, login success. It seems weird the login only timeout or failed on first attempt. This error can be consistently replicated, just close the SSMS, reopen and try login again, the error occur, then second login works.

Tried changed the "Remote Login Timeout" settings (Under SQL Server instance properties, on the "Advanced" page) to a higher number, but it doesn't help, the first login attempt still failed.

After further investigation, the reason was actually on the SQL Server Network Configuration.
The SQL Server is listen to dynamic ports.

Resolution: Change it to fixed port number
- Open SQL Server Configuration Manager
- Expand SQL Server Network Configuration
- Select the SQL Server instance you wanted to connect to
- Double click on TCP/IP
- Select the IP Addresses tab
- Under the IPAll group, remove the value in "TCP Dynamic Ports"
- Enter "1433" into "TCP" port (you can change the port according to your needs)

That resolve the issue.

SQL Server port settings

1 comment: