Cannot connect to SQL Server remotely or locally after reboot

Question

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

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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!

Answer 7

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