CHFI Database and Application Forensics Practice Question
A forensic investigator is analyzing a Microsoft SQL Server instance that was compromised. The investigator wants to identify all login attempts that failed due to incorrect passwords. Which system function or view should be queried?
⚠ Common exam trap
EC-Council often tests the misconception that dynamic management views (DMVs) like sys.dm_exec_sessions store historical authentication data, when in fact they only reflect current state, not past events.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
xp_readerrorlog with filter for 'Login failed'
The xp_readerrorlog extended stored procedure reads the SQL Server error log, which records all login attempts, including failures. By filtering for 'Login failed', the investigator can retrieve the exact entries where authentication failed due to incorrect passwords. This is the standard method for auditing failed logins in SQL Server.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
sys.dm_exec_sessions
Why it's wrong here
sys.dm_exec_sessions is a dynamic management view that exposes one row per currently authenticated session on the SQL Server instance. While it does include a login_name column, it is a point-in-time snapshot of active sessions, not an audit log of failed authentication attempts; a failed login never creates a session, so this DMV contains no record of rejections such as error 18456.
- ✗
sys.dm_tran_locks
Why it's wrong here
sys.dm_tran_locks is a DMV that tracks lock resources currently held or awaited by active transactions, such as row, page, or table locks used for concurrency control. It has no relationship to authentication or login events, and because failed logins do not start a transaction or request a lock, querying this view yields no information about unsuccessful connection attempts.
- ✓
xp_readerrorlog with filter for 'Login failed'
Why this is correct
The correct approach is to query the SQL Server error log via xp_readerrorlog with a filter for the error message text '%Login failed%'. SQL Server writes failure audit events, including error 18456 with detail such as the login name and reason, to the error log; xp_readerrorlog is an undocumented extended stored procedure that reads the current or archived logs and accepts parameters to filter by text, which makes it effective for forensic analysis of failed logins.
- ✗
sys.dm_exec_requests
Why it's wrong here
sys.dm_exec_requests returns information about requests currently executing within the SQL Server instance, including session ID, SQL text, and wait type, but it only reflects active work at the moment of query execution. A completed or failed login attempt does not constitute an active request, and the DMV does not maintain historical login data, so it cannot reveal prior authentication failures.
Go deeper
Related to this question
About these practice questions
Courseiva writes every CHFI question from scratch — 745 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This CHFI practice question is part of Courseiva's free EC-Council certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the CHFI exam.