-- *************************************************************************
-- ***  Comprobar la versión Cliente SQL de las conexiones a SQL Server  ***
-- *************************************************************************
--
-- GuilleSQL - Un portal sobre Microsoft SQL Server en Castellano
-- https://guillesql.es
--

SELECT 
   session_id
   ,connect_time
   ,net_transport   ,protocol_type
   ,protocol_version
   ,protocol_version_bin = CAST(protocol_version AS BINARY(4)) 
   ,driver_version = 
      CASE CAST(protocol_version AS BINARY(4))
         WHEN 0x07000000 THEN 'SQL Server 7.0'
         WHEN 0x07010000 THEN 'SQL Server 2000'
         WHEN 0x71000001 THEN 'SQL Server 2000 SP1'
         WHEN 0x72090002 THEN 'SQL Server 2005'
         WHEN 0x730A0003 THEN 'SQL Server 2008'
         WHEN 0x730B0003 THEN 'SQL Server 2008 R2'
         WHEN 0x74000004 THEN 'SQL Server 2012 / SQL Server 2014'
         ELSE 'Unknown driver' 
      END
   ,client_net_address 
   ,client_tcp_port
   ,local_net_address
   ,local_tcp_port
   ,auth_scheme
   ,net_packet_size
   ,database_name = DB_NAME(T.dbid)
FROM sys.dm_exec_connections
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS T