30

I'm getting extremely long delays (10~30 seconds) in SQL Server Management Studio 2014 when attempting to connect to a SQL Server 2012 instance over TCP using Windows Authentication. This happens when connecting Object Explorer or a new blank query window. Once connected, running queries is fast. The problem does not happen when I connect using SQL Server authentication.

Environment:

  • Windows 7, logged in as a domain user
  • TCP connection via IP address (not hostname)
  • The server is at a remote location connected via VPN
  • No encryption

When I logged into a co-worker's Windows 7 computer with my domain account, and connected to the same SQL Server through the same VPN, there was no delay. When the same co-worker logged into my PC with his own domain account, he experienced the delay. These tests show that the problem is unique to my PC. Also, the problem only appears when connecting to this specific SQL Server and VPN; I can connect to other SQL Servers on the local network via Windows Authentication without any delay.

Things I've tried with no success:

  • Disabled anti-virus and firewall
  • Renamed the "12.0" folder under "%userprofile%\AppData\Roaming\Microsoft\SQL Server Management Studio" to "_12.0" to force SSMS to recreate my user settings.
  • Force Network protocol to TCP rather than <default>. I also tried Named Pipes but my server isn't setup for that.
  • Installed SSMS 2012 and tried that instead of 2014.
  • Disabled IPv6
  • Blackholed crl.microsoft.com to 127.0.0.1 in my etc\hosts file.
  • Disabled the Customer Experience Improvement Program in SSMS, Visual Studio, and Windows.
  • Uninstalled all SQL Server related apps from my PC and reinstalled just 2012.

TCPView clues:

  • Using TCPView, I noticed that when I make a new connection, its state becomes ESTABLISHED right away, but then one or two more connections with the SQL Server are continually attempted and closed with TIME_WAIT. On my co-worker's computer, these connections are ESTABLISHED and solid. So I'm pretty sure this is the source of the timeouts, but what are the connections for, and why do they fail? (I don't have any addons in my SSMS.)

Any ideas?

Update: Intellisense/Autocomplete clue(?):

I noticed that once I finally do connect, Intellisense/Autocomplete doesn't work. Do those require separate connections from SSMS? I tried disabling them, and it didn't seem to resolve the long connection delay.

Improve this question
7
  • Have you tried running SSMS with the /log switch?
    – Mister Magoo
    Commented Jun 17, 2015 at 23:20
  • @MisterMagoo trying that now. It doesn't actually log anything about its connection attempts (the file doesn't grow when making a new connection). It's mostly stuff about loading certain packages in the UI, e.g. "Successfully loaded component assembly from cache". I couldn't find any errors or exceptions.
    – Jordan Rieger
    Commented Jun 17, 2015 at 23:28
  • Ok, I wasn't sure if it would, but figured it was worth a quick try. If you are really interested in what is going on, you might need to break out Sysinternals Process Monitor and see if you can identify what is going on. technet.microsoft.com/en-us/library/bb896645.aspx
    – Mister Magoo
    Commented Jun 18, 2015 at 22:22
  • I've been poring over ProcMon dumps for about an hour, but there's just so many events (even filtered down to Ssms.exe) that I can't make much of it.
    – Jordan Rieger
    Commented Jun 19, 2015 at 23:38
  • @MisterMagoo All I can see is that the connection attempts that I saw with TcpView are indeed taking a long time (over 5 seconds each). I'm basing that on seeing the "TCP Connect" event on a particular local port, and then the next time I see anything sent or received on that local port is always after at least 5 seconds.
    – Jordan Rieger
    Commented Jun 19, 2015 at 23:46

6 Answers 6

Reset to default
25
+100

Try running a trace with SQL Profiler while you, and then your coworker, connect to the server.
Select RPC, SQL Statement & PreConnect - Starting/Completed.
Select Save Results To Table option, then compare the 2 tables to find the bottleneck.

Or, since you're connecting by IP, it could be doing a Reverse DNS lookup. If so, add a entry in your hosts file.

Improve this answer
4
  • 3
    I added a local etc/hosts entry for the IP address of my server, then tried SSMS again. Bingo! Super fast. Thank you! (I left SSMS connecting via IP address as before, not hostname.) I'm just wondering why I need this workaround but my co-workers don't. It does seem to be DNS related. In any case, this is a pretty good solution for me, so I will award you the bounty once you update your answer.
    – Jordan Rieger
    Commented Jun 22, 2015 at 17:55
  • 1
    I think it was the reverse lookup because when I removed the hosts entry, ping - a ###.###.###.### was very slow before the first ping (paused 5 seconds for the reverse lookup.) When added back the hosts entry, ping -a was fast. There was no difference in the tracert or nslookup though. I think something must be different on my PC that is causing me to do the reverse lookup when my co-workers are not (or it's just much faster on theirs.) BTW, my Intellisense works again now that the connection speed is fast.
    – Jordan Rieger
    Commented Jun 23, 2015 at 0:50
  • 1
    Even a fake DNS entry for the IP in the hosts file makes this work much faster! Thank you!
    – felickz
    Commented Jan 22, 2016 at 19:10
  • 1
    I was getting timeouts trying to connect SSMS across a VPN until I read your answer, yes it makes sense because I was connecting to the server via IP and name resolution wasn't working. Adding an entry in the host file finally made it work after many hours trying to get this working. Thanks! As a footnote I want to say that I am using Windows authentication from different domains with the runas /netonly and it works fine with this solution.
    – RobbZ
    Commented Feb 12, 2016 at 14:55
5

What you should check first is your server or client DNS settings

It's not the rare that your SQL Server have the problem connecting to Active Directory. If you try with local Windows account I am sure that you will not have the at issue. It's not unusual that server is configured with public internet DNS and when SQL Server connects to DC to check the credentials and verify it, it will try top contact the public DNS instead of the DNS server of the AD. Since this information is not stored on the public DNS it will fail to verify and this will cause the delay until it manage to contact the proper DNS server or DC via the NTLM

Since you are not experiencing the problem with other SQL Servers, than almost certainly the issue is not related to AD or DC configurations

Fire the IPConfig.exe /all command from the cmd to check the configured DNS servers. You should have only AD's DNS servers configured. Remove all public DNS servers, and leave just AD's DNS servers.

Improve this answer
2
  • Are you suggesting that the SQL Server is contacting a public DNS to lookup the IP address of the Domain Controller, and that is causing the delay when it attempts to validate my Windows Authentication? If that's the case, why would it work fine from my co-worker's PC on the same local network, over the same VPN? I checked the DNS settings on my computer, and there was an extra third DNS server that my IP department had asked me to add, which wasn't present on my co-worker's PC. But when I removed that server, did a ipconfig /flushdns, and tried again, it was still slow.
    – Jordan Rieger
    Commented Jun 22, 2015 at 17:17
  • Using the IP address or the FQDN of the server (not just the hostname) made a huge differnce for me. Was definitely a slow DNS issue in my case.
    – userSteve
    Commented Oct 3, 2016 at 11:24
1

I extended C:\Windows\System32\drivers\etc\hosts file by adding line like this:

201.202.203.204     mysqlserver

201.202.203.204 is IP address of your SQL Server.

mysqlserver - any name that you like (you do not have to use it anywhere).

This made my server faster.

Thank to: d-_-b, Jordan, Rieger, felickz, RobbZ

Improve this answer
1

Turn off Windows Firewall on your SQL server for the Domain network profile.

  • Start Powershell
  • Run Get-NetFirewallProfile -Profile Domain to check its current state
  • If it's enabled, run: Set-NetFirewallProfile -Profile Domain -Enabled False to turn it off.

If this was it, you can turn it back on later and fine tune your settings. Or, if you're in a safe environment, you can leave it off.

The weird thing is that even if Windows Firewall blocks communication, you will still be able to connect but both the initial handshake and subsequent requests will be incredibly slow. My theory (based on no real evidence) is that in these cases communication falls back on Named Pipes which is a lot slower between remote PCs.

Improve this answer
1
  • I obsere the same. Also cannot work up an explanation.
    – Lionet Chen
    Commented Jul 5, 2020 at 2:22
1

I had this problem when we moved to a data centre. IPS had changed and AD has also changed servers.

To resolve, add the server entry on the hosts of the local machine where Management Studio is located.

add the entry on host file on Windows:

xx.xxx.xx.xxx servername.domain

This did a reverse DNS.

Improve this answer
0
1

I had a user using Server Manager Studio with a domain account for authentication, it was slow to open and slow to show the databases. If I used a sql account to connect it was fast. We were connecting by IP and not a name. Someone made a comment in here about a reverse lookup so I edited my hosts file on my pc and did a test. Bam everything worked correctly. I then looked at DNS and low and behold I was missing my PTR for the sql server. I added the PTR, went to the users computer and everything worked fast at that point. This definitely resolved my slowness.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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