[SQL] Force the protocol (TCP, Named Pipes, etc.) in your connection string - Jon Galloway

[SQL] Force the protocol (TCP, Named Pipes, etc.) in your connection string

Barry Dorrans recently mentioned that you can force the database connection protocol by specifying np: or tcp: before the server name in your connection string. I've jumped through some hoops before using localhost to target tcp and (local) to target named pipes, but it looks like there's a much better way to do this (since MDAC 2.6).

There's more info in MS KB Article 313295:

TCP/IP:

server=tcp:hostname
You can optionally specify a specific port number. By default, the port is 1433.
server=tcp:hostname, portNumber

Named Pipes:

server=np:hostname
You can optionally specify a specific named pipe.
server=np:\\hostname\pipe\pipeName

They recommend that you always use (local) to specify the local machine, and then specify a protocol. Sounds like the right way to connect to the local machine over TCP is to specify server=tcp:(local), and to use named pipes on the local machine you'd use np:(local). To specify a protocol when pointing to a server, you'd use server=tcp:DBSERVERNAME.

To quote my previous post:

There are many differences between TCP and Named Pipe connection, but if you're on localhost you're mostly concerned with simple access.

  • The default ASPNET account generally has an easier time with TCP, since the  ASPNET user doesn't have access to named pipes by default (http://support.microsoft.com/Default.aspx?id=315159).
  • If you're using impersonation, Named Pipes is usually simpler. If you're using impersonation with no username specified, you're running under the IIS Authenticating user. This defaults to IUSR_MACHINENAME if you're allowing annonymous access, which generally has access to the IPC$ share required for named pipe communications.  

You can target other supported protocols, too:

Multiprotocol = rpc
Shared Memory = lpc
NWlink  IPX / SPX = spx
Banyan VINES = vines
Apple Talk = adsp

Published Saturday, February 24, 2007 7:09 PM by Jon Galloway

Comments

# re: [SQL] Force the protocol (TCP, Named Pipes, etc.) in your connection string

Don't forget that SQL2005 has TCP/IP (and named pipes) disabled by default. So if you force a protocol you do need to enable it, even when attaching to localhost

Sunday, February 25, 2007 2:49 AM by barryd

# re: [SQL] Force the protocol (TCP, Named Pipes, etc.) in your connection string

This blog post is still helping developers more than a year later. ;-)

Friday, March 21, 2008 4:46 PM by Mark Wisecarver

# re: [SQL] Force the protocol (TCP, Named Pipes, etc.) in your connection string

My question is in a production server environment, is it preferable to force tcp/ip or let the sqlclient decide?

Thursday, July 24, 2008 7:08 PM by Jason Sirota