ODBC and ADO connection to SQL Server: What protocol is used when connecting to <server>\<instance name>?

We understand when connecting to SQL server that specifying:

-

• ,

That the ADO or ODBC client will typically use tcpip to connect.

However, we observe that when a client asks to connect to:

\ that it seems the client (ODBC in this case) does NOT use tcpip.

We believe in some cases, connecting to:

-

May also result in a named pipes connection.

Questions:

• Since \ is not connecting over tcpip, that means it is using named pipes, right?

• How do I force the connection to use tcpip?

And is the behavior (of the 32 bit Windows ODBC driver for SQL Server) in this regard the same as the behavior of the old dblib client?

If you don't specify the protocol in the connection string, then it will use the protocol specified for the database driver. The protocol order, by default, is Shared Memory, TCP/IP and then Named Pipes. You could also set a default protocol.

To specify which protocol to use in a connection string, add tcp: before the server name. Examples: tcp:server1\instance1, tcp:server2, tcp:server3,1433

You can use SQL Server Configuration Manager to see the protocol order or regedit to see the default and protocol order.

The regkey is HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI11.0 for 64-bit applications and HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\MSSQLServer\Client\SNI11.0 for 32-bit applications.

StackExchange Database Administrators Q#163166, answer score: 4