SQL Server Audit File Enabled for Both Failed and Successful Logins
Introduction
During a routine SQL Server security review, it was observed that SQL Server Audit was configured to capture both FAILED_LOGIN_GROUP and SUCCESSFUL_LOGIN_GROUP events. While auditing successful and failed login attempts helps meet compliance and security requirements, enabling both events without proper planning can result in excessive audit data generation.
Over time, the audit files grew rapidly, consuming significant disk space and making it difficult to identify important security events. In environments with frequent application connections, successful login events accounted for most of the audit records.
Why We Need to Do
SQL Server Audit is designed to track security-related events and store them in audit files, the Windows Security Log, or the Windows Application Log. When both successful and failed login auditing are enabled, SQL Server records every login attempt.
This configuration can lead to several challenges:
- Rapid growth of SQL Server audit files.
- Increased disk space utilization.
- Additional I/O overhead on busy SQL Server instances.
- Difficulty in identifying genuine security incidents because successful login records dominate audit logs.
- Longer backup and maintenance times for audit storage locations.
For example, consider an application that uses connection pooling and establishes thousands of SQL Server connections every hour. If successful login auditing is enabled, every connection is written to the audit file. As a result, audit files can grow by several gigabytes in a short period, even when there are no failed login attempts or security issues.
Organizations that require auditing only unsuccessful authentication attempts may find that auditing successful logins provides limited operational value while significantly increasing audit storage requirements.
How Do We Solve
Step 1: Verify Current Server Audit Specifications
Check whether both successful and failed login audit action groups are enabled.
SELECT
sas.name AS AuditSpecification,
sa.name AS AuditName,
sad.audit_action_name,
sad.audited_result
FROM sys.server_audit_specifications sas
JOIN sys.server_audit_specification_details sad
ON sas.server_specification_id = sad.server_specification_id
JOIN sys.server_audits sa
ON sas.audit_guid = sa.audit_guid
ORDER BY sas.name, sad.audit_action_name;
If the output contains both:
- SUCCESSFUL_LOGIN_GROUP
- FAILED_LOGIN_GROUP
then SQL Server is auditing every successful and failed login.
Step 2: Review Audit File Growth
Review the configured audit location and monitor the size and number of audit files.
SELECT
name,
type_desc,
log_file_path,
max_size,
max_files
FROM sys.server_file_audits;
Large audit files or frequent file rollovers may indicate that successful login auditing is generating excessive audit records.
Step 3: Modify the Server Audit Specification
If the security policy requires auditing only failed login attempts, remove the successful login action group.
Example:
ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification]
DROP (SUCCESSFUL_LOGIN_GROUP);
ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification]
ADD (FAILED_LOGIN_GROUP);
ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification]
WITH (STATE = ON);
Replace ServerAuditSpecification with the appropriate audit specification name in your environment.
Step 4: Validate the Configuration
Verify that only the required audit action groups remain enabled.
SELECT
audit_action_name
FROM sys.server_audit_specification_details;
The output should display only the required audit action groups based on your organization’s security policy.
Real-Time Scenario
A production SQL Server hosted several business applications with thousands of user connections every hour. During routine monitoring, the DBA team observed continuous audit file growth, with new audit files being generated frequently.
Investigation showed that both SUCCESSFUL_LOGIN_GROUP and FAILED_LOGIN_GROUP were enabled. Since the organization’s security policy required only failed login monitoring, the successful login audit action group was removed after obtaining the necessary approval.
Following the change:
- Audit file generation reduced significantly.
- Disk space consumption stabilized.
- Security teams could focus on failed login attempts without reviewing large volumes of successful login events.
- Overall audit management became more efficient while remaining compliant with organizational requirements.
Conclusion
By reviewing the SQL Server Audit configuration and enabling only the required login audit events, unnecessary audit file growth was eliminated while maintaining the organization’s security objectives.
This approach reduced storage consumption, improved audit log management, and simplified security monitoring by focusing on relevant authentication failures. Periodic reviews of SQL Server Audit configurations can help ensure that auditing remains aligned with compliance requirements without introducing unnecessary operational overhead.
