Using WMI to replace SQL Server Configuration Manager

[andreasjordan]

I put some lines of code together to show how to work with the WMI class to replace SQL Server Configuration Manager.

This is part of my work on Get-DbaTcpPort and Set-DbaTcpPort and the main part of the new commands Get-DbaNetworkConfiguration and Set-DbaNetworkConfiguration.

I only use dbatools to get the needed libraries with the class Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer.
So even if you don't want to use dbatools, you can use most parts of the code.

$ErrorActionPreference = 'Stop'

Import-Module -Name dbatools

# The instance we want to work with, format like SqlInstance in Connect-DbaInstance
[DbaInstance]$instance = 'WIN10\SQL2017'
# We only need ComputerName and InstanceName for the query
$instance.ComputerName
$instance.InstanceName

# If we want standard dbatools output, then we need ComputerName, ServiceName and DomainInstanceName from the server SMO
$server = Connect-DbaInstance -SqlInstance $instance
$outputComputerName = $server.ComputerName
$outputInstanceName = $server.ServiceName
$outputSqlInstance  = $server.DomainInstanceName

# Or just take these from $instance
$outputComputerName = $instance.ComputerName
$outputInstanceName = $instance.InstanceName
$outputSqlInstance  = $instance.SqlFullName.Trim('[]')  # Not the DomainInstanceName if ComputerName is a full qualified name

# Create a new .NET object, should work to remote computer if user has the necessary rights
$wmiManagedComputer = [Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer]::new($instance.ComputerName)
# More PowerShell syntax than .NET syntax:
# $wmiManagedComputer = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer -ArgumentList $instance.ComputerName
# $wmiManagedComputer = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $instance.ComputerName

# Now test the connection and fill the object
try {
    $wmiManagedComputer.Initialize()
} catch {
    $errorMessage = $_.Exception.InnerException.Message
    Write-Warning -Message $errorMessage
    throw "Connection to $($instance.ComputerName) not possible"
}

# Comparing the GUI elements of the Sql Server Configuration Manager to the SMO:
# SQL Server Service: $wmiManagedComputer.Services
# SQL Server Network Configuration / Protocols for XYZ: $wmiManagedComputer.ServerInstances.Where({$_.Name -eq 'XYZ'}).ServerProtocols
# SQL Native Client 11.0 Configuration / Client Protocols: $wmiManagedComputer.ClientProtocols
# SQL Native Client 11.0 Configuration / Aliases: $wmiManagedComputer.ServerAliases

# We only work with the ServerProtocols here
$wmiServerProtocols = $wmiManagedComputer.ServerInstances.Where({$_.Name -eq $instance.InstanceName}).ServerProtocols

if ($null -eq $wmiServerProtocols) {
    throw "Instance $($instance.InstanceName) not found on $($instance.ComputerName)"
}


# Build the top view like in Sql Server Configuration Manager:
$outputTop = [PSCustomObject]@{
    ComputerName = $outputComputerName
    InstanceName = $outputInstanceName
    SqlInstance  = $outputSqlInstance
    SharedMemory = if ($wmiServerProtocols.Where({$_.Name -eq 'Sm'}).IsEnabled) { 'Enabled' } else { 'Disabled' }
    NamedPipes   = if ($wmiServerProtocols.Where({$_.Name -eq 'Np'}).IsEnabled) { 'Enabled' } else { 'Disabled' }
    TCPIP        = if ($wmiServerProtocols.Where({$_.Name -eq 'Tcp'}).IsEnabled) { 'Enabled' } else { 'Disabled' }
}

# SharedMemory can only be turned on or off, and I don't care about the Pipe Name of Named Pipes, so I will only work with TCP/IP.
# Some info about "[0]":
# The ".Where" always returns a collection, even if there is definitly only one element.
# This is no problem if we just want to query additional properties, but is a problem if we want to use methods.
# So here and in some lines further down, I will get the first (and only) element from the collection.
$wmiTcpIp = $wmiServerProtocols.Where({$_.Name -eq 'Tcp'})[0]
# If you later want to refresh, use this line:
# $wmiTcpIp.Refresh()

$outputTcpIpProtocol = [PSCustomObject]@{
    Enabled   = $wmiTcpIp.ProtocolProperties.Where({$_.Name -eq 'Enabled'}).Value
    KeepAlive = $wmiTcpIp.ProtocolProperties.Where({$_.Name -eq 'KeepAlive'}).Value
    ListenAll = $wmiTcpIp.ProtocolProperties.Where({$_.Name -eq 'ListenOnAllIPs'}).Value
}

$outputTcpIpIPAddressesIPn = foreach ($ip in $wmiTcpIp.IPAddresses.Where({$_.Name -ne 'IPAll'})) {
    [PSCustomObject]@{
        Name            = $ip.Name
        Active          = $ip.IPAddressProperties.Where({$_.Name -eq 'Active'}).Value
        Enabled         = $ip.IPAddressProperties.Where({$_.Name -eq 'Enabled'}).Value
        IPAddress       = $ip.IPAddressProperties.Where({$_.Name -eq 'IpAddress'}).Value
        TCPDynamicPorts = $ip.IPAddressProperties.Where({$_.Name -eq 'TcpDynamicPorts'}).Value
        TCPPort         = $ip.IPAddressProperties.Where({$_.Name -eq 'TcpPort'}).Value
    }
}

$ipAll = $wmiTcpIp.IPAddresses.Where({$_.Name -eq 'IPAll'})
$outputTcpIpIPAddressesIPAll = [PSCustomObject]@{
    Name            = $ipAll.Name
    TCPDynamicPorts = $ipAll.IPAddressProperties.Where({$_.Name -eq 'TcpDynamicPorts'}).Value
    TCPPort         = $ipAll.IPAddressProperties.Where({$_.Name -eq 'TcpPort'}).Value
}


# Option 1: Return all information, even if not used
$outputTcpIpIPAddresses = $outputTcpIpIPAddressesIPn + $outputTcpIpIPAddressesIPAll

# Option 2: Only return information that is used
if ($wmiTcpIp.ProtocolProperties.Where({$_.Name -eq 'Enabled'}).Value) {
    if ($wmiTcpIp.ProtocolProperties.Where({$_.Name -eq 'ListenOnAllIPs'}).Value) {
        $outputTcpIpIPAddresses = $outputTcpIpIPAddressesIPAll
    } else {
        $outputTcpIpIPAddresses = $outputTcpIpIPAddressesIPn
    }
} else {
    $outputTcpIpIPAddresses = $null
}

# Build the full output object with all details about TCP/IP
$outputFull = [PSCustomObject]@{
    ComputerName       = $outputComputerName
    InstanceName       = $outputInstanceName
    SqlInstance        = $outputSqlInstance
    SharedMemoryStatus = if ($wmiServerProtocols.Where({$_.Name -eq 'Sm'}).IsEnabled) { 'Enabled' } else { 'Disabled' }
    NamedPipesStatus   = if ($wmiServerProtocols.Where({$_.Name -eq 'Np'}).IsEnabled) { 'Enabled' } else { 'Disabled' }
    TCPIPStatus        = if ($wmiServerProtocols.Where({$_.Name -eq 'Tcp'}).IsEnabled) { 'Enabled' } else { 'Disabled' }
    TCPIPProtokoll     = $outputTcpIpProtocol
    TCPIPIPAddresses   = $outputTcpIpIPAddresses
}

# Format output as JSON to get a better overview
$outputFull | ConvertTo-Json



# Changing information


# Enable or disable a protokoll

# Choose on of the following three:
$protokollDisplayName = 'Shared Memory'
$protokollDisplayName = 'Named Pipes'
$protokollDisplayName = 'TCP/IP'

$protokollName = switch ($protokollDisplayName) {
    'Shared Memory' { 'Sm' }
    'Named Pipes' { 'Np' }
    'TCP/IP' { 'Tcp' }
}

# Choose an action:
$isEnabledTarget = $true   # to enable
$isEnabledTarget = $false  # to disable

# Get the current status
$isEnabledCurrent = $wmiServerProtocols.Where({$_.Name -eq $protokollName}).IsEnabled

# Change the status if needed
if ($isEnabledCurrent -eq $isEnabledTarget) {
    "Nothing to do, protocol $protokollDisplayName is in requested isEnabled status of $isEnabledCurrent"
} else {
    $wmiServerProtocols.Where({$_.Name -eq $protokollName})[0].IsEnabled = $isEnabledTarget
    $wmiServerProtocols.Where({$_.Name -eq $protokollName})[0].Alter()
    "Changed isEnabled status of protocol $protokollDisplayName from $isEnabledCurrent to $isEnabledTarget"
}



# Changing from dynamic to static port for a ListenOnAllIPs configuration

# Documentation: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-a-server-to-listen-on-a-specific-tcp-port

# Note that the port is not a number but a sting. Documentation says: "Multiple ports may be specified by separating them with a comma."
# Type of TcpPort is String: $wmiTcpIp.IPAddresses.Where({$_.Name -eq 'IPAll'}).IPAddressProperties.Where({$_.Name -eq 'TcpPort'}).Type

# Set the target port:
$portTarget = '14331, 14332, 14333, 14334, 14335'

# Test for ListenOnAllIPs
if (-not $wmiTcpIp.ProtocolProperties.Where({$_.Name -eq 'ListenOnAllIPs'}).Value) {
    throw 'Listen All is set to No'
}

# Get the current value
$portCurrent = $wmiTcpIp.IPAddresses.Where({$_.Name -eq 'IPAll'}).IPAddressProperties.Where({$_.Name -eq 'TcpPort'}).Value

# Change port if needed
if ($portCurrent -eq $portTarget) {
    "Nothing to do, port has requested value of $portTarget"
} else {
    $wmiTcpIp.IPAddresses.Where({$_.Name -eq 'IPAll'}).IPAddressProperties.Where({$_.Name -eq 'TcpDynamicPorts'})[0].Value = ''
    $wmiTcpIp.IPAddresses.Where({$_.Name -eq 'IPAll'}).IPAddressProperties.Where({$_.Name -eq 'TcpPort'})[0].Value = $portTarget
    $wmiTcpIp.Alter()
    "Changed port from $portCurrent to $portTarget"
}



# Changing from a ListenOnAllIPs configuration to listening on one or more IP Adresses only for a static port

# Set the target port:
$portTarget = '14331, 14332, 14333, 14334, 14335'

# Set the target IP Address(es):
$ipTarget = '192.168.3.100'

$listenOnAllIPsTarget = $false

# Get the current state
$listenOnAllIPsCurrent = $wmiTcpIp.ProtocolProperties.Where({$_.Name -eq 'ListenOnAllIPs'}).Value

# Change configuration if needed
if ($listenOnAllIPsCurrent -eq $listenOnAllIPsTarget) {
    "Nothing to do"
} else {
    $wmiTcpIp.ProtocolProperties.Where({$_.Name -eq 'ListenOnAllIPs'})[0].Value = $listenOnAllIPsTarget
    $wmiTcpIp.IPAddresses.Where({$_.Name -eq 'IPAll'}).IPAddressProperties.Where({$_.Name -eq 'TcpDynamicPorts'})[0].Value = ''
    $wmiTcpIp.IPAddresses.Where({$_.Name -eq 'IPAll'}).IPAddressProperties.Where({$_.Name -eq 'TcpPort'})[0].Value = ''
    foreach ($ip in $ipTarget) {
        # $ip = $ipTarget | Select-Object -First 1
        $wmiIp = $wmiTcpIp.IPAddresses.Where({$_.IPAddress -eq $ip})[0]
        if ($null -eq $wmiIp) {
            Write-Warning -Message "IP $ip was not found. Skipping."
        } else {
            if (-not $wmiIp.IPAddressProperties.Where({$_.Name -eq 'Active'}).Value) {
                Write-Warning -Message "IP $ip is not active. Skipping."
                # TODO: When does this happen?
            } else {
                $wmiIp.IPAddressProperties.Where({$_.Name -eq 'Enabled'})[0].Value = $true
                $wmiIp.IPAddressProperties.Where({$_.Name -eq 'TcpDynamicPorts'})[0].Value = ''
                $wmiIp.IPAddressProperties.Where({$_.Name -eq 'TcpPort'})[0].Value = $portTarget
            }
        }
    }
    $wmiTcpIp.Alter()
    "Changed configuration"
}


# After every change:

# Option 1: Restart the service with wmi
$service = $wmiManagedComputer.Services.Where({$_.DisplayName -eq "SQL Server ($($instance.InstanceName))"})[0]
$service.Stop()
$service.Start()

# Option 2: Restart the service with dbatools 
$null = Restart-DbaService -ComputerName $instance.ComputerName -InstanceName $instance.InstanceName -Force