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.

Jonnadula Sasikumar · Accepted Answer · 2013-07-18 09:58:20Z

8

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

answered Jul 18, 2013 at 9:58

Jonnadula Sasikumar

911 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Appulus · Accepted Answer · 2015-09-07 07:42:31Z

2

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.

edited Sep 7, 2015 at 7:42

Appulus

19k11 gold badges40 silver badges47 bronze badges

answered Jul 6, 2011 at 23:39

James L.

9,5115 gold badges42 silver badges80 bronze badges

Comments

Andrew Shepherd · Accepted Answer · 2015-03-17 22:56:27Z

1

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.

SQL Server Configuration Manager Alias Node

answered Mar 17, 2015 at 22:56

Andrew Shepherd

45.5k33 gold badges148 silver badges212 bronze badges

Collectives™ on Stack Overflow

Cannot connect to database using localhost\sqlserver

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related