views:

2996

answers:

7

After some network maintenance last weekend, we had some trouble with our development server which led to us restart it. Some updates were installed during the restart, which may or may not have been a factor. Since then, we have been unable to connect to SQL Server 2005, even locally via Management Studio. Depending on the protocol used for the connection attempt, we get one of the following two errors:

  1. When you try to connect to the instance by using the named pipes protocol or the shared memory protocol, you receive the following error message: No process is on the other end of the pipe.

  2. When you try to connect to the instance by using the TCP/IP protocol, you receive the following error message: An existing connection was forcibly closed by the remote host.

The aforementioned protocols are all enabled, and the SQL services are all running happily (apart from the SQL Server Agent, which cannot connect to SQL Server either). We also checked the Surface Area Configuration tool to ensure it accepts connections.

It's not a certificates problem (as seen online) as we don't use encrypted connections.

Any suggestions? Otherwise we will probably resort to a full reinstall.

** UPDATE **

Further clues:

  • We can connect to Integration Services, but nothing else
  • We can connect to the instance of SQL Server Embedded Edition running on the same server
+3  A: 

Verify that the SQL Browser service is running. Most likely it's not set to Auto Start.

You might want to look in it's properties to verify it's listening on correct network interface.

Run the SQL Server Configuration Manager. Under the SQL Server 2005 Network Configuration, you will see 4 protocols. Then go to properties for TCP/IP. You should be able to figure it out from there.

The only reason for this to fail is if the SQL Server service or SQL Server Browser services aren't running or if the browser service is misconfigured.

** UPDATE ** Another possibility is the use of a firewall. Be sure to turn off the windows firewall, then try again.

Chris Lively
It is running (set to automatic) but it logs the following warning before starting up:The configuration of the AdminConnection\TCP protocol in the SQL instance MICROSOFT##SSEE is not valid.I haven't been able to find where to configure this (didn't even know if it was a proper error).
For this error check the following entry in your registry (MSSQL.2005 may vary). You should try these values (no warranty):[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2005\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp]"TcpDynamicPorts"="1""DisplayName"="TCP/IP"
MicSim
The TCP/IP settings appear to be correct. I've since realised that the error about invalid TCP protocol configuration refers to a different instance of SQL Server, MICROSOFT##SSEE - ours is MSSQLSERVER. Moreover, the problems occur regardless of the protocol in use.
Thanks again, but the Windows firewall is off and we don't use any other firewalls on that machine.
A: 

Have you checked the connection port to make sure it isn't already used by something else?

Jeremy Reagan
Yes, thanks - the port seems to be fine.
A: 

Try connecting using the DAC (Dedicated Administrator Connection). http://msdn.microsoft.com/en-us/library/ms178068(SQL.90).aspx Have you looked at http://support.microsoft.com/?kbid=942861 ? It could be caused be some network adapter chipsets and you can fix the issue by:

Click Start, click Run, type cmd, and then press ENTER.
At the command prompt, type the following command, and then press ENTER:
Netsh int ip set chimney DISABLED

notandy
Thanks for the suggestion, but it just gives the same error.
Have you looked at http://support.microsoft.com/?kbid=942861 ?
notandy
The Netsh command unfortunately had no effect, but it looked as though the TCP Chimney Offload was already disabled beforehand anyway.
+1  A: 

Check the 'Surface Area Configuration' tool and make sure all the settings are correct (Remote connections set to: TCP/IP, etc).

JFV
+1  A: 

Long shot, but you don't have another instance of SQL Server installed on that box?

I know that might sound odd, but I've had a few cases recently where we've had to jump through hoops because the client has been using Backup Exec as their backup service, which installs it's own instance of SQL Server to manage itself. This tends to screw up all sorts of things with unpredictable results - to the point where when doing upgrades on a couple of occasions we've removed Backup Exec, done the upgrade, and reinstalled. It's perfectly possible when you do the connect you're not attempting to connect to the instance of SQL Server you think you are

Cruachan
We do actually appear to, yes - we have an instance of Embedded Edition running, MICROSOFT##SSEE, as well as our normal MSSQLSERVER instance. Unfortunately we have no idea whether it was present before the restart or not, or where it comes from, but stopping it has not helped.
Management Studio has also confirmed that we are trying to connect to the correct instance.
Well that sounds ok. Although I would say we got some very odd behaviour and although it is defiantly systems admin by dead chicken if you are really stuck I would suggest taking any other instances out, even if you have to rationalise by citing simplification
Cruachan
A: 

When I said "It's not a certificates problem (as seen online) as we don't use encrypted connections", I was wrong.

Apparently, some old SSL certificates we had on the server were causing these problems, even though we didn't appear to use them. I still don't know why, but deleting them (and then uninstalling them from IIS) fixed our problem.

Thanks very much for everyone's help!

See http://support.microsoft.com/?kbid=919710
+1  A: 

Have you looked in the ERRORLOG to see what it says?

mrdenny