Get a list of instances and and port numbers running on a server

Question

Been looking at some code solutions that will derive a list of SQL instances running on a server. The script will run on a server, and return the servername and the port number of SQL instances running on the server. Let us assume the script is running on a server called Server141, the SQL instance is running on port 4455. I will like it to return Server141,4455

I have tried the following to no avail.

<#
.SYNOPSIS
Shows the Instances and the Port Numbers on a SQL Server

.DESCRIPTION
This function will show the Instances and the Port Numbers on a SQL Server using WMI

.PARAMETER Server
The Server Name

.EXAMPLE
Get-SQLInstancesPort Fade2Black

This will display the instances and the port numbers on the server Fade2Black
.NOTES
AUTHOR: Rob Sewell sqldbawithabeard.com
DATE: 22/04/2015
#>

function Get-SQLInstancesPort {

    param ([string]$Server)

    [system.reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")|Out-Null
    [system.reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")|Out-Null
    $mc = new-object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $Server
    $Instances = $mc.ServerInstances

    foreach ($Instance in $Instances) {
        $port = @{Name = "Port"; Expression = {$_.ServerProtocols['Tcp'].IPAddresses['IPAll'].IPAddressProperties['TcpPort'].Value}}
        $Parent = @{Name = "Parent"; Expression = {$_.Parent.Name}}
        $Instance|Select $Parent, Name, $Port
    }
}

Complained about cannot find type [Microsoft.SQLServer.Management.Smo.Wmi.ManagedComputer]

I also tried

#input file name
$Inputfilename='c:\server123.csv'
#An array to hold the output values  
$Results=@() 
 #the servers are listed. Make WMI service is enabled on all the target machine  
#Import the server list - Read the servers from the c:\server123.csv file.  
Import-Csv $Inputfilename |% {  
$namespace = Get-WmiObject -ComputerName $_.server -Namespace "root\microsoft\sqlserver" -Class "_Namespace" -Filter "name like 'ComputerManagement%'" | sort desc | select -ExpandProperty name -First 1 
$port=Get-WmiObject -ComputerName $_.server -Namespace "root\microsoft\SqlServer\$namespace" -Class ServerNetworkProtocolProperty | select instancename,propertystrval,PropertyName,IPAddressName,ProtocolName |   where{$_.IPAddressName -eq 'IPAll' -and $_.propertystrval -ne ''}  foreach ($p in $port)  
   {  
     $Properties = @{      
                     Servername = $_.Server      
                     Instancename = $p.instanceName 
                     PropertyName = $p.PropertyName
                     port=$p.propertystrval  
                 }  
$Results += New-Object psobject -Property $properties  
   }  
}  
$Results |select ServerName,InstanceName,PropertyName,Port |Format-Table -AutoSize

This had WMI issues too.

Looked on the site https://www.mssqltips.com/sqlservertip/3542/automated-way-to-get-all-port-information-for-sql-server-instances/

Solution works, however I need this to be a function that I can call that will return servername, portnumber as I will then use this information which will be stored in an array to loop and connect to the instances.

# If there is a valid instance name, proceed farther
    if ($InstanceName.length -gt 0) {

        # Navigate the child keys 
        foreach ($key in Get-ChildItem -path $_.pspath){

            # Find entries belonging to actual database engine instances
            if ($key.name -like "*MSSQLServer*")
            {

                # Navigate to the key where the TCP settings are stored
                Set-Location -path $key.pspath;
                cd .\SuperSocketNetLib\tcp -ErrorAction SilentlyContinue;

                # Ensure we're only reporting against the right keys

                $TCPKey = Get-Location;

                if ($TCPKey -like '*SuperSocketNetLib*') {

                    $TCPProps = Get-ItemProperty $TCPKey;

                    # Find out if TCP is enabled
                    $Enabled = $TCPProps.psobject.Properties["enabled"].value;

                    # Begin the reporting
                    Write-Host "Instance Name: $InstanceName";
                    Write-Host "------------------------------------------------------------------------------";

                    # If TCP is not enabled, there's point finding all the ports. Therefore, we check.
                    if ($Enabled -eq 1)
                    {
                        foreach ($Key in gci $TCPKey)
                        {
                            $IPprops = Get-ItemProperty $Key.pspath;
                            $IPAddress = $IPProps.psobject.Properties["IpAddress"].Value;

                            # For the Key IPAll, there is no IPAddress value. therefore, we trap for it.
                            if ($IPAddress -eq $null)
                            {
                                $IPAddress = "All"
                            }

                            Write-Host "  IP Address: $IPAddress";
                            Write-Host "    Dyn. Ports: ", $IPProps.psobject.Properties["TcpDynamicPorts"].Value;
                            Write-Host "    Sta. Ports: ", $IPProps.psobject.Properties["TcpPort"].Value;

                        }
                    } else {
                        Write-Host "  TCP not enabled."
                    }

                    Write-Host "------------------------------------------------------------------------------";
                    Write-Host "";
                }
            }
        }
    }  
}

# Return to original location
Pop-Location;

Answer 1

Hi，

Please see if installing extensions for PowerShell helps.

https://blog.sqlserveronline.com/2018/08/08/powershell-sql-server-cannot-find-type-microsoft-sqlserver-management-smo-server/
Please note: Information posted in the given link is hosted by a third party. Microsoft does not guarantee the accuracy and effectiveness of information.

Best Regards,
Ian Xue

============================================

If the Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I have loaded SQLPS module and still no luck.

Answer 3

Late answer, but have been using the below - similar to what you had:

clear-host  
  
$Results = @()  
$namespace = Get-WmiObject -Namespace 'root/Microsoft/SqlServer' -Class '__NameSpace'|Where-Object {$_.Name -like "ComputerManagement*"} | sort desc | select -ExpandProperty name -First 1   
$port = Get-WmiObject -Namespace "root\microsoft\SqlServer\$namespace" -Class 'ServerNetworkProtocolProperty' | select * |   where{$_.IPAddressName -eq 'IPAll' -and $_.propertystrval -ne ''}    
$Results += foreach ($p in $port){  
    $version = switch($p.__NAMESPACE){  
        root\Microsoft\SqlServer\ComputerManagement11 {"SQL 2012"}  
        root\Microsoft\SqlServer\ComputerManagement12 {"SQL 2014"}  
        root\Microsoft\SqlServer\ComputerManagement13 {"SQL 2016"}  
        root\Microsoft\SqlServer\ComputerManagement14 {"SQL 2017"}  
        root\Microsoft\SqlServer\ComputerManagement15 {"SQL 2019"}  
    }  
      
    [pscustomobject][ordered]@{  
        Servername = $env:COMPUTERNAME        
        Instancename = $p.instanceName   
        PropertyName = $p.PropertyName  
        port=$p.propertystrval    
        ver=$version  
    }    
}  
 $Results | Format-Table -AutoSize