Auditing and Threat Detection: Knowing What Happened, and What's Happening Now

You can't investigate what you never recorded. When a row changes it shouldn't have, a permission appears that no one remembers granting, or a login starts probing at 3 a.m., the difference between a five-minute answer and a shrug is whether the right things were being captured before it happened. Auditing is that preparation. This is part two of the series — part one covered authentication, network, and in-database protection.

Same principle as always: diagnosis before prescription. You can't diagnose an incident from evidence you didn't keep — so decide what matters, and record it deliberately.

SQL Server Audit — the built-in system of record

SQL Server Audit is the native, purpose-built way to record activity. It has two halves: a server audit that defines where the record goes, and audit specifications — at the server or database level — that define what gets recorded. Point it at the events that actually matter: failed and successful logins, permission and role changes, schema changes, and access to your sensitive tables.

The detail that makes or breaks it is the target. An audit trail an administrator can quietly edit isn't evidence — it's a suggestion. Write the audit to a location with tight, separate permissions: the Windows Security event log is more protected than the Application log, which any authenticated user can write to. Microsoft is explicit that a sysadmin can tamper with any audit component, so treat the target's protection as part of the design. For regulated environments, this is the artifact an auditor asks for first.

Login and access auditing

Even without a full audit specification, capture login activity. Failed logins expose brute-force attempts and misconfigured applications; successful logins from unexpected accounts or hosts expose misuse. Turn on login auditing at the instance, decide whether you need failed-only or both, and send it somewhere it can be reviewed — ideally forwarded off the box so a compromised server can't erase the evidence of its own compromise.

Tracking data changes: system-versioned temporal tables

Auditing access is one thing; reconstructing how the data itself changed over time is another, and it's where system-versioned temporal tables earn their place. Add a PERIOD FOR SYSTEM_TIME to a table and turn versioning on, and SQL Server automatically keeps a linked history table: every time a row is updated or deleted, the prior version is written to history with the exact window it was valid for (the ValidFrom/ValidTo period columns). You get a complete, engine-maintained record of how the table looked at any point — no triggers to write, no history logic to maintain.

For auditing and investigation, that's powerful. You can query the past directly:

• FOR SYSTEM_TIME AS OF '2026-07-01T09:00' — see exactly what a record said at a moment in time, before a bad update or a disputed change.
• Compare a row's full lineage to answer "when did this value change, and what was it before?" without restoring a backup.
• Recover from an accidental update or delete by reading the value straight out of history.

Two honest caveats, because they decide how you use it. First, temporal tables record what changed and when, but not who — the history table has no built-in "changed by." Pair it with SQL Server Audit, or add your own ModifiedBy column, if attribution matters. Second, it's a history, not a vault: a privileged user can still alter the history table. It's excellent for change tracking and recovery, but when the record itself must be provably untampered, that's a job for Ledger (below).

Worth distinguishing from the change-capture features you may already know: Change Data Capture and Change Tracking exist to feed ETL and synchronization, not to serve as a human-readable audit history. Temporal tables are the one built for "show me what this data used to say."

Deeper capture: Extended Events and DDL triggers

When you need more than the audit spec captures, Extended Events is the lightweight engine for deep, targeted capture — specific statements, waits, errors, or activity against a particular object — without the overhead of the old Profiler traces. And DDL triggers let you react to structural changes: alert on, log, or even roll back an unexpected ALTER or DROP, so schema changes never happen silently.

Tamper-evidence: the Ledger feature

For the cases where "the record hasn't been altered" has to be provable, not just trusted, SQL Server 2022 and Azure SQL add Ledger. Ledger tables cryptographically chain their history — SHA-256 hashes combined through a Merkle tree into database digests you can store in immutable, off-database storage — so any tampering, even by a database administrator, can be detected and demonstrated. It's the right tool when regulation or a chain-of-custody requirement means you need to prove integrity, not merely assert it. Most environments won't need it everywhere; the ones that need it, need it badly.

Threat detection: Microsoft Defender for SQL

Everything above is a record you review after the fact. Microsoft Defender for SQL adds the layer that warns you during — two capabilities in one. Its vulnerability assessment scans the instance against a hardening baseline and hands you a prioritized list of misconfigurations to fix. Its advanced threat protection watches for the patterns that signal an attack in progress: potential SQL injection, brute-force login attempts, and access from unusual locations or unfamiliar principals — and alerts you in near real time.

Tie it together at the destination. In Azure, send SQL auditing to Log Analytics; on-premises, forward audit and login events to your SIEM. Alerts nobody sees aren't detection — the value is in getting the signal to someone who can act on it before the record becomes a post-mortem.

Continue the series: Part 3 — Encryption at Rest →  ·  ← Part 1

If you'd rather have a senior DBA stand up auditing and threat detection on your environment, that's what I do. See SQL Server DBA services, review real client results, or get in touch.

---