SQL Install privilege check

Test-LocalSecurityPolicy.ps1

[CmdletBinding()]
param(
    [Parameter()]
    [string]$PolicyPath,

    [Parameter()]
    [switch]$PassThru,

    [Parameter()]
    [switch]$Quiet
)

Set-StrictMode -Version 2.0
$ErrorActionPreference = 'Stop'

# This script checks the local security policy against a known good baseline.
# This script is intended to be run on a Windows system with PowerShell 5.1 or later.
# This script is not intended to modify the local security policy, only to report on compliance with the baseline or facilitate troubleshooting.
# This script is not intended to be run on a domain controller, as the local security policy is not used on domain controllers, and the default values are different.
# This script was written and will be maintained with a SQL Server focus.
# This script was written with the assistance of Copilot.

# Example usage:
# Test the local security policy against the current system's settings:
#  .\Test-LocalSecurityPolicy.ps1
# Test the security policy exported from another system:
#  secedit.exe /export /cfg sqldb1.inf
#  .\Test-LocalSecurityPolicy.ps1 -PolicyPath 'sqldb1.inf'

# Replace or extend these entries with the settings expected by your baseline.
$KnownGood = @(
    # Required by SQL Server installation and update
    [pscustomobject]@{ Section = 'Privilege Rights'; Key = 'SeSecurityPrivilege'; Expected = '*S-1-5-32-544' } # SID for Administrators group
    [pscustomobject]@{ Section = 'Privilege Rights'; Key = 'SeBackupPrivilege'; Expected = '*S-1-5-32-544,*S-1-5-32-551' } # SID for Administrators and Backup Operators groups
    [pscustomobject]@{ Section = 'Privilege Rights'; Key = 'SeDebugPrivilege'; Expected = '*S-1-5-32-544' } # SID for Administrators group
    #Other examples
    # [pscustomobject]@{ Section = 'System Access'; Key = 'MinimumPasswordLength'; Expected = '14' }
    # [pscustomobject]@{ Section = 'System Access'; Key = 'PasswordComplexity'; Expected = '1' }
    # [pscustomobject]@{ Section = 'System Access'; Key = 'LockoutBadCount'; Expected = '5' }
    # [pscustomobject]@{ Section = 'System Access'; Key = 'ResetLockoutCount'; Expected = '15' }
    # [pscustomobject]@{ Section = 'System Access'; Key = 'LockoutDuration'; Expected = '15' }
)

function Import-SecurityPolicy {
    param(
        [Parameter(Mandatory)]
        [string]$Path
    )

    $settings = @{}
    $section = $null

    foreach ($line in Get-Content -LiteralPath $Path) {
        $trimmedLine = $line.Trim()

        if ($trimmedLine -match '^\[(.+)\]$') {
            $section = $Matches[1].Trim()
            if (-not $settings.ContainsKey($section)) {
                $settings[$section] = @{}
            }
            continue
        }

        if (-not $section -or -not $trimmedLine -or $trimmedLine.StartsWith(';')) {
            continue
        }

        $separatorIndex = $trimmedLine.IndexOf('=')
        if ($separatorIndex -lt 1) {
            continue
        }

        $key = $trimmedLine.Substring(0, $separatorIndex).Trim()
        $value = $trimmedLine.Substring($separatorIndex + 1).Trim()
        $settings[$section][$key] = $value
    }

    return $settings
}

$temporaryPolicyPath = $null

try {
    if (-not $PolicyPath) {
        $secedit = Get-Command -Name 'secedit.exe' -ErrorAction SilentlyContinue
        if (-not $secedit) {
            throw 'secedit.exe was not found. This script must run on Windows.'
        }

        $currentIdentity = [Security.Principal.WindowsIdentity]::GetCurrent()
        $currentPrincipal = [Security.Principal.WindowsPrincipal]::new($currentIdentity)
        if (-not $currentPrincipal.IsInRole([Security.Principal.WindowsBuiltInRole]::Administrator)) {
            throw 'Exporting local security policy requires an elevated PowerShell session. Run PowerShell as Administrator and try again.'
        }

        $temporaryPolicyPath = Join-Path ([System.IO.Path]::GetTempPath()) ("security-policy-{0}.inf" -f [guid]::NewGuid())
        $seceditOutput = & $secedit.Source /export /cfg $temporaryPolicyPath /quiet 2>&1
        if ($LASTEXITCODE -ne 0 -or -not (Test-Path -LiteralPath $temporaryPolicyPath)) {
            throw "Unable to export the local security policy with secedit.exe (exit code $LASTEXITCODE). $($seceditOutput -join ' ')"
        }

        $PolicyPath = $temporaryPolicyPath
    }
    elseif (-not (Test-Path -LiteralPath $PolicyPath -PathType Leaf)) {
        throw "Policy file not found: $PolicyPath"
    }

    $policy = Import-SecurityPolicy -Path $PolicyPath
    $results = foreach ($baselineSetting in $KnownGood) {
        $actual = $null
        $found = $policy.ContainsKey($baselineSetting.Section) -and
            $policy[$baselineSetting.Section].ContainsKey($baselineSetting.Key)

        if ($found) {
            $actual = $policy[$baselineSetting.Section][$baselineSetting.Key]
        }

        [pscustomobject]@{
            Section   = $baselineSetting.Section
            Key       = $baselineSetting.Key
            Expected  = $baselineSetting.Expected
            Actual    = $actual
            Compliant = $found -and ($actual -eq $baselineSetting.Expected)
        }
    }
    
    $nonCompliant = @($results | Where-Object { -not $_.Compliant })

    if (!$Quiet) {
        Write-Host ''
        Write-Host "Local Security Policy Compliance Report" -ForegroundColor Cyan
        if ($policyPath) {
            Write-Host "Policy Path: $PolicyPath"
        }
        else {
            Write-Host "Policy Path: Current System"
        }
        Write-Host "Total Settings Checked: $($results.Count)"
        if ($nonCompliant.Count -eq 0) {
            Write-Host "All settings are compliant with the baseline." -ForegroundColor Green
        }
        else {
            Write-Host "Non-compliant settings found: $($nonCompliant.Count)" -ForegroundColor Red
        }
        Write-Host ''
        $results | Format-Table -AutoSize | Out-Host
    }


    if ($PassThru) {
        Write-Output $results
    }

    if ($nonCompliant.Count -gt 0) {
        exit 1
    }
    exit 0
}
finally {
    if ($temporaryPolicyPath -and (Test-Path -LiteralPath $temporaryPolicyPath)) {
        Remove-Item -LiteralPath $temporaryPolicyPath -Force -ErrorAction SilentlyContinue
    }
}