10

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?

2
  • What naming method are you using to connect to the other machine, are you using an IP address (i.e. 192.168.1.1) or a name such as: MySqlServer. I suspect this is a name resolution issue, you can confirm that by putting the name of the Sql server in your hosts file in which case the issue should disappear. Sep 10, 2010 at 15:06
  • By machine name, but it's on my home network and I can hit shares on it by using machine name. It's just SQL that gives me issues.
    – SergioL
    Sep 10, 2010 at 17:35

6 Answers 6

7

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
0
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.

2
  • Already set to off (0) on all databases. However, it's not just the first attempt from client, it's first attempt for each app running on client (so 2nd attempt for SQL Studio connects, while it's running, I spin up VS2010 it fails on first conn, but succeeds on 2nd...even though studio is already connected and running).
    – SergioL
    Sep 23, 2010 at 14:04
  • maybe its DNS/ hostname resolution related? try changing your connection string to connect using sql server IP/port & see if it goes away Sep 24, 2010 at 1:54
2

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!

0

Disable firewall. Test network(ping). Sniff network traffic to sql server(use wireshark)

0

Can you try running SQL Profiler before you connect for the first time with either VS or SSMS, and see what's happening on the SQL Server?

Also, have you checked the event logs to see if anything is being logged?

0

I have just witnessed this first-attempt long pause connection timeout issue on a fresh SQL Server 2017.

To solve it, I added the missing SQL Server application executable inbound traffice rule to Windows Defender Firewall. In order to do so, check the compreensive Microsoft's documentation on this subject

On how come does Windows Firewall allowing successful incoming connections on some of the secondary SQL Server connection attempts, prior previous incomig traffic rule was defined, it remains a mistery.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .