17.2 SQL Server Forensics: SSMS, ApexSQL, SQLCMD Transaction-Log Collection, Plan Cache & Trace Files

17.2 SQL Server Forensics: SSMS, ApexSQL, SQLCMD Transaction-Log Collection, Plan Cache & Trace Files

Quick Answer: Blueprint Domain 4 requires collecting volatile database data, collecting primary data files and active transaction logs using SQLCMD, collecting active transaction logs using SQL Server Management Studio, collecting database plan cache, and collecting SQL Server trace files and error logs; Domain 5 adds database forensics using SQL Server Management Studio and ApexSQL DBA. SQL Server stores data in MDF (primary), NDF (secondary), and LDF (transaction log) files. The LDF is the journal that proves what was deleted, and the plan cache is volatile — it dies with the service.

---

Physical File Architecture

Data files live under ...\MSSQL\DATA\, and the trace and error logs under ...\MSSQL\LOG\. Resolve the real paths rather than assuming:

SELECT name, physical_name, type_desc, state_desc, size FROM sys.master_files;
SELECT SERVERPROPERTY('ErrorLogFileName'), SERVERPROPERTY('InstanceDefaultDataPath');

[!IMPORTANT] Never copy a live MDF/LDF pair with a file-copy utility. Data files of a running instance are locked and internally inconsistent mid-checkpoint; a raw copy produces a database that will not attach. Either take a BACKUP DATABASE ... WITH COPY_ONLY (which does not disturb the differential backup chain), or image the volume with the instance cleanly stopped. Record which path was chosen and why.

---

Order of Volatility Inside a Database Instance

The RFC 3227 principle applies within the DBMS just as it does to a host:

1. Plan cache, active sessions, open transactions, locks — destroyed by a service restart, a memory-pressure eviction, or DBCC FREEPROCCACHE.
2. Active transaction log (LDF) — truncated on checkpoint in simple recovery model, or on log backup in full recovery model.
3. Default trace and error logs — roll over by size and count.
4. MDF/NDF data files — persist in the forensic image.

Collect in that order. An examiner who shuts the instance down "to image it cleanly" first has destroyed items 1 and much of 2.

---

Collecting Volatile Database Data with SQLCMD

sqlcmd runs from a trusted external binary on the responder's media and writes output straight to evidence storage.

:: Windows-authenticated connection (-E), no headers, comma-separated, to evidence drive
sqlcmd -S TARGET\SQLPROD -E -W -s"," -h-1 ^
  -Q "SELECT session_id, login_name, host_name, program_name, client_net_address, login_time, status FROM sys.dm_exec_sessions s JOIN sys.dm_exec_connections c ON s.session_id=c.session_id" ^
  -o E:\evidence\sessions.csv

:: Open transactions that have not yet committed
sqlcmd -S TARGET\SQLPROD -E -Q "SELECT * FROM sys.dm_tran_active_transactions" -o E:\evidencective_tran.csv

:: COPY_ONLY backup capturing data and the active log together, without breaking the backup chain
sqlcmd -S TARGET\SQLPROD -E -Q "BACKUP DATABASE [AppDB] TO DISK='E:\evidence\AppDB.bak' WITH COPY_ONLY, CHECKSUM, INIT"
sqlcmd -S TARGET\SQLPROD -E -Q "BACKUP LOG [AppDB] TO DISK='E:\evidence\AppDB_log.trn' WITH COPY_ONLY, CHECKSUM, INIT"

Hash every output file at the point of creation. WITH CHECKSUM makes the backup self-verifying, which is a useful integrity statement in the report alongside the examiner's own hash.

SQL Server Management Studio (SSMS) performs the same collection through a GUI — Tasks ▸ Back Up with the Copy-only checkbox, and Reports ▸ Standard Reports for schema-change history — and additionally provides the query window used for the transaction-log reconstruction below. SSMS is convenient for analysis on a restored copy; scripted sqlcmd is preferred on a live target because the exact commands are reproducible and logged.

---

Reconstructing Deleted Data from the Transaction Log

This is the heart of SQL Server forensics. The write-ahead log records each modification before the data page is updated, so the log holds operations the tables no longer reflect.

-- Read the ACTIVE transaction log of an attached database
SELECT [Current LSN], Operation, Context, AllocUnitName,
       [Transaction Name], [Transaction ID], [Begin Time], [End Time], [Transaction SID]
FROM fn_dblog(NULL, NULL)
WHERE Operation IN ('LOP_DELETE_ROWS','LOP_MODIFY_ROW','LOP_INSERT_ROWS')
ORDER BY [Current LSN];

-- Read a transaction LOG BACKUP file (evidence copy, no live instance needed)
SELECT [Current LSN], Operation, AllocUnitName, [Transaction Name], [Begin Time]
FROM fn_dump_dblog(NULL,NULL,'DISK',1,'E:\evidence\AppDB_log.trn',
                   DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
                   DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
                   DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
                   DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
                   DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
                   DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
                   DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT);

Operations that matter: LOP_DELETE_ROWS, LOP_INSERT_ROWS, LOP_MODIFY_ROW, LOP_BEGIN_XACT / LOP_COMMIT_XACT (bracketing a transaction with its Begin Time and the Transaction SID that identifies the security principal), and LOP_FORMAT_PAGE (page reinitialization, produced by TRUNCATE TABLE).

The RowLog Contents 0 column holds the raw row bytes, which must be decoded against the table's column definitions to reproduce the deleted values — the step commercial tools automate.

[!IMPORTANT] fn_dblog and fn_dump_dblog are undocumented and unsupported. That does not make them inadmissible, but it obliges the examiner to validate the output independently — typically by restoring the backup to a point in time before the deletion and comparing the reconstructed rows to the restored table. Validation against a known-good reference is exactly the Daubert answer for an unsupported technique.

Recovery-model caveat: under the simple recovery model, the log truncates at every checkpoint, so the window of recoverable history may be minutes. Under full recovery, history extends back to the last log backup, and the chain of .trn backups extends it further. Establish SELECT name, recovery_model_desc FROM sys.databases; before promising a client that deleted rows are recoverable.

---

Plan Cache and DMV Capture

The plan cache holds the text of recently executed statements, including ad-hoc SQL an application never stored anywhere.

SELECT TOP 500
       qs.last_execution_time, qs.execution_count,
       DB_NAME(st.dbid) AS database_name,
       st.text AS statement_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.last_execution_time DESC;

SELECT cp.objtype, cp.usecounts, cp.size_in_bytes, st.text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE st.text LIKE '%xp_cmdshell%' OR st.text LIKE '%OPENROWSET%' OR st.text LIKE '%sp_OACreate%';

Those three strings are the classic SQL-Server-to-host escalation primitives: xp_cmdshell executes operating-system commands, OPENROWSET with BULK reads server files, and sp_OACreate instantiates OLE automation objects. Finding any of them in the plan cache on a server whose application never uses them is a strong compromise indicator — and finding sp_configure 'xp_cmdshell', 1 in the default trace shows the attacker enabling it.

The plan cache is evidence with a fuse. It is cleared by a service restart, by DBCC FREEPROCCACHE, by memory pressure, and by certain configuration changes. Capture it in the first minutes of live response or lose it.

---

Default Trace and Error Log

The default trace runs unless explicitly disabled and captures object creation and deletion, sp_configure changes, login failures, database and permission changes, and log-file growth.

SELECT TE.name AS EventClass, T.DatabaseName, T.ObjectName, T.LoginName,
       T.HostName, T.ApplicationName, T.StartTime, T.TextData
FROM sys.fn_trace_gettable(
       CONVERT(nvarchar(260), (SELECT value FROM sys.fn_trace_getinfo(1) WHERE property = 2)), DEFAULT) T
JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
ORDER BY T.StartTime DESC;

The .trc files sit in the MSSQL\LOG directory as log_1.trc … log_5.trc, rolling by size — another reason to preserve early.

The error log (ERRORLOG, with archived ERRORLOG.1 … ERRORLOG.6) yields service start and stop times, Login failed for user '...' entries with the error state that distinguishes a bad password from a nonexistent login, backup and restore events, and evidence of a configuration change requiring restart. It is a plain text file readable directly from the image, or queryable live with EXEC sp_readerrorlog.

Extended Events (the modern replacement for SQL Trace) writes .xel files that are read with sys.fn_xe_file_target_read_file and provide far richer capture where the organization configured a session in advance.

---

ApexSQL and Commercial Log Readers

The blueprint names ApexSQL DBA explicitly. The relevant component is ApexSQL Log, a transaction-log reader that parses .ldf files and log backups and renders operations as readable SQL with the originating user, application, and host — and generates UNDO scripts that reverse a destructive operation and REDO scripts that replay it.

[!WARNING] UNDO generation is a remediation feature, not a forensic one. Running an UNDO script against the evidence database modifies it and destroys the state under examination. Generate UNDO only against a working copy, and keep the original restored image untouched and hashed.

Whatever tool is used, it must be validated under NIST CFTT-style methodology — demonstrated against a database with a known deletion history — before its output supports testimony.