[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