Connection using Listener or Server Name

%3CLINGO-SUB%20id%3D%22lingo-sub-3067403%22%20slang%3D%22en-US%22%3EConnection%20using%20Listener%20or%20Server%20Name%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3067403%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20any%20way%20to%20know%20if%20a%20client%20connection%20is%20using%20the%26nbsp%3BListener%20Name%20or%20the%20primary%20Server%20Name%20%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3218547%22%20slang%3D%22en-US%22%3ERe%3A%20Connection%20using%20Listener%20or%20Server%20Name%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3218547%22%20slang%3D%22en-US%22%3EPlease%20use%20below%20query%20to%20find%20required%20information.%3CBR%20%2F%3E%3CBR%20%2F%3ESELECT%20es.login_name%3CBR%20%2F%3E%2Ces.program_name%3CBR%20%2F%3E%2Cec.client_net_address%3CBR%20%2F%3E%2Cec.client_tcp_port%3CBR%20%2F%3E%2Cagl.dns_name%3CBR%20%2F%3E%2Caglip.ip_address%3CBR%20%2F%3E%2Cagl.port%3CBR%20%2F%3EFROM%20sys.availability_group_listeners%20agl%3CBR%20%2F%3EINNER%20JOIN%20sys.availability_group_listener_ip_addresses%20aglip%20ON%20agl.listener_id%20%3D%20aglip.listener_id%3CBR%20%2F%3EINNER%20JOIN%20sys.dm_exec_connections%20ec%20ON%20ec.local_net_address%20%3D%20aglip.ip_address%3CBR%20%2F%3EINNER%20JOIN%20sys.dm_exec_sessions%20es%20ON%20ec.session_id%20%3D%20es.session_id%3CBR%20%2F%3E%3CBR%20%2F%3EUNION%20ALL%3CBR%20%2F%3E%3CBR%20%2F%3ESELECT%20es.login_name%3CBR%20%2F%3E%2Ces.program_name%3CBR%20%2F%3E%2Cec.client_net_address%3CBR%20%2F%3E%2Cec.client_tcp_port%3CBR%20%2F%3E%2C%40%40SERVERNAME%20AS%20%5Bdns_name%5D%3CBR%20%2F%3E%2Csr.value_data%20AS%20%5Bip_Address%5D%3CBR%20%2F%3E%2Cec.local_tcp_port%20AS%20%5Bport%5D%3CBR%20%2F%3EFROM%20sys.dm_server_registry%20sr%3CBR%20%2F%3EINNER%20JOIN%20sys.dm_exec_connections%20ec%20ON%20sr.value_name%20%3D%20'IpAddress'%3CBR%20%2F%3EAND%20ec.local_net_address%20%3D%20sr.value_data%3CBR%20%2F%3EINNER%20JOIN%20sys.dm_exec_sessions%20es%20ON%20ec.session_id%20%3D%20es.session_id%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
New Contributor

Is there any way to know if a client connection is using the Listener Name or the primary Server Name ?

1 Reply
Please use below query to find required information.

SELECT es.login_name
,es.program_name
,ec.client_net_address
,ec.client_tcp_port
,agl.dns_name
,aglip.ip_address
,agl.port
FROM sys.availability_group_listeners agl
INNER JOIN sys.availability_group_listener_ip_addresses aglip ON agl.listener_id = aglip.listener_id
INNER JOIN sys.dm_exec_connections ec ON ec.local_net_address = aglip.ip_address
INNER JOIN sys.dm_exec_sessions es ON ec.session_id = es.session_id

UNION ALL

SELECT es.login_name
,es.program_name
,ec.client_net_address
,ec.client_tcp_port
,@@SERVERNAME AS [dns_name]
,sr.value_data AS [ip_Address]
,ec.local_tcp_port AS [port]
FROM sys.dm_server_registry sr
INNER JOIN sys.dm_exec_connections ec ON sr.value_name = 'IpAddress'
AND ec.local_net_address = sr.value_data
INNER JOIN sys.dm_exec_sessions es ON ec.session_id = es.session_id