During a recent development and testing session of SQL Agent Insight, connections to my local SQL test instances were taking over 10 minutes to connect, regardless of the version or edition, and to be honest, it was pissing me off. The connection dialog in SQL Agent Insight, because of this incident, has been directly modified to show connection time now. I used those connection times to show me that the first connection to any of my test SQL instances was taking anywhere from 810-819 seconds. Any connection thereafter was less than 1 second. I found this super puzzling; why was the connection succeeding but taking so long for the first one?
What got me into this mess? Looking at task manager, I noticed that my system hadn’t been restarted in 130 days, so I thought, it’s about time to do some Windows updates that I keep getting nagged about, especially since one of them was a security patch for .NET, which might affect future development. Did those, didn’t think anything of them, rebooted and then that’s when my issues started.
Every time I would attempt to connect to a SQL instance, the first attempt was taking forever, and by forever I mean over 10 minutes consistently. To the “Google” I turned, and couldn’t find anything, initially. I did find some posts stating that I should run the application as administrator, i.e. with elevated privileges. I thought I don’t have to do this, I’ve never had to run my IDE as administrator and develop on my system as an admin, what the hell? Low and behold, doing so resolved the issue. But this didn’t satisfy me. Something in the background happened to my system after I rebooted, but what was it?
I started to do some connection testing and this was the process that I went through. If you have come to this page because of a similar issue, you will identify with these troubleshooting steps.
- Uninstalled all of the Windows updates and rebooted. I figured, Windows updates in the past have been known to cause issues, so I will get rid of them. Problem was still there after reboot. I then thought, perhaps one of those updates was corrupt or something, so I reinstalled them, rebooted, and still had the problem. Hmm, no better off, I still had to run my IDE as an admin. OK…so I left the updates installed, since hey, I should be safer for it now right?
- Booted up a test instance of Windows 10, opened a share back to my software build output folder and ran SQL Agent Insight. Connections to the test SQL instances were immediate. OK, further progress I thought. This was isolated to my main development system.
- Turned off UAC (User Account Control). This worked! But I didn’t like the fact that my system would be less secure of it. So I turned it back to its default setting.
- Turned off my local Windows firewall. No go. Connections were still taking over 10 minutes. Damn. Firewall turned back on, because like UAC, don’t feel comfortable without it running.
- Turned off the firewall on the SQL instances. Again, no go. Connections still taking forever. So back on the firewall went.
- Double-checked all of the settings within my SQL instances to ensure that the connection protocols were valid. I typically turn off everything but TCP/IP for the instance in SQL Server Configuration Manager, so wanted to make sure this was still enabled. Yes it was.
- I even went as far as modifying the app.manifest file for the project, changing the requestedExecutionLevel from “asInvoker” to “requireAdministrator” as a temporary solution, thinking that if I couldn’t figure out my problem, perhaps others were having the same issue and forcing the admin permissions would help. I didn’t like this “solution” either, since from the beginning of development with this project, I’ve never had to increase my local privileges to run the .NET app. So, changed this back to “asInvoker”.
- Started to troubleshoot connectivity to my test SQL instances. I could ping them by IP and by name, so I knew there wasn’t a DNS issue of sorts. I was able to telnet to the SQL port and get a response. So why was I able to connect instantly via telnet versus waiting what seemed like eons from a .NET application?
- And lastly, I shutdown the SQL instance completely. The first initial connection was still taking over 10 minutes. It would eventually fail, which was expected, but this further reaffirmed my suspicion that the problem was with my development system.
At this point (after a couple of hours and some BEvERages), I left the situation. I could continue to develop, but had to start my IDE and in-turn the compiled .NET application with administrator privileges for connections to the SQL instances to happen as “normal” and not take over 10 minutes initially to do so.
Then came tonight. Sometimes you have to sleep on things to get a clear head and to solve problems more effectively. I could say it was a result of a few BEvERages, that resulted in the “clearer” thinking, but I doubt it 🙂
I don’t recall what I searched for on the net, but I did come across this article, though not directly related to developing .NET applications, the thread was referencing how users were having issues with SSMS (SQL Server Management Studio) taking forever to connect to SQL instances. Low and behold there was a post referencing how Microsoft Premier Support was contacted and how it was direct result of upgrading their VMware Horizon View Client. I thought, this is a stretch, but hey, I do use the client for connecting into remote systems that I consult for; I’d be no worse off in trying the posted solution. So I followed the VMware KB article titled, “Launching applications requires elevated permissions after uninstalling or upgrading VMware Horizon View Client 3.1 or 3.2 (2105977)“, backed up the posted registry key, modified my registry based on the article, rebooted and bingo! It worked, connections to my SQL instance were back to under a second. Awesome, back in business!
And come to think of it, because of the consulting work I do, I did recently upgrade the Horizon View Client to version 4.2 (though I don’t remember the previous version), but didn’t reboot after the upgrade, so this “issue” was hiding and waiting during those 130 days of up-time.
So, there you have it. The problem, the eventual resolution, and some moral I’m sure is buried in there somewhere. Hope this article helps someone.