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.

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?

share|improve this question
    
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. –  Coding Gorilla Sep 10 '10 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 '10 at 17:35

5 Answers 5

up vote 5 down vote accepted

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

Hopefully this will help other desperate people like I was :)

Best regards, radusun

share|improve this answer
    
dude that did. thank you. –  SergioL Nov 12 '10 at 19:44

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!

share|improve this answer

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.

share|improve this answer
    
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 '10 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 –  Nick Kavadias Sep 24 '10 at 1:54

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

share|improve this answer

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?

share|improve this answer

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.