SQL Server Timeout on first attempt

I'm having a strange issue where I try to attach to SQL Server 2008 running on a second computer (both machines running Win7 64-bit), either through the Data Sources in Visual Studio or via the SQL Management console itself.

On first attempt to connect, it times out. Second attempt works fine.

I can access shares on the second computer without any difficulty, it just appears to be the first time I try to connect to SQL for each application instance. That is, if I open two Visual Studio instances, both will fail on their first attempt to connect, but succeed on the second. I have to connect twice for each instance (regardless of the fail/success sequence in any other application).

I hope that makes sense.

Any advice?

Solution 1:

I think I found the solution, at least in my case it's working. I am using instance name and this automatically imply a dynamic port for the sql server service. I've changed the settings from dynamic to a fix port and then opened the firewall on that port.

SQL Server Configuration Manager --> SQL Server Network Configuration --> Protocols for 'InstanceName' --> TCP/IP --> Properties --> IP Addresses --> IP All -->

Here you see two options:

• TCP Dynamic Ports: 51250 (randomly generated)
• TCP Port: empty - I put here 1433 and then I opened the firewall (in case it was not already opened). You can put whatever port you want (I put 1433 because it was the only instance. In case of multiple instances you should choose for each instance a different port and then open them in firewall)

The script used to easy your task of openning the ports I've downloaded from MS and I am reproducing it here (comments are in german but they should be obvious):

@echo =========  Ports des SQL-Servers  ===================
@echo Aktivieren von Port 1433 für die SQLServer-Standardinstanz
netsh firewall set portopening TCP 1433 "SQLServer" 
@echo Aktivieren von Port 1434 für dedizierte Administratorverbindungen
netsh firewall set portopening TCP 1434 "SQL-Administratorverbindung" 
@echo Aktivieren von Port 4022 für den konventionellen SQL Server-Service Broker  
netsh firewall set portopening TCP 4022 "SQL-Service Broker" 
@echo Aktivieren von Port 135 für Transact-SQL-Debugger/RPC 
netsh firewall set portopening TCP 135 "SQL-Debugger/RPC" 
@echo =========  Ports für Analysedienste  ==============
@echo Aktivieren von Port 2383 für die SSAS-Standardinstanz
netsh firewall set portopening TCP 2383 "Analysedienste" 
@echo Aktivieren von Port 2382 für den SQL Server-Browserdienst
netsh firewall set portopening TCP 2382 "SQL-Browser" 
@echo =========  Verschiedene Anwendungen  ==============
@echo Aktivieren von Port 80 für HTTP 
netsh firewall set portopening TCP 80 "HTTP" 
@echo Aktivieren von Port 443 für SSL
netsh firewall set portopening TCP 443 "SSL" 
@echo Aktivieren des Ports für die Schaltfläche 'Durchsuchen' des SQL Server-Browserdiensts
netsh firewall set portopening UDP 1434 "SQL-Browser" 
@echo Zulassen von Multicast-/Broadcastantwort auf UDP (Aufzählung der Browserdienste OK)
netsh firewall set multicastbroadcastresponse ENABLE

Solution 2:

My best guess here is that you have AUTO_CLOSE turned On for the database. This means that the database needs to spin-up when you connect, which is what is causing the inital time-out.

Second guess is that it may be hostname resolution related. So it takse too long to resolve the hostname the first time (by broadcast maybe?), but then is cached on subsequent connection attempts. What are you using to resolve the host? is it in DNS? Try changing the connection string to be in an IP,port format. i.e. 192.168.100.100,1433

You can also try running ipconfig /flushdns after a successful connection attempt & see if you then get the same behavior. The dodgy workaround is to put the lookup in your HOSTS file, but you should fix it properly.

Solution 3:

Feels like a long shot in the dark wearing a blindfold, but it might help. There's a oldish thread over at the Microsoft SQL Developer forums describing what looks to be the same problem, along with a possible fix. His server is running Windows Server 2008, but might be relevant for your Win7 set up too.

The thread:

http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/58bd9c4d-0572-4567-8e32-82a7fd600022

From the thread:

Yes, i have fixed this problem.

My windows server 2008 was configured to reject SASL LDAP binds (see warning 2886).

Since i have configured my server to not reject such binds, sql server 2008 connections work correct.

You could look at Microsoft KB 935834 for info on modifying LDAP signing settings (can't link to it as I'm a new user).

Hope it helps!