Which session is using what protocol to connect to SQL Server
Every session will use a Network Transport to connect to SQL Server. Without which connecting to SQL Server is not possible.
Different types of Protocols used to connect to SQL Server:
1.Shared Memory
2.Named Pipes
3.TCP/IP
4.VIA
Shared Memory: This protocol is the default protocol if we are connecting to the SQL Server from the same server where SQL Server is installed.
This protocol will not use any Port number to connect to SQL Server.
Named Pipes: This protocol can be used by using the syntax below mentioned.
For Default Instance
Server Name = \\Servername\pipe\sql\query
For Named Instance
Server Name = \\Servername\pipe\MSSQL$INSTANCENAME\sql\query
This protocol will not use any Port number to connect to SQL Server.
TCP/IP : This protocol uses the port defined in the properties of TCP/IP protocol.
If nothing is defined, then default port number is 1433.
For Default Instance:
Server Name = Instancename,1433
For Named Instance:
Server Name = ServerName\InstanceName,1433
VIA: Virtual Interface Adapter
Specify an Alias Name.
Provide the network interface card (NIC) number followed by a port number in the Via Parameters box. If left blank, VIA will attempt to connect to port <NIC#>:1433.
Specify VIA as the Protocol.
In the Server field, enter the server name or <servername>\<instancename> for a named instance
Query used to find the session details using which port and protocol is as mentioned below:
SELECT session_id [Session ID],connect_time [Connect Time],net_transport [Net Transport],
last_read [latest read date by this session],last_write [Latest write done by this session],
client_net_address [Connecting from IP],client_tcp_port [Connecting from Port],
local_net_address [Connecting to IP],local_tcp_port [Connecting through Port]
FROM sys.dm_exec_connections WHERE session_id = @@SPID;
Hope the above information helps
Thanks
