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

WMI – Error – “0x8004100E”

Background

Trying to steal and use someone else’s code does not do well for me.

Here I am having stolen a code, but stuck with bad characters and an error message.

Code ( Original )


OPTION EXPLICIT

Dim strComputer
Dim URL
Dim objWMIService
Dim colItems
Dim objItem


strComputer = "."

URL="winmgmts:\\" & strComputer & "\root\Microsoft\SqlServer\ComputerManagement10"

Wscript.Echo "URL is "& URL

set objWMIService = GetObject(URL)

Set colItems = objWMIService.ExecQuery("SELECT * FROM SecurityCertificate",,48)

For Each objItem in colItems

	Wscript.Echo ""

	Wscript.Echo "SecurityCertificate instance"
	Wscript.Echo ""
	Wscript.Echo "ExpirationDate: " & objItem.ExpirationDate
	Wscript.Echo "FriendlyName: " & objItem.FriendlyName
	Wscript.Echo "IssuedBy: " & objItem.IssuedBy
	Wscript.Echo "IssuedTo: " & objItem.IssuedTo
	Wscript.Echo "Name: " & objItem.Name
	Wscript.Echo "SHA: " & objItem.SHA
	Wscript.Echo "StartDate: " & objItem.StartDate
	Wscript.Echo "SystemStore: " & objItem.SystemStore


Next

 

Error

Error Image

Error Message


(null): 0x8004100E

Trouble Shooting

WMI Code Creator

Download WMI Code Creator from here.

Extract

Extract the compressed ( zip) file.

Run

Run the file.

Namespace

Looked at the NameSpaces…

They are version specific.

The one I want is root\Microsoft\SqlServer\ComputerManagement12.

But, the one referenced in the code is root\Microsoft\SqlServer\ComputerManagement10.

On this particular box I am on SQL Server v2014.

And, so ComputerManagement12 is SQL 2014.

While ComputerManagement10 is SQL Server 2008.

 

Code ( Revised )

Code revised for v2014.

 

OPTION EXPLICIT
On error resume next

Dim strComputer
Dim baseURL
Dim sqlServerVersion
Dim URL
Dim objWMIService
Dim colItems
Dim objItem
Dim query

strComputer = "."

baseURL="winmgmts:\\" & strComputer & "\root\Microsoft\SqlServer\"
rem comment out version information
rem sqlServerVersion="ComputerManagement10"
sqlServerVersion="ComputerManagement12"

URL=baseURL & sqlServerVersion

set objWMIService = GetObject(URL)

if (Err.Number <> 0) then

	Wscript.Echo "GetObject failed on " & URL
	Wscript.Echo "Error Number is " & CSTR(Err.Number)
	Wscript.Echo "Error Description is " & Err.Description

	Wscript.Quit
	
end if

query = "SELECT * FROM SecurityCertificate"

Set colItems = objWMIService.ExecQuery(query,,48)


if (Err.Number <> 0) then

	Wscript.Echo "ExecQuery failed on " & query
	Wscript.Echo "Error Number is " & CSTR(Err.Number)
	Wscript.Echo "Error Description is " & Err.Description

	Wscript.Quit
	
end if

For Each objItem in colItems

	Wscript.Echo ""

	Wscript.Echo "SecurityCertificate instance"
	Wscript.Echo ""
	Wscript.Echo "ExpirationDate: " & objItem.ExpirationDate
	Wscript.Echo "FriendlyName: " & objItem.FriendlyName
	Wscript.Echo "IssuedBy: " & objItem.IssuedBy
	Wscript.Echo "IssuedTo: " & objItem.IssuedTo
	Wscript.Echo "Name: " & objItem.Name
	Wscript.Echo "SHA: " & objItem.SHA
	Wscript.Echo "StartDate: " & objItem.StartDate
	Wscript.Echo "SystemStore: " & objItem.SystemStore


Next