Been playing on my workstation with VS2013, WebAPI, and code first technology.While trying to build a new database based on a tutorial I started to lose the ability to connect or it was intermittent.
Later I tried to connect ot several of my other databases and sometimes i can connect and sometimes I can't. Sometimes SSMS is trying to connect using Named Pipes even though I've specifically selected TCP/IP. It seems to be doing the same when using a custom app.
My guess is that somehow my libraries or possibly related entries in the registry are corrupt.
Is there a way to clean up or repair this issue?
Thanks in advance
Kyle J.
Code first? Yuck! Almost a guarantee of bad database design.
I doubt your libraries or registry are corrupt. Perhaps you really do have network problems? Or are all the databases on your workstation? (that might explain the named pipes)
What error are you getting when it can't connect? Regarding which library it uses, you can force it by specifying tcp, like this: tcp:servername\instancename
But this might be due to a connection timeout. If that's the case, you can increase the timeout value in the SSMS settings.
I'll need to see the error.
All the databases are on separate server machines. The only protocol enabled on those machines is TCI/IP.
My workstation is set to only use TCP/IP for Sql. I have no other issues with hitting the internet, or pinging the serves, or even doing RDP into those servers.
I can run SSMS on one server and talk connect to all the others...so the issue is my workstation.
I have run Windows Defender and even let the system do an entire checkdisk to see if it found anything. (No it didn't).
I just opened SSMS again and I'm trying to connect to one of my servers. The connection should have timed out as it's been a few minutes, but the "busy bar" is happily scrolling left to right and it's not generating an error.
Windows Firewall is completely disabled....I double checked.
Weird happenings, while SSMS was in it's connection loop which should have timed out, I loaded my VS2008 instance. All of a sudden SSMS starts to work normally. I can connect to everything..
So I shut down VS2008, I shut down SSMS. I start SSMS back up and try to connect to the same servers....no go...same issue.
I don't remember having to do this prior but...I ran SSMS as Administrator and now it is able to connect to the remote servers without any issue.
Going to try the same with one app that has the same issue.
So it seems for what ever reason, something related to the sql libraries on my workstation now requires SSMS and my stand alone app to run under Windows Administrator privileges in order to make a connection to a sql server.
Anyone have any ideas about this? One of the Windows Updates perhaps? Although I hadn't applied any before the issue start.
So here is a quick review of what I have found out...
On my workstation any app that connects to a sql server must be run as an Administrator. This includd SSMS and any custom in-house apps.
Anyone have ANY ideas on this?
This means that your normal Windows login does not have any permissions in SQL Server. You need to add your login to the server logins and then give it permissions in the databases you want to access.
I cannot connect to any of the sql servers using the sa account and password either.
Seems like it might be a permissions problem on my LOCAL machine. Write access to a folder or event queue or something weird.
The sa account may be disabled.
Not likely. Can you post the exact, full error message you get when you try to connect?
If I RDP into any of the SQL servers, I can connect to the local server or any of the remote servers using the sa account.
still waiting to see the error message....
I finally had a break where I could let it sit....it took 14 minutes, but it finally did connect to the server.
Once this connection was made I was able to connect to all my other servers.
I exited and then restarted SSMS. It again took near 14 mins to connect to a server.
No actual error message or dialog was shown. Nothing in the event logs either.
So...something is eating up your bandwidth. nothing you can do on your PC to change that. Have to talk to your network boys.
If it were a true network issue, running SSMS as Administrator would have the same problem and it doesn't.
My educated guess is that it's a local permissions problem on my workstation.
I'm just not sure HOW to figure out what permissions might have changed.
that seems pretty unlikely. If you didn't have permissions, you could never connect. Yet, you can -- even if it takes a while. That rules out permissions.
It could very well be that running as Administrator does have the same issue, but you've been lucky so far. Try this:
Using SQLCMD, attempt to connect every few seconds (You could use a little BAT script to do it). Run one set of commands as Administrator and the other set as a normal user Run them simultaneously and observe the results.
I'm suggesting SQLCMD to remove the GUI from the problem. SSMS is big and takes a while to start up (at least on my boxes!)
Longshot: Can DNS, or whatever, be different if logging in as Admin rather than User?
Hypothesis being that when logged in as User the route that the packets are taking is rubbish - I know squat about the hardware network layers though, so it may be that more/different/slower "hops" is not actually a possibility based purely on Admin/User login.
From sqlcmd I can connect to my newer servers, run a query and get the output. The script hits 4 servers in a row without any issue and I can punch it as fast as I can click my mouse in PowerShell (NOT running as administrator.)
So, at the same time you are connecting using SQLCMD, can you try to connect using SSMS? Does SQLCMD work while SSMS hangs or waits a long time?
If so, just for comparison, try using Visual Studio to do the connection (Or LINQPad, or your own .NET program)