Hi Friends,

We all know that there are 4 protocols that can be used to connect a SQL Server instance from a client; namely Share Memory, TCP/IP, Named Pipes & VIA. At least one protocol needs to be enabled for a successful connection. You can enable all 4 if you wish so and you can also set the order in which they are attempted. Many a times, you will see Share Memory being used when the client and the instance to which you are connecting are on the same box. TCP/IP is widely used to connect to remote servers on inter-connected systems. So on and so forth. You can enable, disable, set the order of protocols – all from SQL Server configuration manager.

1_Which_protocol_is_being_used_for_client_server_connectivity_in_SQL_Server

Remember, the client also needs to be configured with the correct protocols to connect to a SQL Server instance. SQL Server Native client is installed on client machine as part of SQL Server client connectivity setup which sets up the protocols, their order, enabling them, etc.

2_Which_protocol_is_being_used_for_client_server_connectivity_in_SQL_Server

Finally, while you are connected to SQL Server and firing your queries or managing your boxes, you can find out which protocol is being used for the current connection:

When I run this on my system, I get the output of Shared Memory, since that’s the order and client and server are on the same box.