Database Administration
sql-server sql-server-2012 ssms connectivity
Updated Tue, 28 Jun 2022 21:56:22 GMT

SQL Server Management studio slow connection or timeout when using Windows Authentication


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.




Solution

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.





Comments (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. — 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. — Jun 23, 2015 at 00:50  
  • +1 – Even a fake DNS entry for the IP in the hosts file makes this work much faster! Thank you! — 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. — Feb 12, 2016 at 14:55