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?

  • If you use (local) or 127.0.0.1 or MSWART7 or the actual IP address instead of localhost, does anything change? What network protocols are enabled in Configuration Manager? (I don't know that you can specify tcp: successfully when using a named instance name, which implies Shared Memory. I can't connect on any of mine that way - I either need to use tcp: with a port, or no tcp: with a name.) – Aaron Bertrand Jul 1 '15 at 18:39
  • The (local), 127.0.0.1 and MSWART7 don't change the behavior of what I'm seeing. The tcp: prefix seems consistent with the alternative method of specifying the protocol in the Connection Properties. – Michael J Swart Jul 1 '15 at 19:28
  • All the protocols are enabled. I found that sys.dm_exec_connections is also a useful dmv – Michael J Swart Jul 1 '15 at 19:34
  • Well I still think that you can't specify tcp: for a named instance while also naming the instance. The point of specifying tcp: is that you go directly to the port instead of relying on the browser service to map instance name -> port. What happens when you don't use the tcp: prefix? – Aaron Bertrand Jul 1 '15 at 19:36
  • (Also, note, I can't connect to my local named instance when I use localhost\instance and specify TCP/IP in the Connection Properties, either. IMHO you're mixing two things by specifying the instance name and trying to force TCP. If you want to force TCP for a named instance, you're going to have to specify a fixed port in the service configuration, and specify that port # when connecting. Or use a client network utility alias maybe. For local connections it will attempt to use Shared Memory unless you specify otherwise; named instance isn't specifying otherwise :-).) – Aaron Bertrand Jul 1 '15 at 19:37
up vote 3 down vote accepted

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:

Object Explorer with two connections, one shared memory and one TCP/IP

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.

  • I would like to set up a client alias to a named instance. And client aliases can't use Shared Memory. That's why I'm trying to enable tcp to a local instance. I'd be very surprised to learn that tcp is incompatible with named instances (that use dynamic ip addresses). I've had success with aliases to local named instances in the past, but I can't see what's changed. – Michael J Swart Jul 2 '15 at 12:30
  • Here's the feature that seems to require TCP: michaeljswart.com/wp-content/uploads/2015/07/aliases.png Is this the Client Network Utility Alias feature you said were no longer supported? – Michael J Swart Jul 2 '15 at 12:41
  • @Michael Sorry, what I should have said was that the Client Network Utility no longer exists as a standalone tool; it is now part of Configuration Manager (it's been that long since I've used it). However, this doesn't work for me. Same result even if (a) I hard-code the current port # in the alias, and (b) if I fix the port # for the SQL Server service. – Aaron Bertrand Jul 2 '15 at 12:59
  • Wow, thanks, it's good to have some validation then (I'm not going crazy or missing something obvious) – Michael J Swart Jul 2 '15 at 13:25

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.