CSA (312-39) SOC Simulation Lab
Step into the role of a SOC analyst investigating a web server compromise that pivoted to backend database access. In this lab, you'll learn which logs are critical for identifying exact data modifications and how to differentiate between various log types during an incident.
Scenario Context
You are investigating an incident at FinVault Corp. SIEM alerts triggered off multiple failed authentication attempts against the primary web portal, terminating in a successful login for the `admin_portal` service account. By correlating network flows, you identify that this compromised web server immediately initiated lateral movement to the backend Microsoft SQL Server.
The database holds sensitive customer financial records. Executive management needs to know immediately: "Did the attacker alter any financial records or create backdoor accounts in the database?"
Security Environment
SIEM Telemetry Snapshot (Filtered for Incident Scope):
[14:02:15] W3SVC1: 401 Unauthorized POST /admin/login.aspx - 198.51.100.44
[14:02:18] W3SVC1: 401 Unauthorized POST /admin/login.aspx - 198.51.100.44
[14:02:22] W3SVC1: 200 OK POST /admin/login.aspx - 198.51.100.44 (User: admin_portal)
[14:03:05] WinEvent: Security EventID 4624 (Logon) - Logon Type: 3 (Network) - Src IP: 10.0.5.20 (WebSrv) -> Dst: 10.0.5.50 (SQLSrv)
[14:03:06] SQL_Log: Successful login for user 'sa' from [10.0.5.20]
Analyst Note: The adversary has achieved initial access, escalated privileges to a web admin, and used those credentials (or a stored connection string) to authenticate to the backend database as `sa`. We must now perform damage assessment.
Question
Pearl is an L1 SOC analyst working for a global financial institution that relies on SQL Server databases to store and manage sensitive customer information. As part of her daily security monitoring, she notices an alert on her SOC dashboard indicating a brute-force attack attempt targeting the organization's web application login page. Upon investigating, she observes multiple failed login attempts originating from the same IP address, followed by a successful login as a server administrator. Recognizing the potential threat, she immediately shifts focus to the database server logs to analyze unauthorized access patterns. During detailed analysis of the logs, she uncovers that the attacker successfully brute-forced a privileged user account on the website and using the compromised credentials, they gained access to the SQL Server database. Which of the following logs will help her identify if the intruder performed any unauthorized modifications in the database?
What is happening here?
Pearl has detected a classic multi-stage attack. The adversary executed a successful brute-force against an external web application, compromised an administrative session, and pivoted. Using the web application's trust relationship (likely a hijacked connection string or reused credentials), they authenticated to the backend SQL Server. The critical Incident Response (IR) question now shifts from Containment to Damage Assessment: we know they got in, but did they alter financial records, drop tables, or create new backend users?
Why B (Transaction Log) is correct:
The SQL Server Transaction Log (typically the .ldf file) is the forensic gold standard for identifying modifications. To maintain ACID (Atomicity, Consistency, Isolation, Durability) properties, the database engine writes every single modification (INSERT, UPDATE, DELETE, schema changes) to the transaction log before writing it to the actual data file. By analyzing the transaction log (using native tools like fn_dblog or third-party forensic tools), an analyst can see the exact row-level changes the attacker made, right down to the hexadecimal values before and after the modification.
Why the others are incorrect:
- A. Security Log: The Windows/SQL Security logs track authentication (success/failure) and privilege usage (Event ID 4624/4625), but they do not record the specific SQL queries or row-level data modifications by default.
- C. Maintenance Log: These track scheduled jobs, backups, DBCC (Database Console Commands) checks, and index rebuilds. They provide no visibility into user-driven data modifications.
- D. Audit Log: While SQL Server Audit can be configured granularly to log SELECT, INSERT, and UPDATE statements, it is heavily dependent on the DBA's explicit configuration and is often disabled to save storage. The Transaction log, however, is a fundamental, unavoidable, and always-on architectural requirement of the database engine. In CSA and standard forensic contexts, the T-Log is the definitive answer for database modifications.
Mini Lesson: SOC Triage vs. Forensics
As a SOC Analyst, your job during triage is to confirm the scope of the breach using accessible SIEM logs (Security Logs, Network flows). However, when transitioning to the Damage Assessment phase of the NIST IR Lifecycle, you must know which deep-dive artifacts to request from system administrators. You cannot parse a `.ldf` file directly in Splunk or Sentinel; you must instruct the DBA to isolate the database, back up the active Transaction Log, and use specialized tools to reconstruct the adversary's exact queries to determine if a mandatory breach notification is required.
Ready for the next challenge?
Improve your detection and response thinking with more realistic SOC scenarios.
Explore More CSA Simulations