SQL Server – Network – Get TCP Ports

Background

Get network ports that the current SQL Server Instance is using.

Outline

There are some pathways to getting the SQL Server Network Ports:

  1. sys.dm_server_registry
    • SQL Server Version
      • v2008

Code

sys.dm_server_registry

SQL


select 

          tblDSR.registry_key
        , tblDSR.value_name
        , tblDSR.value_data
        , [userOrAdmin]
            = case
                when (tblDSR.registry_key like '%AdminConnection%') then 'Admin'
                else 'User'
              end

from   sys.dm_server_registry tblDSR

/*
	Database Engine - MSSQLServer
*/
where
       (

            (
                ( tblDSR.registry_key like '%\MSSQLServer\%' )
			)
        )

/*
	Filter in TCP Ports
*/
and
       (

            (
                tblDSR.value_name in
                    (
                          'TcpDynamicPorts'
                        , 'TcpPort'
                    )
            )

        )

/*
    Filter out \IP1 thru \IP4
        HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp\IP1
        HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp\IP2
        HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp\IP3
        HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp\IP4
*/
and
       (
            ( tblDSR.registry_key not like '%\IP_')
        )

/*
    Filter out empty data
*/
and
        (

                ( tblDSR.value_data is not null )
            and ( tblDSR.value_data != '' )
            and ( tblDSR.value_data != '0' )
        )

Output

Output – Default Instance

sysDOTdm_server_registry__Default__20180606_0258PM.png

Output – Named Instance

sysDOTdm_server_registry__20180606_0239PM.png

Explanation

  1. Registry Key
    • Please close out your eyes to the AdminConnection; unless you are trying to connect to the Admin Port
    • Most people will be using the regular user default port

References

  1. Microsoft
    • Docs / SQL / Relational databases / System dynamic management views
      • sys.dm_server_registy

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s