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 |
|
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] |
Result
Comments
Thanks, nice info
Thanks Amit,
Keep visiting our site and provide comments to improve the quality of post.