Cannot connect to database using localhost\sqlserver

Question

I know we should be aware of such phrases but all of a sudden all my connections to localhost\sqlexpress stopped working. As .\sqlexpress and (local)\sqlexpress work, this wouldn't be a big deal if it weren't for the fact that I am working on a team project where all the connection strings are defined in the former format. And obviously I have a hard time to accept it shouldn't work anymore so I spent several hours analysing and reinstalling several SQL Server versions to no avail. (And on the way breaking the db projects in VS2010 so I needed to reinstall as well...).

But here are the details. As already mentioned I couldn't connect to localhost\sqlexpress but to .\sqlexpress. The same symptoms using SQL Server Manager, and connection strings for deployment and running wcf services. Funnily enough resolving the default instance as localhost works.

First things first, yes the SQL Server is running and just to be sure I enabled TCP/IP and set the port fixed to 1433 although it works on my colleagues machine with TCP/IP disabled. (Somewhere along the way I read that localhost can be resolved using shared memory when using the native SQL client so I guess that is what's happening on his system.)

The setup is only one SQL Server Express 2008 R2 instance and VS2010 on Windows 7 Professional.

Starting up Sql express the errorlog says:

Server is listening on [ 'any' <ipv6> 1433].
Server is listening on [ 'any' <ipv4> 1433].

Netstat says (excerpt):

TCP    0.0.0.0:1433           Machinenename:0             LISTENING
TCP    [::]:1433              Machinenename:0             LISTENING

I note the absence of 127.0.0.1:1433 and any UDP on port 1433

In order to diagnose the problem I use sqlcmd to find out what transport is used:

1. localhost\sqlexpress

sqlcmd -Slocalhost\sqlexpress  -> Shared memory
sqlcmd -Stcp:localhost\sqlexpress ->  SQL Server Network Interfaces: Error Locating Server/Instance Specified

Cannot connect to localhost\sqlexpress from SQL Server Management Studio even if I specify shared memory as protocol

2. localhost

sqlcmd -Slocalhost -> TCP
sqlcmd -Stcp:localhost -> TCP

Can connect to localhost from SQL Server Management Studio

3. (local)\sqlexpress

sqlcmd -S(local)\sqlexpress  -> Shared memory
sqlcmd -Stcp:(local)\sqlexpress  ->  SQL Server Network Interfaces: Error Locating Server/Instance Specified

Can connect to (local)\sqlexpress from SQL Server Management Studio

So I am lost here. Any help is highly appreciated.

Answer 1

Have you verified that Shared Memory is enabled in SQL Server Configuration Manager, under SQL Server Network Configuration | Protocols?

When I disable the "Shared Memory" protocol, "sqlcmd -S.\sqlexpress" times out. Enabling it makes the command work just fine.

One more thought... Port 1434 is used for the SQL Server Discovery service, which lets programs like SQL Management Studio and the like discover that SQL Server is running on the machine. Just one more thing to investigate.

Answer 2

We had this problem. For some reason, on this machine, an Alias had been created that directed the server name we were using (localhost\SQL2008_R2) to a server which didn't exist any more.

Once we deleted the Alias it all worked.

The aliases are a tree node in SQL Server Configuration Manager.

Answer 3

in MS SQL, go to Configuration tools -> SQL Server Configuration Manager Select SQL Server Network Configuration -> Select protocol in the right side window enable tcp/ip and restart the services in services.msc then try to connect to localhost\sqlexpress