Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

This is a strange one that has plagued me for a while now. When logging in to the second instance of SQL Server 2008 on one of our database servers, we get a timeout error:

Cannot connect to servername\mssqlserver2.

Additional information:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server)")

(This is the error message when trying to connect with Microsoft SQL Server Management Studio; other tools experience the same error, but of course say it in different ways.)

Immediately re-attempting to log in works just fine, so whatever the cause it's ephemeral! This happens regardless of user or authentication type (both Windows and SQL Server authentication methods are supported on this instance). What's even weirder, though, is that the first instance on this server has never once demonstrated this problem.

Server is a Windows Server 2008 R2 virtual server, hosted in Microsoft Hyper-V (host is likewise Server 2008 R2). The server has 2GB of RAM, and seems to regularly be using 90% of that -- could low memory be the cause of this issue? I could see this second instance -- which is not used very often yet -- being swapped out to disk, and then taking too long to load back into memory to respond in time to the connection request, but I'd rather have more than just my own hunch before I go scheduling a downtime for this server (the first instance is used regularly) and then just throwing extra resources at it in the blind hope that the problem goes away.

share|improve this question
    
What protocols are enabled for the second instance? Are you trying to connect using SSMS running on the same server as the second instance or are you connecting over the network? –  joeqwerty Jun 28 '11 at 1:56
    
What do you expect with a virtualised, hardly used server? –  gbn Jun 28 '11 at 3:57
    
@joeqwerty Shared Memory, Named Pipes, and TCP/IP are enabled; VIA is disabled. (Same on both instances.) I'll need to confirm at a later time (already logged in to the server this morning, so the issue is not present at the moment), but I think I do get the same problem connecting from localhost (I normally connect over the local network). @gbn The server is used very regularly. The first instance of SQL Server on it works fine; only the second instance has a problem, but not the server in general. –  Kromey Jun 28 '11 at 16:27
add comment

1 Answer 1

up vote 2 down vote accepted

Solved!

Turns out the issue was a firewall one, although why it would work on the second attempt is still a mystery to me -- generally if a firewall is getting in the way, it stays in the way, it doesn't magically allow a second attempt right through...

Anyway, it turns out that the first instance was configured to listen on TCP port 1433, however the second instance was configured for "TCP Dynamic Ports" on 59196 (or something like that); what's so "dynamic" about a single port is beyond me, but whatever.

Adding a firewall rule to the Windows Firewall to allow connections to that port immediately resolved my problem. I then took it a step further, however: Instead of using this "dynamic" port, I changed it to use TCP port 1432 (1434 being used for administrative connections, or something like that), and modified the firewall rule to allow that port instead.

The port configuration can be found in SQL Server Configuration Manager --> SQL Server Network Configuration --> TCP/IP Properties --> IP Addresses; under this tab, I simply modified the settings in the IPAll section, ignoring the myriad IPn sections (which were all disabled anyway): blank the "TCP Dynamic Ports" entry, and enter 1432 for "TCP Port". Changing these settings does require that you restart your SQL Server instance.

share|improve this answer
    
+1. Good work finding the cause of the problem and the resolution. –  joeqwerty Jun 28 '11 at 23:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.