Find IP and Port using SQL SSMS

Find IP and Port of a SQL Server from a client machine. The script will not provide all the information if you are connected to the host/local machine of the SQL server. It will only work when you are in a client machine, SSMS is installed in the client machine & you are connected to the SQL server via SSMS.

Run the below script in using SSMS (SQL Server Management Studio).

TSQL Script

select
@@servername as [Server Name],
@@servicename as [Instance Name],
client_net_address as [Client Machine IP],
local_net_address as [SQL Server IP],
local_tcp_port as [SQL Server Port],
auth_scheme as [Authentication Mode],
net_transport as [Physical Transport Portocol],
protocol_type as [Payload Portocol Type]
FROM sys.dm_exec_connections
where session_id=@@SPID

or run below TSQL Script

SELECT  
@@servername as [Server Name],
@@servicename as [Instance Name],
CONNECTIONPROPERTY('client_net_address') AS [Client Machine IP],
CONNECTIONPROPERTY('local_net_address') AS [SQL Server IP],
CONNECTIONPROPERTY('local_tcp_port') AS [SQL Server Port],
CONNECTIONPROPERTY('auth_scheme') AS [Authentication Mode],
CONNECTIONPROPERTY('net_transport') AS [Physical Transport Portocol],
CONNECTIONPROPERTY('protocol_type') AS [Payload Portocal Type]

Both the script will provide the same result.

Result