[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