SQL Server – Transact SQL – Get Network Info

Background

Wanted to review the IP Addresses for some of our SQL Server Instances that are hosted on Cloud Servers.

Outline

  1. CONNECTIONPROPERTY
  2. sys.dm_exec_connections
  3. sys.dm_tcp_listener_states

Code

CONNECTIONPROPERTY

SQL

Sample

SELECT 

          [netTransport]
            = CONNECTIONPROPERTY('net_transport')

        , [authScheme]
            = CONNECTIONPROPERTY('auth_scheme')

        , [ipAddress]
            = CONNECTIONPROPERTY('local_net_address')

        , [iPort]
            = CONNECTIONPROPERTY('local_tcp_port')

Output

connectionProperty_20181107_0732AM

sys.dm_exec_connections

SQL

Sample

SELECT
              [machineName]
                = SERVERPROPERTY('ComputerNamePhysicalNetBIOS') 

            , [sqlInstance]
                = SERVERPROPERTY('servername') 

            , [instance]
                = SERVERPROPERTY('InstanceName')

            , [ipAddress]
                = tblDMEC.[LOCAL_NET_ADDRESS]

            , [numberofConnections]
                = count(*)

 FROM sys.dm_exec_connections tblDMEC

 where
        (

            ( tblDMEC.LOCAL_NET_ADDRESS is not null )

        )

 group by

         tblDMEC.LOCAL_NET_ADDRESS

Output

sys.dm_exec_connections.20181107.0738AM.PNG

sys.dm_tcp_listener_states

SQL

Sample

SELECT * 

FROM   sys.dm_tcp_listener_states tblTLS

Output

sys.dm_tcp_listener_states.20181107.0811AM.PNG