How do I troubleshoot error 26: "Error Locating Server/Instance Specified"

Question

I'm trying to configure TCP access to my SQL Server named instance (named SQL2012). I use management studio to connect to the database engine. I type in the server name: "tcp:localhost\SQL2012" and I receive this error message:

(provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

This post by the SQL Server team seems to be really useful, in it they suggest testing out the SqlBrowser service using PortQry. When I do, I get this info:

Querying target system called:

 localhost

Attempting to resolve name to IP address...


Name resolved to 127.0.0.1

querying...

UDP port 1434 (ms-sql-m service): LISTENING or FILTERED

Sending SQL Server query to UDP port 1434...

Server's response:

ServerName MSWART7
InstanceName SQL2012
IsClustered No
Version 11.0.5058.0
tcp 52329

   ♥K   K P☺K 5 - 2 1 qK 1 4  rK 8 7 0☺    1 qK

==== End of SQL Server query response ====

And sure enough, I'm able to connect to the database when I specify the server name "tcp:localhost,52329"

I'm not sure what I'm missing. SQL Browser seems to be functioning and ports and firewalls seem to be open or irrelevant (I'm trying to connect to localhost).

Where do I look next?

Answer 1

When you try to connect to a local instance, SQL Server will always attempt to use Shared Memory. This connection will work fine:

localhost\SQL2012

This works in my scenario, and the connection is indeed using Shared Memory in this case.

SELECT session_id,net_transport,local_net_address,local_tcp_port
FROM sys.dm_exec_connections;

Results:

session_id  net_transport   local_net_address   local_tcp_port
----------  -------------   -----------------   --------------
53          Shared memory   <local machine>     NULL

If you want to try to override Shared Memory (not sure why, on a local machine) and force TCP/IP instead, you can't mix that with specifying the named instance explicitly, at least in all instances I tried (no pun intended). The named instance forces SQL Server to map the instance name to the port number, using SQL Server browser, but this apparently can't be done when you force TCP/IP in the first place (the whole point of using TCP/IP is so you go straight to the port and not bother with the browser). If your named instance happens to be running on its own IP address and forced to port 1433, you may be able to get this to work; you may also be able to get it to work if you are using a Client Network alias (but I could not).

I was able to connect if I looked up the current dynamic port assigned to that instance in Configuration Manager (in my case it was 49538), and then connect using tcp:localhost,49538:

So now:

SELECT session_id,net_transport,local_net_address,local_tcp_port
FROM sys.dm_exec_connections;

Results:

session_id  net_transport   local_net_address   local_tcp_port
----------  -------------   -----------------   --------------
53          Shared memory   <local machine>     NULL
54          TCP             10.211.55.10        49538 

All variations I tried - across multiple machines and versions - where I tried to combine a named instance with overriding shared memory and use TCP/IP instead - yielded the same error that you received. This is both when I prefixed the server/instance name with tcp: and when I specified TCP/IP for the network protocol in the connection properties dialog.

Long story short: If you really need to force TCP/IP for a local connection, you're going to need to use a fixed port for that instance, and specify the port. Or use 1433 (which will only work if there's no default instance or if you have a dedicated IP address for that instance), then you won't need to specify the port, but you'll still need to specify the tcp: prefix or use that option in the connection dialog.

I do not suggest trying to disable Shared Memory. FWIW.