Connection Info

This query allows you to see how connections are being made, including things like authentication protocol, connection time, TCP ports

-- Requires VIEW SERVER STATE permission on the server.

SELECT session_id,
       connect_time,
       --switch the following to GETDATE() for SQL Server 2005 or earlier
       DATEDIFF(day, connect_time, SYSDATETIME()) AS days_connected,
       CONVERT(NVARCHAR(50), SYSDATETIME() - connect_time, 8) AS time_connected,
       net_transport,
       protocol_type,
       protocol_version,
       encrypt_option, -- Is an SSL connection in being used
       auth_scheme, -- NTLM/KERBEROS = Windows Authentication, SQL = SQL Authentication
       node_affinity,
       client_net_address,
       client_tcp_port,
       local_net_address,
       local_tcp_port, -- port instance currently listening on
       connection_id,
       parent_connection_id
FROM master.sys.dm_exec_connections