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).
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.