Find IP and Port using SQL SSMS
Scenario
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
|
1 2 3 4 5 6 7 8 9 10 11 |
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
|
1 2 3 4 5 6 7 8 9 |
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
Facebook Comments Box
Comments
Thanks, nice info
Thanks Amit,
Keep visiting our site and provide comments to improve the quality of post.