Auditing Microsoft SQL Server plays a vital role in detecting security incidents and potential breaches. It is also a mandatory requirement for meeting compliance standards such as PCI DSS and HIPAA.
The initial step is to determine which activities should be audited. Typical audit targets include user authentication events, server configuration changes, schema modifications, and data changes. After defining the scope, the next task is selecting the appropriate security auditing mechanisms. Commonly used auditing features include:
- C2 Auditing
- Common Criteria Compliance
- Login Auditing
- SQL Server Auditing
- SQL Trace
- Extended Events
- Change Data Capture
- DML, DDL, and Logon Triggers
This article is intended for database administrators (DBAs) who plan to use C2 auditing, Common Criteria Compliance, or SQL Server Auditing. Third-party auditing tools are not covered, although such tools can be extremely beneficial, particularly in large-scale or highly regulated environments.
Enabling C2 Auditing and Common Criteria Compliance
For environments where auditing is not yet configured, enabling C2 auditing provides the simplest starting point. C2 auditing is an internationally recognized standard available in SQL Server. It records events such as user logins, stored procedure execution, and object creation or deletion. However, it operates as an all-or-nothing mechanism, offering no granularity in selecting audited events. As a result, it can generate large volumes of audit data. Additionally, C2 auditing is currently in maintenance mode and is expected to be removed in future SQL Server releases.
Common Criteria Compliance is a more recent standard that replaces C2 auditing. Developed by the European Union, it is supported in Enterprise and Datacenter editions of SQL Server 2008 R2 and later. Enabling this standard may introduce performance overhead if the server hardware is not adequately provisioned.
Steps to enable C2 auditing in SQL Server 2017:
1. Launch SQL Server Management Studio (SSMS).
2. Connect to the appropriate database engine instance. In the Connect to Server dialog, ensure that Server type is set to Database Engine, then click Connect.
3. In Object Explorer, right-click the SQL Server instance at the top and choose Properties.
4. In the Server Properties window, under Select a page, choose Security.
5. On the Security page, configure login auditing behavior. By default, only failed logins are logged. Alternatively, logging can be set to successful logins only, or both successful and failed attempts.
6. Under Options, enable C2 audit tracing.
7. To activate Common Criteria Compliance, select Enable Common Criteria compliance.
Common Criteria (CC) Compliance supports multiple Evaluation Assurance Levels (EALs), ranging from EAL1 to EAL7. Higher levels require increasingly rigorous validation. Enabling Common Criteria compliance in SQL Server activates EAL1 by default, although manual configuration can support EAL4 or higher.
Activating CC Compliance modifies SQL Server behavior. For example, table-level DENY permissions override column-level GRANT permissions, and both successful and failed login attempts are logged. Residual Information Protection (RIP) is also enabled, ensuring memory blocks are overwritten before reuse.
8. Click OK to apply the configuration.
9. Depending on selected options, SQL Server may require a restart. If prompted, confirm the restart. When Common Criteria Compliance is enabled, a full server reboot is required. Otherwise, restart the SQL Server instance from Object Explorer.
Enabling SQL Server Audit
SQL Server Auditing can be used as an alternative to C2 auditing, or alongside it. SQL Server Audit objects support event collection at both the server and database levels.
Creating a Server Audit Object
To configure server-level auditing, follow these steps:
1. In Object Explorer, expand Security.
2. Right-click Audits and select New Audit… to create a server-level audit object.
3. In the Create Audit window, assign a name in the Audit name field.
4. Define the behavior for audit failures using On Audit Log Failure. Available options include continuing operation, shutting down the server, or failing only audited database operations. Selecting Fail operation allows non-audited actions to continue.
5. Choose the audit destination. Options include a file, the Windows Security log, or the Application event log. When using a file, a storage path must be specified.
Writing to the Windows Security log requires additional permissions. For simplicity, selecting the Application event log is recommended. Optional T-SQL filters may also be applied to restrict audit scope.
6. Click OK.
7. The new audit object appears under Audits in Object Explorer. Right-click it and select Enable Audit.
8. Confirm by clicking Close.
Creating a Database Audit Object
Database-level auditing requires an existing server audit object.
1. In Object Explorer, expand Databases, then select the target database.
2. Expand Security, right-click Database Audit Specifications, and choose New Database Audit Specification….
3. In the Properties window under Actions, configure audit actions by selecting the statement type (e.g., INSERT or DELETE), object class, and related options.
4. Click OK, then enable the audit by right-clicking the specification and selecting Enable Database Audit Specification.
Viewing SQL Server Audit Logs
C2 audit logs and SQL Server audit files are stored in the default data directory of the SQL Server instance. Each file has a maximum size of 200 MB, after which a new file is automatically created.
SQL Server includes a native utility called Log File Viewer for reviewing audit data. To access it:
1. In SQL Server Management Studio, expand Security in Object Explorer.
2. Right-click the desired audit object and select View Audit Logs.
3. The Log File Viewer displays audit entries on the right-hand side, regardless of whether logs are written to files or Windows event logs.
4. Use the Filter option to refine displayed entries. SQL Server audit files are stored in .sqlaudit format and are not directly readable. The Log File Viewer provides an Export function to save logs in a comma-delimited .log file format.
