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).

Solution 1

Had the same problem. Here is what solved it for me:

1. Open SQL Management Studio
2. Connected through the admin account I used to setup the instance
3. Selected the server from Object Explorer
4. Right mouse click properties
5. Server properties opens
6. Go to Security
7. Changed it from Windows Authentication mode to SQL Server and Windows Authentication Mode

Solution 2

I didi everything google teaches and can't connect with TCP/IP successfully. Then I do these steps to make it work in my home computer:

step 1. Enable TCP/IP in SQL Server Network Configuration for your DB instance.
step 2. Choose properties of TCP/IP and click IP Addresses tab.
step 3. Inactive all IP address except 127.0.0.1.
step 4. Set TCP Port to 1433 in 127.0.0.1 and IPALL.
step 5. Restart sql server service.
step 6. Connect it with TCP/IP using '127.0.0.1\SQLEXPRESS,1433' in Toad for SQL Server freeware, it works!
step 7. Connect it with same server string in php, it works!
step 8. If you need read Chinese string into php program as I am, download MS SQL Server driver for PHP(Believe me, don't even try php_mssql stuff, it'll drive you crazy with Chinese data). For PHP 5.2x, use ts_VC6 version. For 5.3X, use ts_VC9 version (especially when in Win7).
step 9. Edit php.ini (For Appserv, it's under C:\Windows7; for WAMPserver 2.2, it's under c:\wamp\bin\php\php5.3.8), find extension=... line groups. Add 'extension = specific sql erver driver dll filename you are to use.
step 10. Unzip the file downloaded in step 8 and copy the specific dll (or copy all) to php extension directory (you can find it in php.ini's extension_dir=... line). For Appserv, you better copy it to c:\windows\system32, too(Don't ask me why?).
step 11. restart apache service of Appserv or restart Wampserver.

Now you can use TCP/IP to connect with SQL Server 2008 and you can read utf-8 string (in my case) into php program correctly.

Hope this helps those who are deperately looking for this solution, as i am.

Comments

[Proposition Joe]

I created a new alias as you suggested, with TCPIP as selected "network library", but still no luck. I get an error that suggests it can't even find the server: "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) (Microsoft SQL Server, Error: 10061)"

[Proposition Joe]

By the way, when using the cliconfg tool, I noticed that the existing alias for the server, which I have been using successfully before these errors appeared, had network library set to "Named Pipes". However, before I enabled Named Pipes (and set off the problems), I was using Shared Memory only. Very strange, but perhaps this explains why the initial error message is focused on Named Pipes.

[Toby Allen]

adding the port number with a , at the end of the server name did the trick for me. Thank you.

[sboisse]

This worked for me. I might add using telnet on the local machine and remote machines help determine if it is not working remotely only, or if it is not working locally as well (using 127.0.0.1). cmd: telnet <hostname or ip> 1433. I also had to run a script to enable sql server ports through the firewall: support.microsoft.com/kb/968872

[KarelG]

after reading many possible solutions concerning the named pipes, finally your solution has been helpful for me. I didn't knew that the WIM only was set as default. ( MS SQL 2012 ). thank you !