mcp/Start-SqlCertAuditMcp.ps1
|
# ============================================================================= # Script : Start-SqlCertAuditMcp.ps1 # Author : Keith Ramsey # Created : 2026-06-30 # ============================================================================= # MVP Model Context Protocol (MCP) server for the SqlCertForge.Audit (read-only) # module. Zero-dependency: implements MCP stdio transport (newline-delimited # JSON-RPC 2.0) by hand -- initialize / tools/list / tools/call / ping. # # Exposes ONLY the 4 read-only audit tools. No state-changing cmdlets. This is # the free "funnel" tier; the paid/state-changing surface is the future C#-hosted # MCP with a compiled license (see BusinessCreation MCP plan). # # Protocol rules honored: # - stdout carries ONLY MCP JSON messages, one per line, no embedded newlines. # - stderr is used for any diagnostics. # ============================================================================= [CmdletBinding()] param() $ErrorActionPreference = 'Stop' [Console]::OutputEncoding = [System.Text.UTF8Encoding]::new($false) [Console]::InputEncoding = [System.Text.UTF8Encoding]::new($false) # --- load the read-only module (keep stdout clean: route any noise to stderr) --- try { Import-Module (Join-Path $PSScriptRoot '..\SqlCertForge.Audit.psd1') -Force -ErrorAction Stop 3>$null 4>$null 5>$null 6>$null } catch { [Console]::Error.WriteLine("FATAL: could not load SqlCertForge.Audit: $($_.Exception.Message)") exit 1 } # --- tool catalogue: MCP tool name -> PowerShell function + input schema ---- $script:Tools = @( @{ name = 'get_rs_http_config' fn = 'Get-RsHttpConfig' description = 'Inventory a Reporting Services instance HTTPS surface (read-only): reserved URLs, SSL certificate bindings, and the actual registered/browseable URLs. Makes no changes.' inputSchema = @{ type = 'object' properties = @{ RsInstance = @{ type = 'string'; description = "RS instance: 'SSRS', 'PBIRS', or the SQL instance name."; default = 'MSSQLSERVER' } ComputerName = @{ type = 'string'; description = 'Target computer. Defaults to the local machine.' } } } }, @{ name = 'test_rs_https_endpoint' fn = 'Test-RsHttpsEndpoint' description = 'Confirm a Reporting Services endpoint actually SERVES over HTTPS (read-only): discovers the URL, issues a GET (200/401 = healthy), and validates the served certificate SAN/thumbprint. For wildcard reservations, pass VanityHost.' inputSchema = @{ type = 'object' properties = @{ RsInstance = @{ type = 'string'; default = 'MSSQLSERVER' } Application = @{ type = 'string'; enum = @('ReportServerWebService', 'ReportServerWebApp'); default = 'ReportServerWebService' } VanityHost = @{ type = 'string'; description = 'The host name users actually type (required when the reservation is a strong wildcard).' } Url = @{ type = 'string'; description = 'Explicit full URL to test (overrides discovery).' } ExpectedThumbprint = @{ type = 'string'; description = '40-hex thumbprint the served cert must match.' } ComputerName = @{ type = 'string' } } } }, @{ name = 'test_sql_cert_binding' fn = 'Test-SqlCertBinding' description = 'Report the TLS certificate binding for one or more SQL Server instances from the registry (read-only): thumbprint, ForceEncryption, and expiry. Supports remote -Node.' inputSchema = @{ type = 'object' properties = @{ SqlInstance = @{ type = 'array'; items = @{ type = 'string' }; description = "Instance(s): 'MSSQLSERVER' or 'HOST\\INSTANCE'." } Node = @{ type = 'array'; items = @{ type = 'string' }; description = 'Computer(s) to read. Defaults to local.' } } required = @('SqlInstance') } }, @{ name = 'test_rs_cert_binding' fn = 'Test-RsCertBinding' description = 'Report the TLS certificate bound to a Reporting Services endpoint via WMI (read-only): thumbprint and expiry for the requested application.' inputSchema = @{ type = 'object' properties = @{ RsInstance = @{ type = 'string'; default = 'MSSQLSERVER' } Application = @{ type = 'string'; enum = @('ReportServerWebService', 'ReportServerWebApp'); default = 'ReportServerWebService' } ComputerName = @{ type = 'string' } } } }, @{ name = 'get_sql_cert_inventory' fn = 'Get-SqlCertInventory' description = 'Estate-wide, read-only inventory of every SQL Server certificate surface -- engine connection binding, Reporting Services, TDE, backup encryption, endpoints (mirroring / AG / Service Broker) and cell-level -- one row per certificate (or per unreadable surface) with expiry. Makes no changes; never throws.' inputSchema = @{ type = 'object' properties = @{ SqlInstance = @{ type = 'array'; items = @{ type = 'string' }; description = "Instance(s) to inventory: 'localhost', 'HOST', or 'HOST\\INSTANCE'."; default = @('localhost') } ThresholdDays = @{ type = 'integer'; description = 'Days-to-expiry at or below which a row is flagged Expiring.'; default = 30 } } } }, @{ name = 'test_sql_tde_configuration' fn = 'Test-SqlTdeConfiguration' description = 'Report the Transparent Data Encryption state of an instance (read-only): the database master key, the encrypted databases and their DEK encryptors, the TDE certificates, and -- the part that matters most -- whether each TDE certificate private key has been backed up. Makes no changes.' inputSchema = @{ type = 'object' properties = @{ SqlInstance = @{ type = 'string'; description = "The instance to inspect."; default = 'localhost' } } } }, @{ name = 'test_sql_backup_encryption_readiness' fn = 'Test-SqlBackupEncryptionReadiness' description = 'Report whether certificate-encrypted backups can still be restored on this server (read-only): for each encrypting certificate, whether it is present here and escrowed, plus the dependent databases and backup counts. Optionally check one backup FILE via -BackupPath (the DR-server question msdb history cannot answer). Makes no changes.' inputSchema = @{ type = 'object' properties = @{ SqlInstance = @{ type = 'string'; description = "The instance to audit."; default = 'localhost' } BackupPath = @{ type = 'string'; description = 'Optional: a specific backup file to check restorability for on THIS server (RESTORE HEADERONLY).' } } } }, @{ name = 'test_sql_endpoint_cert_auth' fn = 'Test-SqlEndpointCertAuth' description = 'Report certificate authentication on a database-mirroring / Always On AG or Service Broker endpoint (read-only): whether the endpoint exists, its state, whether it authenticates by certificate, and which certificate with expiry. Makes no changes.' inputSchema = @{ type = 'object' properties = @{ SqlInstance = @{ type = 'string'; description = "The instance to inspect."; default = 'localhost' } EndpointType = @{ type = 'string'; enum = @('DatabaseMirroring', 'ServiceBroker'); description = 'Which endpoint to inspect.'; default = 'DatabaseMirroring' } } } }, @{ name = 'test_sql_column_master_key' fn = 'Test-SqlColumnMasterKey' description = "Audit a database's Always Encrypted Column Master Keys (read-only): each key's store provider, key path and dependent CEK count, and -- for the certificate-store provider -- whether the referenced certificate is present locally, with expiry. Makes no changes." inputSchema = @{ type = 'object' properties = @{ SqlInstance = @{ type = 'string'; description = "The instance to query."; default = 'localhost' } Database = @{ type = 'string'; description = 'The database whose column master keys to audit.' } } required = @('Database') } }, @{ name = 'test_sql_cell_key_hierarchy' fn = 'Test-SqlCellKeyHierarchy' description = "Audit a user database's cell-level encryption certificate/key hierarchy (read-only): each certificate (excluding built-in ##MS_* certs), the symmetric key(s) it protects, and which principals may use it -- the least-privilege check for hand-rolled column encryption. Makes no changes." inputSchema = @{ type = 'object' properties = @{ SqlInstance = @{ type = 'string'; description = "The instance to query."; default = 'localhost' } Database = @{ type = 'string'; description = 'The user database to audit.' } } required = @('Database') } }, @{ name = 'test_sql_polybase_certificate' fn = 'Test-SqlPolyBaseCertificate' description = 'Report PolyBase scale-out certificate readiness (read-only): whether PolyBase is installed, the scale-out compute nodes, and -- when an engine IP:port is supplied -- the certificate bound to that HTTP.sys port with subject and expiry. Verifiable facts only; makes no changes.' inputSchema = @{ type = 'object' properties = @{ SqlInstance = @{ type = 'string'; description = "The instance to query."; default = 'localhost' } IPPort = @{ type = 'string'; description = "Optional: the PolyBase engine HTTP.sys IP:port to check for a certificate binding, e.g. '0.0.0.0:16450'." } } } }, @{ name = 'test_sql_cert_client_trust' fn = 'Test-SqlCertClientTrust' description = "Report whether a client machine trusts a SQL/RS TLS issuer certificate (read-only): loads the issuer/root .cer you supply and reports whether it is present in the target machine's LocalMachine\\Root store, with thumbprint and expiry. Makes no changes." inputSchema = @{ type = 'object' properties = @{ CertificatePath = @{ type = 'string'; description = 'Path to the issuer/root certificate file (.cer) to check for.' } ComputerName = @{ type = 'string'; description = 'The client machine whose trust store to read. Defaults to the local machine.' } } required = @('CertificatePath') } } ) function Write-Rpc($obj) { # Compact, single-line JSON (string values keep newlines as escaped \n). [Console]::Out.WriteLine(($obj | ConvertTo-Json -Depth 25 -Compress)) } [Console]::Error.WriteLine("sqlcertforge-audit MCP server ready ($(($script:Tools).Count) tools).") while ($null -ne ($line = [Console]::In.ReadLine())) { if ([string]::IsNullOrWhiteSpace($line)) { continue } try { $msg = $line | ConvertFrom-Json -ErrorAction Stop } catch { [Console]::Error.WriteLine("bad JSON: $($_.Exception.Message)"); continue } $id = if ($msg.PSObject.Properties['id']) { $msg.id } else { $null } $method = [string]$msg.method switch ($method) { 'initialize' { Write-Rpc @{ jsonrpc = '2.0'; id = $id; result = @{ protocolVersion = '2024-11-05' capabilities = @{ tools = @{} } serverInfo = @{ name = 'sqlcertforge-audit'; version = '0.1.0' } } } } 'notifications/initialized' { } # notification: no response 'ping' { Write-Rpc @{ jsonrpc = '2.0'; id = $id; result = @{} } } 'tools/list' { $list = $script:Tools | ForEach-Object { @{ name = $_.name; description = $_.description; inputSchema = $_.inputSchema } } Write-Rpc @{ jsonrpc = '2.0'; id = $id; result = @{ tools = @($list) } } } 'tools/call' { $name = [string]$msg.params.name $tool = $script:Tools | Where-Object { $_.name -eq $name } | Select-Object -First 1 if (-not $tool) { Write-Rpc @{ jsonrpc = '2.0'; id = $id; error = @{ code = -32602; message = "Unknown tool: $name" } } } else { try { $params = @{} if ($msg.params.PSObject.Properties['arguments'] -and $null -ne $msg.params.arguments) { $msg.params.arguments.PSObject.Properties | ForEach-Object { $params[$_.Name] = $_.Value } } $res = & $tool.fn @params $text = $res | ConvertTo-Json -Depth 25 Write-Rpc @{ jsonrpc = '2.0'; id = $id; result = @{ content = @(@{ type = 'text'; text = $text }); isError = $false } } } catch { Write-Rpc @{ jsonrpc = '2.0'; id = $id; result = @{ content = @(@{ type = 'text'; text = "Error: $($_.Exception.Message)" }); isError = $true } } } } } default { if ($null -ne $id) { Write-Rpc @{ jsonrpc = '2.0'; id = $id; error = @{ code = -32601; message = "Method not found: $method" } } } } } } |