Can't connect to SQL Server 2008 - looks like Shared Memory problem

Question

I am unable to connect to my local instance of SQL Server 2008 Express using SQL Server Management Studio.

I believe the problem is related to a change I made to the connection protocols. Before the error occurred, I had Shared Memory enabled and Named Pipes and TCP/IP disabled. I then enabled both Named Pipes and TCP/IP, and this is when I started experiencing the problem.

When I try to connect to the server with SSMS (with either my SQL server sysadmin login or with windows authentication), I get the following error message:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Named Pipes Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

Why is it returning a Named Pipes error? Why would it not just use Shared Memory, as this has a higher priority order in the list of connection protocols? It seems like it is not listening on Shared Memory for some reason?

When I set Named Pipes to enabled and try to connect, I get the same error message.

My windows account is does not have administrator priviliges on my computer - perhaps this is making a difference in some way (as some of the discussions in this post about an "SuperSocketNetLib\Lpc" registry key seems to suggest).

Answer 1

Since now you cannot connect to SQL Server to disable named pipes, you have to force a TCPIP connection.

Here's how:

click on START / RUN then run the cliconfg.exe utility. In this, create an alias for your server (you can use the same name) and put the default for TCPIP

That should force TCPIP over Named Pipes and maybe get you connected.

Try connecting to the alias name that you used in CliConfg and it will now connect using TCPIP.

Answer 2

I had this exact same problem. I'd try connecting as ASHWHVM004\SQLEXPRESS and I'd get a connection failed message. If I explicitly requested Shared Memory, it'd work fine. Shared Memory, Named Pipes, and TCP/IP were all enabled.

I seemingly "fixed" it by doing the following:

1. I opened up Sql Server Configuration Manager and drilled down to SQL Server Network Configuration > Protocols for SQLEXPRESS.
2. I disabled everything except Shared Memory.
3. I restarted the SQLEXPRESS service instance and tried connecting as before. It worked fine.
4. I enabled TCP/IP and Named Pipes and restarted the SQLEXPRESS service instance and tried connecting as before. It worked fine.

Either I did something else during this process to fix it that I don't recall, or the act of disabling and re-enabling caused some twiddly bit to get flipped back the right way.

Good luck!