Cannot connect to database using localhost \ sqlserver

Question

I know that we should know such phrases, but suddenly all my connections to localhost\sqlexpress stopped working. Since .\sqlexpress and (local)\sqlexpress work, it would not matter much if it weren’t for the fact that I’m working on a team project where all connection strings are defined in the previous format. And, obviously, it’s hard for me to accept that it should no longer work, so I spent several hours analyzing and reinstalling several versions of SQL Server to no avail. (And on the way, breaking db projects in VS2010, so I also needed to reinstall ...).

But here are the details. As already mentioned, I could not connect to localhost\sqlexpress , but to .\sqlexpress . The same symptoms as with SQL Server Manager and the connection strings for deploying and running wcf services. It's funny enough to allow the default instance as localhost .

Firstly, yes, SQL Server is working, and I’m sure that I turned on TCP / IP and found that the port is fixed at 1433, although it works on the computer of my colleagues with TCP / IP disabled. (Somewhere along the way, I read that localhost can be resolved using shared memory using its own SQL client, so I assume this is what happens on its system.)

Customization is just one instance of SQL Server Express 2008 R2 and VS2010 for Windows 7 Professional.

Running Sql expresses an errorlog:

 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

To diagnose the problem, I use sqlcmd to find out which transport is used:

1. local \ SQLEXPRESS

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

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

2. local

 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'm lost here. Any help is appreciated.

Answer 1

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

Answer 2

You have 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 expires. Enabling this makes the team very good.

Another thought ... Port 1434 is used for SQL Server Discovery Service, which allows programs such as SQL Management Studio, etc. Detect that SQL Server is running on the computer. Another thing to explore.

Answer 3

We had this problem. For some reason, an alias was created on this machine that directed the server name that we used (localhost \ SQL2008_R2) to a server that no longer exists.

Once we removed the alias, it all worked.

Aliases are the node tree in SQL Server Configuration Manager.