Gutcheck

0.11.1

Private/Kinds/AppConnection.ps1

# The AppConnection Kind: the database server and the web service an application is
# configured to talk to, read out of the application's own configuration.
#
# A client-server business application that is slow or will not start is, more often than
# not, waiting for its SQL Server. Which Server that is differs per Customer, so no Check
# Definition can name it - but the application's configuration file does, on every machine
# it is installed on. A Check Definition names the file and where in it the Server stands;
# this Kind reads that and measures what it finds.
#
# Built for, and observed on 2026-10-09 with, a .NET application that keeps one
# <ConnectionFileItem> per connection in an XML file under %APPDATA%, each with a Server, a
# Database and a WebServiceAddress - and with a Username and a Password beside them.
#
# Credentials. This Kind never reads one. From an XML file it takes the nodes a Check
# Definition names and no others; from a connection string only the keys that name the
# Server and the database (ConvertFrom-AppConnectionString). Nothing it does needs to sign
# in: what it asks a SQL Server is what the server tells anybody who reaches its port.
#
# What is measured:
#
# - Reachability, by the Server Kind and judged by it (name resolution, TCP connects,
# ping), the way the Odbc Kind hands over the Servers it finds. A web service address
# is a host and a port like any other.
# - The SQL Server's pre-login answer. The first packet of a TDS connection is answered
# before any sign-in with the server's version and whether it encrypts
# ([MS-TDS] 2.2.6.5 PRELOGIN). Observed: a SQL Server 2019 answered "15.0.x" and
# encryption option 0 in 31 ms. A port that is open and does not answer this is not a
# SQL Server, or one that is too busy to talk - either is worth knowing.
# - For a named instance without a port, the port the SQL Server Browser gives out on
# UDP 1434 ([MC-SQLR] CLNT_UCAST_INST), which is how every client finds it.

# Which keys of a connection string name the Server and the database. Every other key is
# dropped unread - among them the ones that carry credentials.
$script:AppConnectionServerKeys   = @('data source', 'server', 'address', 'addr', 'network address')
$script:AppConnectionDatabaseKeys = @('initial catalog', 'database')

function ConvertFrom-AppConnectionString {
    <#
    .SYNOPSIS
        The Server and the database a connection string names, and nothing else of it. Pure.
    #>
    [CmdletBinding()]
    [OutputType([psobject])]
    param([AllowNull()][AllowEmptyString()][string]$ConnectionString)

    $server = $null
    $database = $null
    foreach ($pair in ("$ConnectionString" -split ';')) {
        $parts = $pair -split '=', 2
        if ($parts.Count -lt 2) { continue }
        $key   = $parts[0].Trim().ToLowerInvariant()
        $value = $parts[1].Trim().Trim('"').Trim("'").Trim()
        if (-not $value) { continue }
        if (-not $server -and $script:AppConnectionServerKeys -contains $key)       { $server = $value }
        if (-not $database -and $script:AppConnectionDatabaseKeys -contains $key)   { $database = $value }
    }
    if (-not $server -and -not $database) { return }
    [pscustomobject]@{ Server = $server; Database = $database }
}

function ConvertTo-AppConnectionUrlTarget {
    <#
    .SYNOPSIS
        A web service address as a host and a port. Pure; nothing for what is not one.
    #>
    [CmdletBinding()]
    [OutputType([psobject])]
    param([AllowNull()][AllowEmptyString()][string]$Url)

    $uri = $null
    if (-not [uri]::TryCreate("$Url".Trim(), [UriKind]::Absolute, [ref]$uri)) { return }
    if ($uri.Scheme -notin 'http', 'https' -or -not $uri.Host) { return }
    if ($uri.IsLoopback) { return }
    [pscustomobject]@{
        Url      = "$Url".Trim()
        Scheme   = $uri.Scheme
        HostName = $uri.Host
        Port     = $uri.Port
        Entry    = '{0}:{1}' -f $uri.Host, $uri.Port
    }
}

function Read-AppConnectionNode {
    <#
    .SYNOPSIS
        The text of the one node an XPath names below an item, or $null.
    .DESCRIPTION
        An empty XPath is a field the Check Definition did not name, and is not read.
    #>
    [CmdletBinding()]
    [OutputType([string])]
    param([Parameter(Mandatory)]$Item, [AllowNull()][AllowEmptyString()][string]$XPath)

    if (-not "$XPath".Trim()) { return $null }
    $node = $null
    try { $node = $Item.SelectSingleNode($XPath) } catch { return $null }
    if ($null -eq $node) { return $null }
    $text = "$($node.InnerText)".Trim()
    if ($node -is [System.Xml.XmlAttribute]) { $text = "$($node.Value)".Trim() }
    if ($text) { $text } else { $null }
}

function ConvertFrom-AppConnectionXml {
    <#
    .SYNOPSIS
        The connections one XML document holds, as the Check Definition says to read it. Pure.
    .DESCRIPTION
        -Source names the items (Item, an XPath) and, relative to each, where its name,
        Server, database, web service address and default flag stand - or a connection
        string, of which only the Server and the database are taken. An item that names
        neither a Server nor an address is not a connection and is left out.
    #>
    [CmdletBinding()]
    [OutputType([psobject])]
    param([Parameter(Mandatory)][xml]$Document, [AllowNull()]$Source)

    $itemPath = "$(Get-DataProperty $Source 'Item')".Trim()
    if (-not $itemPath) { $itemPath = '/*' }
    $items = @()
    try { $items = @($Document.SelectNodes($itemPath)) } catch { return }

    foreach ($item in $items) {
        $server   = Read-AppConnectionNode -Item $item -XPath (Get-DataProperty $Source 'Server')
        $database = Read-AppConnectionNode -Item $item -XPath (Get-DataProperty $Source 'Database')
        $string   = Read-AppConnectionNode -Item $item -XPath (Get-DataProperty $Source 'ConnectionString')
        if ($string) {
            $parsed = ConvertFrom-AppConnectionString -ConnectionString $string
            # Not kept: the string is where the password is.
            $string = $null
            if (-not $server)   { $server = Get-DataProperty $parsed 'Server' }
            if (-not $database) { $database = Get-DataProperty $parsed 'Database' }
        }
        $url = Read-AppConnectionNode -Item $item -XPath (Get-DataProperty $Source 'Url')
        if (-not $server -and -not $url) { continue }

        $default = "$(Read-AppConnectionNode -Item $item -XPath (Get-DataProperty $Source 'Default'))"
        [pscustomobject]@{
            Name      = Read-AppConnectionNode -Item $item -XPath (Get-DataProperty $Source 'Name')
            Server    = $server
            Database  = $database
            Url       = $url
            IsDefault = [bool]($default -match '^(true|1|yes)$')
        }
    }
}

function Get-AppConnectionSqlBrowserPort {
    <#
    .SYNOPSIS
        The TCP port the SQL Server Browser names for an instance, or $null.
    .DESCRIPTION
        One UDP datagram to port 1434: 0x04 and the instance name. The answer is text,
        "...;tcp;<port>;...". No answer is the usual case behind a firewall, and is $null.
    #>
    [CmdletBinding()]
    [OutputType([int])]
    param([Parameter(Mandatory)][string]$HostName, [Parameter(Mandatory)][string]$Instance, [int]$TimeoutMs = 2000)

    $udp = New-Object System.Net.Sockets.UdpClient
    try {
        $udp.Client.ReceiveTimeout = $TimeoutMs
        $request = [byte[]](@(4) + [Text.Encoding]::ASCII.GetBytes($Instance) + @(0))
        $null = $udp.Send($request, $request.Length, $HostName, 1434)
        $from = New-Object System.Net.IPEndPoint ([System.Net.IPAddress]::Any), 0
        $answer = $udp.Receive([ref]$from)
        ConvertFrom-AppConnectionBrowserAnswer -Text ([Text.Encoding]::ASCII.GetString($answer))
    }
    catch { $null }
    finally { $udp.Close() }
}

function ConvertFrom-AppConnectionBrowserAnswer {
    <#
    .SYNOPSIS
        The TCP port out of a SQL Server Browser answer, or $null. Pure.
    #>
    [CmdletBinding()]
    [OutputType([int])]
    param([AllowNull()][AllowEmptyString()][string]$Text)

    if ("$Text" -match '(?i);tcp;(\d{1,5})(;|$)') {
        $port = [int]$Matches[1]
        if ($port -ge 1 -and $port -le 65535) { return $port }
    }
    $null
}

function ConvertFrom-AppConnectionPreLogin {
    <#
    .SYNOPSIS
        The version and the encryption option out of a TDS pre-login answer. Pure.
    .DESCRIPTION
        The answer is a TDS packet (8 bytes of header), then a table of options - token,
        offset, length, five bytes each, ended by 0xFF - and then their data, the offsets
        counted from the end of the header. Token 0 is the version: major, minor, and the
        build as two bytes, high first. Token 1 is one byte: 0 off (the sign-in alone is
        encrypted), 1 on, 2 not supported, 3 required. Nothing for bytes that are not that.
    #>
    [CmdletBinding()]
    [OutputType([psobject])]
    param([AllowNull()][byte[]]$Bytes)

    if ($null -eq $Bytes -or $Bytes.Length -lt 14 -or $Bytes[0] -ne 4) { return }
    $version = $null
    $encryption = $null
    $position = 8
    while ($position -lt $Bytes.Length -and $Bytes[$position] -ne 0xFF) {
        if ($position + 4 -ge $Bytes.Length) { return }
        $token  = $Bytes[$position]
        $offset = 8 + ($Bytes[$position + 1] * 256) + $Bytes[$position + 2]
        $length = ($Bytes[$position + 3] * 256) + $Bytes[$position + 4]
        if ($offset + $length -gt $Bytes.Length) { return }
        if ($token -eq 0 -and $length -ge 4) {
            $version = '{0}.{1}.{2}' -f $Bytes[$offset], $Bytes[$offset + 1], (($Bytes[$offset + 2] * 256) + $Bytes[$offset + 3])
        }
        if ($token -eq 1 -and $length -ge 1) { $encryption = [int]$Bytes[$offset] }
        $position += 5
    }
    if (-not $version) { return }
    [pscustomobject]@{ Version = $version; Encryption = $encryption }
}

function Get-AppConnectionPreLogin {
    <#
    .SYNOPSIS
        Asks a SQL Server who it is, without signing in. Never throws.
    .DESCRIPTION
        Sends the pre-login packet every client sends first - version, encryption "off",
        no instance, thread 0, no MARS - and reads the one packet that answers it.
    #>
    [CmdletBinding()]
    [OutputType([psobject])]
    param([Parameter(Mandatory)][string]$HostName, [Parameter(Mandatory)][int]$Port, [int]$TimeoutMs = 3000)

    $result = [ordered]@{ Answered = $false; Version = $null; Encryption = $null; Milliseconds = $null; Error = $null }
    $client = New-Object System.Net.Sockets.TcpClient
    try {
        $watch = [Diagnostics.Stopwatch]::StartNew()
        if (-not ($client.ConnectAsync($HostName, $Port).Wait($TimeoutMs) -and $client.Connected)) {
            $result.Error = 'no connection'
            return [pscustomobject]$result
        }
        $stream = $client.GetStream()
        $stream.ReadTimeout  = $TimeoutMs
        $stream.WriteTimeout = $TimeoutMs

        # Five options and the terminator are 26 bytes, so the data begins at offset 26.
        $options = [byte[]](0,0,26,0,6,  1,0,32,0,1,  2,0,33,0,1,  3,0,34,0,4,  4,0,38,0,1,  0xFF)
        $data    = [byte[]](9,0,0,0,0,0,  0,  0,  0,0,0,0,  0)
        $length  = 8 + $options.Length + $data.Length
        $packet  = [byte[]](@(0x12, 1, [byte]($length -shr 8), [byte]($length -band 255), 0, 0, 1, 0) + $options + $data)
        $stream.Write($packet, 0, $packet.Length)

        $buffer = New-Object byte[] 512
        $read   = $stream.Read($buffer, 0, $buffer.Length)
        $watch.Stop()
        $answer = $null
        if ($read -gt 0) { $answer = ConvertFrom-AppConnectionPreLogin -Bytes ([byte[]]$buffer[0..($read - 1)]) }
        if ($answer) {
            $result.Answered     = $true
            $result.Version      = $answer.Version
            $result.Encryption   = $answer.Encryption
            $result.Milliseconds = [math]::Round($watch.Elapsed.TotalMilliseconds, 1)
        }
        else { $result.Error = 'not a pre-login answer' }
    }
    catch { $result.Error = $_.Exception.GetBaseException().Message }
    finally { $client.Dispose() }
    [pscustomobject]$result
}

function Get-AppConnectionData {
    [CmdletBinding()]
    [OutputType([psobject])]
    param([hashtable]$Parameters = @{})

    $sources     = New-Object 'System.Collections.Generic.List[psobject]'
    $connections = New-Object 'System.Collections.Generic.List[psobject]'

    foreach ($source in @(Get-Parameter $Parameters 'Sources' @())) {
        $configured = "$(Get-DataProperty $source 'Path')".Trim()
        if (-not $configured) { continue }
        $path   = [Environment]::ExpandEnvironmentVariables($configured)
        $exists = Test-Path -LiteralPath $path -PathType Leaf
        $failure = $null
        $found   = @()
        if ($exists) {
            try {
                $document = New-Object System.Xml.XmlDocument
                # No DTD, no external entity: the file is the application's, not ours.
                $document.XmlResolver = $null
                $document.Load($path)
                $found = @(ConvertFrom-AppConnectionXml -Document $document -Source $source)
            }
            catch { $failure = $_.Exception.GetBaseException().Message }
        }
        foreach ($connection in $found) {
            $connection | Add-Member -NotePropertyName 'Source' -NotePropertyValue $path -Force
            $connections.Add($connection)
        }
        $sources.Add([pscustomobject]@{
            ConfiguredPath = $configured; Path = $path; Exists = [bool]$exists; Error = $failure; Connections = $found.Count
        })
    }

    # Every SQL Server once, however many connections name it.
    $sqlServers = New-Object 'System.Collections.Generic.List[psobject]'
    # One Server entry per host, with every port: a SQL Server and a web service on one
    # machine are one name to resolve and one host to ping, however the file spells it.
    $hosts      = New-Object 'System.Collections.Generic.List[string]'
    $ports      = @{}
    $addTarget  = {
        param([string]$HostName, $Port)
        $key = $HostName.ToLowerInvariant()
        if (-not $ports.ContainsKey($key)) { $hosts.Add($HostName); $ports[$key] = New-Object 'System.Collections.Generic.List[int]' }
        if ($Port -and -not $ports[$key].Contains([int]$Port)) { $ports[$key].Add([int]$Port) }
    }
    $seen       = @{}
    foreach ($connection in $connections) {
        $target = ConvertTo-OdbcServerTarget -Server (Get-DataProperty $connection 'Server')
        if (-not $target) { continue }
        $key = ('{0}\{1},{2}' -f $target.HostName, $target.Instance, $target.Port).ToLowerInvariant()
        if ($seen.ContainsKey($key)) { continue }
        $seen[$key] = $true

        $port = $null
        $portFrom = $null
        if ($target.Port)             { $port = [int]$target.Port; $portFrom = 'Given' }
        elseif (-not $target.Instance) { $port = $script:OdbcDefaultSqlPort; $portFrom = 'Default' }
        else {
            $port = Get-AppConnectionSqlBrowserPort -HostName $target.HostName -Instance $target.Instance
            if ($port) { $portFrom = 'Browser' }
        }

        $preLogin = $null
        if ($port) { $preLogin = Get-AppConnectionPreLogin -HostName $target.HostName -Port $port }
        & $addTarget $target.HostName $port

        $sqlServers.Add([pscustomobject]@{
            HostName  = $target.HostName
            Instance  = $target.Instance
            Port      = $port
            PortFrom  = $portFrom
            Databases = @($connections | Where-Object { "$(Get-DataProperty $_ 'Server')" -eq "$(Get-DataProperty $connection 'Server')" } |
                ForEach-Object { Get-DataProperty $_ 'Database' } | Where-Object { $_ } | Select-Object -Unique)
            PreLogin  = $preLogin
        })
    }

    $urls = @($connections | ForEach-Object { ConvertTo-AppConnectionUrlTarget -Url (Get-DataProperty $_ 'Url') } | Where-Object { $_ })
    foreach ($url in $urls) { & $addTarget $url.HostName $url.Port }
    $entries = @(foreach ($name in $hosts) {
        $list = $ports[$name.ToLowerInvariant()]
        if ($list.Count) { '{0}:{1}' -f $name, ($list -join ',') } else { $name }
    })

    # Reachability is the Server Kind's, as for the Servers the Odbc Kind finds.
    $server = $null
    if ($entries.Count) { $server = Get-ServerData -Parameters @{ Servers = @($entries) } }

    [pscustomobject]@{
        PSTypeName  = 'Gutcheck.Data.AppConnection'
        App         = "$(Get-Parameter $Parameters 'App' '')"
        Configured  = [bool]@(Get-Parameter $Parameters 'Sources' @()).Count
        Sources     = @($sources)
        Connections = @($connections)
        SqlServers  = @($sqlServers)
        Urls        = @($urls | ForEach-Object { $_.Url } | Select-Object -Unique)
        Server      = $server
    }
}

function Get-AppConnectionSqlProduct {
    <#
    .SYNOPSIS
        The name a SQL Server version is sold under: "15.0.4420" is SQL Server 2019. Pure.
    #>
    [CmdletBinding()]
    [OutputType([string])]
    param([AllowNull()][AllowEmptyString()][string]$Version)

    $parts = "$Version" -split '\.'
    $name = switch ($parts[0]) {
        '17' { '2025' }
        '16' { '2022' }
        '15' { '2019' }
        '14' { '2017' }
        '13' { '2016' }
        '12' { '2014' }
        '11' { '2012' }
        '10' { if ($parts.Count -gt 1 -and $parts[1] -eq '50') { '2008 R2' } else { '2008' } }
        '9'  { '2005' }
        default { '' }
    }
    if ($name) { return 'SQL Server {0}' -f $name }
    'SQL Server'
}

function Get-AppConnectionSqlVersionText {
    <#
    .SYNOPSIS
        A SQL Server version with the release it is: "SQL Server 2019 (Version 15.0.4420)".
        Nothing where no version was read. Pure.
    #>
    [CmdletBinding()]
    [OutputType([string])]
    param([AllowNull()][AllowEmptyString()][string]$Version)

    if (-not "$Version".Trim()) { return '' }
    (Get-Text 'Value.AppConnection.SqlVersion') -f (Get-AppConnectionSqlProduct -Version $Version), $Version
}

function ConvertTo-AppConnectionFinding {
    [CmdletBinding()]
    [OutputType([psobject])]
    param(
        [AllowNull()]$Data,
        [hashtable]$Parameters = @{},
        [AllowNull()]$Situation
    )

    $check = Get-Text 'Check.AppConnection.Connections'
    if (-not (Get-DataProperty $Data 'Configured')) {
        return New-Finding -Category Apps -Check $check -Severity INFO `
            -Value (Get-Text 'Value.AppConnection.NotConfigured') -Hint (Get-Text 'Hint.AppConnection.NotConfigured')
    }

    $sources = Get-DataCollection $Data 'Sources'
    foreach ($source in $sources) {
        $path = Get-DataProperty $source 'Path'
        if (Get-DataProperty $source 'Error') {
            New-Finding -Category Apps -Check $check -Severity INFO `
                -Value ((Get-Text 'Value.AppConnection.Unreadable') -f $path, (Get-DataProperty $source 'Error')) `
                -Hint (Get-Text 'Hint.AppConnection.Unreadable')
        }
        elseif (-not (Get-DataProperty $source 'Exists')) {
            New-Finding -Category Apps -Check $check -Severity INFO `
                -Value ((Get-Text 'Value.AppConnection.NoFile') -f $path) -Hint (Get-Text 'Hint.AppConnection.NoFile')
        }
    }

    $connections = Get-DataCollection $Data 'Connections'
    if (-not $connections.Count) {
        if (@($sources | Where-Object { (Get-DataProperty $_ 'Exists') -and -not (Get-DataProperty $_ 'Error') }).Count) {
            New-Finding -Category Apps -Check $check -Severity INFO `
                -Value (Get-Text 'Value.AppConnection.NoneFound') -Hint (Get-Text 'Hint.AppConnection.NoneFound')
        }
        return
    }

    # What is configured, in one line: a Technician should see the Server's name without
    # opening the evidence.
    $named = @($connections | ForEach-Object {
        $target = "$(Get-DataProperty $_ 'Server')"
        if (-not $target) { $target = "$(Get-DataProperty $_ 'Url')" }
        if (Get-DataProperty $_ 'Database') { $target = (Get-Text 'Value.AppConnection.ServerDatabase') -f $target, (Get-DataProperty $_ 'Database') }
        $target
    } | Select-Object -Unique)
    New-Finding -Category Apps -Check $check -Severity INFO -Value ('{0}: {1}' -f $named.Count, ($named -join ', '))

    foreach ($sql in (Get-DataCollection $Data 'SqlServers')) { New-AppConnectionSqlFinding -SqlServer $sql -Data $Data -Parameters $Parameters }

    $server = Get-DataProperty $Data 'Server'
    if ($server) { ConvertTo-ServerFinding -Data $server -Parameters $Parameters -Situation $Situation }
}

function New-AppConnectionSqlFinding {
    <#
    .SYNOPSIS
        What one SQL Server said about itself. Pure.
    .DESCRIPTION
        Nothing where the port did not open: that is the Server Kind's Finding, and a
        second one would say the same thing twice. A port that opened and gave no
        pre-login answer is WARN. A version older than SqlMinimumSupportedMajor is WARN:
        Microsoft no longer fixes it. That a server does not force encryption is said and
        not judged - inside one building it is the rule.
    #>
    [CmdletBinding()]
    [OutputType([psobject])]
    param([Parameter(Mandatory)]$SqlServer, [AllowNull()]$Data, [hashtable]$Parameters = @{})

    $minimum = ConvertTo-Number (Get-Parameter $Parameters 'SqlMinimumSupportedMajor' 14)
    $slowWarn = Get-Parameter $Parameters 'SqlAnswerWarnMs' 500
    $slowFail = Get-Parameter $Parameters 'SqlAnswerFailMs' ([double]::MaxValue)

    $hostName = "$(Get-DataProperty $SqlServer 'HostName')"
    $instance = "$(Get-DataProperty $SqlServer 'Instance')"
    $name = $hostName
    if ($instance) { $name = '{0}\{1}' -f $hostName, $instance }
    $check = (Get-Text 'Check.AppConnection.SqlServer') -f $name

    $port = ConvertTo-Number (Get-DataProperty $SqlServer 'Port')
    if ($null -eq $port) {
        return New-Finding -Category Apps -Check $check -Severity WARN `
            -Value (Get-Text 'Value.AppConnection.BrowserSilent') -Hint (Get-Text 'Hint.AppConnection.BrowserSilent')
    }

    $preLogin = Get-DataProperty $SqlServer 'PreLogin'
    if (-not (Get-DataProperty $preLogin 'Answered')) {
        # Whether the port opened at all is in what the Server Kind measured.
        $opened = @((Get-DataCollection (Get-DataProperty $Data 'Server') 'Servers') | Where-Object {
            "$(Get-DataProperty $_ 'HostName')" -eq $hostName -and
            @((Get-DataCollection $_ 'PortResults') | Where-Object { (ConvertTo-Number $_.Port) -eq $port -and (ConvertTo-Number $_.Successes) -gt 0 }).Count
        }).Count
        if (-not $opened) { return }
        return New-Finding -Category Apps -Check $check -Severity WARN `
            -Value ((Get-Text 'Value.AppConnection.NoPreLogin') -f $port) -Hint (Get-Text 'Hint.AppConnection.NoPreLogin')
    }

    $version = "$(Get-DataProperty $preLogin 'Version')"
    $lines = @((Get-Text 'Value.AppConnection.SqlVersion') -f (Get-AppConnectionSqlProduct -Version $version), $version)
    if ("$(Get-DataProperty $SqlServer 'PortFrom')" -eq 'Browser') { $lines += (Get-Text 'Value.AppConnection.PortFromBrowser') -f $port }
    $databases = Get-DataCollection $SqlServer 'Databases'
    if ($databases.Count) { $lines += (Get-Text 'Value.AppConnection.Databases') -f ($databases -join ', ') }
    $lines += switch (ConvertTo-Number (Get-DataProperty $preLogin 'Encryption')) {
        0       { Get-Text 'Value.AppConnection.EncryptionOff' }
        1       { Get-Text 'Value.AppConnection.EncryptionOn' }
        2       { Get-Text 'Value.AppConnection.EncryptionNone' }
        3       { Get-Text 'Value.AppConnection.EncryptionRequired' }
        default { Get-Text 'Value.AppConnection.EncryptionUnknown' }
    }
    $ms = ConvertTo-Number (Get-DataProperty $preLogin 'Milliseconds')
    $slow = 'OK'
    if ($null -ne $ms) {
        $lines += (Get-Text 'Value.AppConnection.AnsweredIn') -f $ms
        $slow = Get-Severity $ms $slowWarn $slowFail
    }

    $old = 'OK'
    $hints = @()
    $major = ConvertTo-Number (@($version -split '\.')[0])
    if ($null -ne $major -and $null -ne $minimum -and $major -lt $minimum) {
        $old = 'WARN'
        $hints += Get-Text 'Hint.AppConnection.SqlOutOfSupport'
    }
    if ($slow -ne 'OK') { $hints += Get-Text 'Hint.AppConnection.SqlSlow' }

    New-Finding -Category Apps -Check $check -Severity (Get-WorstSeverity $old $slow) `
        -Value ($lines -join ' | ') -Hint ($hints -join ' | ')
}

function ConvertTo-AppConnectionSection {
    [CmdletBinding()]
    [OutputType([psobject])]
    param([AllowNull()]$Data)

    $sqlServers = Get-DataCollection $Data 'SqlServers'
    New-Section -Title (Get-Text 'Title.AppConnection.Connections') -Row @(
        foreach ($connection in (Get-DataCollection $Data 'Connections')) {
            $target = ConvertTo-OdbcServerTarget -Server (Get-DataProperty $connection 'Server')
            $sql = $null
            if ($target) {
                $sql = @($sqlServers | Where-Object {
                    "$(Get-DataProperty $_ 'HostName')" -eq $target.HostName -and "$(Get-DataProperty $_ 'Instance')" -eq "$($target.Instance)"
                })[0]
            }
            $preLogin = Get-DataProperty $sql 'PreLogin'
            $row = [ordered]@{}
            $row[(Get-Text 'Column.AppConnection.Name')]       = Get-DataProperty $connection 'Name'
            $row[(Get-Text 'Column.AppConnection.Default')]    = Get-TriStateText ([bool](Get-DataProperty $connection 'IsDefault'))
            $row[(Get-Text 'Column.AppConnection.Server')]     = Get-DataProperty $connection 'Server'
            $row[(Get-Text 'Column.AppConnection.Port')]       = Get-DataProperty $sql 'Port'
            $row[(Get-Text 'Column.AppConnection.Database')]   = Get-DataProperty $connection 'Database'
            $row[(Get-Text 'Column.AppConnection.Url')]        = Get-DataProperty $connection 'Url'
            # Which SQL Server that is, and the number: a version is not shown bare.
            $row[(Get-Text 'Column.AppConnection.SqlVersion')] = Get-AppConnectionSqlVersionText -Version (Get-DataProperty $preLogin 'Version')
            $row[(Get-Text 'Column.AppConnection.AnswerMs')]   = Get-DataProperty $preLogin 'Milliseconds'
            $row[(Get-Text 'Column.AppConnection.Source')]     = Get-DataProperty $connection 'Source'
            [pscustomobject]$row
        }
    )

    $server = Get-DataProperty $Data 'Server'
    if ($server) { ConvertTo-ServerSection -Data $server }
}