Security Whitepaper

This page describes how SQLTriage stores credentials, authenticates users, records audit events, and limits what each role can do. It is written for the reviewer whose job is to sign off — not for a fellow engineer. Every claim below cites the file in the public repository that backs it.

1. Threat model

SQLTriage runs on a DBA’s desktop. It reads from SQL Server instances using connection credentials the DBA already holds. The trust boundary is the operator’s workstation.

What SQLTriage protects against:

• A stolen .lmcreds export being read on a different machine.
• A user account with the on-disk credential store but no DPAPI key reading saved passwords.
• An attacker silently editing, deleting, or reordering rows in the audit log without leaving evidence.
• An attacker enumerating valid usernames by timing the login form.
• A non-admin operator changing settings, managing users, or running scripts.

What SQLTriage explicitly does not do:

• It is not a SIEM. The audit log records what SQLTriage did, not what every monitored SQL Server did.
• It is not a vulnerability scanner for the host operating system.
• It does not manage certificate lifecycles for the SQL Servers it monitors.
• It does not install an agent on those servers — every check is a SELECT against DMVs from the operator’s machine.
• It does not call home. There is no telemetry endpoint, no licence server, no update beacon. Network traffic during an audit is to the SQL Servers named in the connection list, and nothing else.

It is a single-tenant Windows desktop tool whose security posture is bounded by the workstation it runs on.

2. Credential handling

Three layers, increasing in scope:

At rest, on this machine. Saved SQL Server passwords are encrypted with AES-256-GCM. The 256-bit key is generated once per machine, stored in config/.credential-key, and the key file itself is wrapped with DPAPI at LocalMachine scope, so any user or service on that machine can use it but the file is useless if copied elsewhere. See CredentialProtector.GetOrCreateAesKey in Data/CredentialProtector.cs.

The DPAPI CurrentUser fallback still writes. A stored value prefixed enc: is DPAPI at CurrentUser scope rather than AES under the machine-scope key. CredentialProtector.Decrypt still decrypts that format on read, for backward compatibility. It is also still written, by two live fallback paths inside CredentialProtector.Encrypt. Both of them call EncryptDpapi(plainText, DataProtectionScope.CurrentUser, "enc:"):

The refusal has two arms, and they leave the key file in different states. The distinction matters, because the recovery is different.

• The key file was not replaced. RefuseUnlessPreserved throws when the existing key file would not unwrap and could not be moved safely aside. Nothing new was written. The original key file is still on disk.
• The key file was already replaced. GetOrCreateAesKey throws a second time, later in the same call, when the aside did succeed, a fresh key file was written, and KeyAsideLifecycle.ProveReplacement could not then prove that file reads back. The new key is refused as the key in force, because a password sealed under a key the next start cannot read comes back empty after a restart with nothing said at the time it was saved.

On both arms there is no usable AES key to seal under, and the DPAPI fallback is the only way the save succeeds at all.

The two fallback paths do not log the same thing, and the difference matters to anyone reading the log for evidence. The refusal path logs at Error immediately before it falls back, and states the change of scope outright: the credential was saved for the Windows account running SQLTriage at that moment, it will read back empty for every other account including the SQLTriage Windows service, and the key file named in the refusal above it needs fixing. The AES-failure path logs at Warning with the text “AES-GCM encryption failed, falling back to DPAPI”. It does not state the scope change and does not state the consequence.

If DPAPI then fails as well, neither path saves anything: both log an Error and Encrypt returns the empty string, which the caller stores as a blank password.

The consequence of a successful fallback is worth stating plainly, because it is a change of scope and not a change of format. An aes: credential is readable by every account on the machine, including the SQLTriage Windows service. An enc: credential is readable only by the Windows account that wrote it. For any other account, Decrypt returns the empty string rather than raising an error (CredentialProtector.DecryptDpapi catches the CryptographicException, logs a Warning and returns empty), so the password reads back blank and nothing at the point of use says why. The way out is to repair the credential key file, then save the credential again. It is then re-encrypted machine-scope as aes:.

The account of CredentialProtector above was read against Data/CredentialProtector.cs on 2026-08-25 and is cited by symbol name. It carried line numbers until that date. They were removed because they did not resolve in the published copy linked above: the published snapshot and the development tree hold the same symbols at different offsets, so a line number that is right in one is wrong in the other, and one of them pointed past the end of the file. Cite a symbol here, not a line. The line numbers elsewhere in this document were not re-checked on 2026-08-25 and may have the same problem.

Closing the two fallbacks is a code change, and it has not been made.

Portable export. To move a server list between machines, SQLTriage can emit a .lmcreds bundle. The user supplies a passphrase; SQLTriage derives a 256-bit AES key with PBKDF2-HMAC-SHA256 — 310,000 iterations, 32-byte random salt — then re-encrypts each password with AES-256-GCM under that derived key. The iteration count matches the OWASP 2023 minimum recommendation for PBKDF2-SHA-256. See Data/CredentialPorter.cs lines 26–28 and 172–178. On import, the passphrase decrypts each password and SQLTriage immediately re-encrypts it with the target machine’s AES key, so the bundle never sits decrypted in cache.

In transit, in memory. Decrypted passwords exist only for the moment a connection is opened. They are not logged, not serialised, and not written to the SQLite cache.

3. Local password authentication

Server Mode supports local-account passwords in addition to OAuth. Passwords are hashed with Argon2id at the parameters OWASP recommended in 2024:

Hashes are stored self-describing: argon2id$v=19$m=19456,t=2,p=1$<salt>$<hash>. Verification uses CryptographicOperations.FixedTimeEquals so a wrong password cannot be distinguished from a wrong username by response timing. On a missing account, the verifier runs the same Argon2 computation against a synthetic hash so the work performed is the same shape whether the email exists or not. See Data/Services/RbacService.cs lines 251–311 and 345–367.

4. Tamper-evident audit log

Every authentication, permission change, script execution, and credential operation is written to a tamper-evident chain. Each entry stores the HMAC-SHA256 signature of (previous-entry-signature || canonical(this-entry)), keyed with a 32-byte HMAC key persisted next to the log file. Any edit, deletion, or reorder breaks the chain from that point forward. On startup, SQLTriage walks the chain and records a .chain-break-marker if verification fails, so the next operator and the next auditor both see the break.

Entries at severity Critical or Error are also mirrored to the Windows Event Log (source SQLTriage-Audit, log Application), so a SIEM or EDR already tailing the Event Log picks them up without integrating with SQLTriage.

Log files rotate at 64 KiB to keep the chain segments small enough to scan quickly; the rotation does not break the chain because each segment seeds from the previous segment’s last signature.

See Data/AuditLogService.cs — rotation threshold line 42; HMAC computation lines 533–555; Event Log mirroring lines 476–481; startup chain verification lines 136–185.

Two operational notes worth being explicit about: the 32-byte HMAC key is held in a file with the Hidden NTFS attribute rather than DPAPI-wrapped, so its confidentiality relies on file-system ACLs on the SQLTriage data directory (line 103–108). If that file is deleted or the bytes are insufficient on read, a new key is generated and the chain restarts from the next entry — visible in the log itself. Treat the data directory’s ACLs as part of the audit posture.

5. Role-based access control

Three roles, with the permission matrix enforced in one place — the private static HasPermission method in RbacService (no line citation on purpose: the previous one had drifted by ~300 lines and pointed at unrelated code):

Unknown permissions default to admin-only.

In the default desktop mode (WPF + WebView2), the local Windows user is treated as Admin — it is a single-user application and the operator already owns the connection credentials. In Server Mode, where the same UI is served over Kestrel for browser access, authentication is enforced through cookie-backed OAuth via Google and Microsoft identity providers (see ServerModeService.cs lines 1–10 and 455–470). OAuth client secrets are themselves encrypted at rest using CredentialProtector.Encrypt before being persisted to the RBAC config (lines 71–77).

6. Defence in depth

• Single-file, signed-where-possible distribution. Release builds publish as a single self-contained executable (SQLTriage.csproj lines 92–95). The shipped assembly is not obfuscated: no build path applies ConfuserEx2. Do not claim obfuscation until a release pipeline actually performs it.
• No external network calls during audit. Checks query DMVs over the existing SQL connection. There is no telemetry, no licence check, no auto- update beacon, and no third-party API in the audit hot path.
• No agent on SQL Server. SQLTriage does not write to monitored databases and does not require any installed component on the SQL Server host.
• Framework-mapped findings. Most audit checks carry citations to the framework controls they satisfy: NIST 800-53, CIS, ISO 27001 and others where applicable. Coverage is uneven across the corpus today; checks without a mapping say so rather than inventing one.
• WAL durability. The local SQLite cache runs in WAL mode and the WAL is checkpointed (PRAGMA wal_checkpoint(FULL)) on application exit so an abrupt shutdown does not corrupt the cache (App.xaml.cs line 332–348).

7. Test coverage on the security-critical paths

The security primitives are covered by unit tests that run on every build:

Tamper detection in particular is exercised against deletion, reorder, single- byte mutation, signature substitution, and key replacement — the cases an auditor will ask about. The full suite lives in Tests/SQLTriage.Tests/.

8. Out of scope

A whitepaper that overclaims is worse than one that is candid, so to be explicit about what SQLTriage is not:

• It is not a replacement for a SIEM. It tells you what SQLTriage did; your SIEM tells you what every other system did.
• It is not a host vulnerability scanner. It audits SQL Server configuration, not the Windows host.
• It does not manage certificate or key lifecycles for the SQL Servers it monitors. Whether TrustServerCertificate is on or off is the operator’s choice on a per-connection basis.
• It does not federate identity. Server Mode supports Google and Microsoft OAuth; SAML and arbitrary OIDC providers are not implemented today.
• It does not encrypt the on-disk SQLite cache itself. Sensitive material (passwords, OAuth secrets) is encrypted before being written; non-sensitive cached query results are not.

If any of these gaps blocks a deployment, raise it on the GitHub issue tracker — they are deliberate scope decisions, not oversights, but the priorities can change with a credible use case behind them.

Last reviewed against the codebase: 2026-05-15. Specific line numbers in this document refer to the source as of that date; the file references will remain correct even as line numbers drift.