Auditing SQL Server is an essential task for identifying security issues and data breaches and ensuring that the integrity and confidentiality of critical data is maintained. In addition, auditing SQL Server is a compliance requirement for many regulations including PCI DSS and HIPAA.
Applies to: SQL Server 2008 R2 and later versions. Some features, such as Common Criteria compliance, require Enterprise edition.
What is SQL Server Auditing?
SQL Server Auditing is the process of tracking, recording, and reviewing events that occur within a SQL Server database engine to detect security issues, ensure compliance, and maintain data integrity.
It lets you create server audits containing server audit events and database audits for database level events. Server audit specifications track server-level events (such as logins and configuration changes), while database audit specifications track events within a specific database (such as table modifications).
There are several levels of auditing for SQL Server, depending on government or standards requirements for your installation. SQL Server Audit provides the tools and processes you need to enable, store, and view audits on various server and database objects. The result of the SQL Server Audit report is stored in event logs or files which can be reviewed as required to detect potential threats and vulnerabilities.
SQL Server Auditing Features
The first step is to determine what you want to audit based on the needs of your organization. For example, you might want to audit user logins, server configuration, schema changes and data modifications. After this, you have to choose which security auditing features to use. Useful features include the following:
Any one or a combination of the above features can be chosen based on the requirements of your organization.
Among these, C2 auditing and Common Criteria compliance are the most widely used international standards for SQL auditing.
C2 Auditing is a legacy security auditing mode (now deprecated in favor of Common Criteria) that records information beyond SQL servers. For example, it would include who triggered the event and in which database, the server name, the event type, and the outcome of the event.
Common Criteria compliance is an international computer security certification standard (ISO/IEC 15408) that enables Residual Information Protection (RIP)—a security feature that overwrites memory allocations with a pattern of bits before they are used by a new resource—the ability to view login statistics, and the table-level DENY taking precedence over the column-level GRANT.
Login Auditing monitors the SQL Server login activities and writes both failed and successful logins to an error log. SQL Trace is an event-driven monitoring tool that captures user activity. Change data capture records all the insert, update, and delete activities in the server table. DML, DDL, and Logon Triggers are used for auditing and regulating database operations.
How to Enable SQL Server Auditing
Prerequisites
Before enabling SQL Server auditing, ensure you have:
- Permissions: CONTROL SERVER or ALTER ANY SERVER AUDIT permission
- SQL Server Edition: Any edition supports basic auditing; Enterprise edition required for Common Criteria compliance
- Disk Space: Sufficient storage for audit log files (each file can reach 200 MB)
- Access: SQL Server Management Studio (SSMS) installed and ability to connect to the database engine
Steps to Enable Auditing
- Open the SQL Server Management Studio.
- Connect to the database engine for which you want to enable C2 auditing. In the Connect to Server dialog box, make sure that Server Type is set to Database Engine and then click Connect.
- In the Object Explorer panel, right-click your SQL Server instance and select Properties.
- In the Server Properties window, click Security under Select a Page.
- On the Security page, you can configure login monitoring. By default, only failed logins are recorded but you can choose to audit Successful logins only, or Both failed and successful logins.
- Check Enable C2 audit tracing under Options.
- If you want to enable C2 Common Criteria Compliance auditing, check the Enable Common Criteria compliance option.
Note
Common Criteria (CC) Compliance is a flexible standard that can be implemented with different Evaluation Assurance Levels (EALs)—a numerical rating (1-7) indicating the depth and rigor of the security evaluation, with higher numbers representing more thorough verification. When you check Enable Common Criteria compliance in SQL Server, you are enabling CC Compliance EAL1. It is possible to configure SQL Server manually for EAL4+.
Enabling CC Compliance changes SQL Server behavior. For example, the table-level DENY permissions will take precedence over column-level GRANT, and both successful and failed logins will be audited. In addition, Residual Information Protection (RIP) is enabled, which overwrites memory allocations with a pattern of bits before they are used by a new resource.
- Click OK.
- Based on the selected options, you might be prompted to restart SQL Server. If you get a message to restart, click OK in the warning dialog. If you have enabled C2 Common Criteria Compliance, reboot the server. Otherwise, right-click your SQL Server instance in Object Explorer again and select Restart from the menu. In the warning dialog, click Yes to confirm that you want to restart SQL Server.
How to Create a Server Audit Object
The following steps explain how to create a server-level SQL Server audit object:
-
- From the SQL Server Management Studio, in the Object Explorer panel on the left, expand Security.
- Right-click Audits and select New Audit. This will create a new SQL Server Audit object for server-level auditing.
- In the Create Audit window, give the audit settings a name in the Audit name
- Specify what should happen if SQL Server auditing fails using the On Audit Log Failure. You can choose Continue or choose to shut down the server. If you select Fail operation, database operations that are not audited will continue to work.
- In the Audit destination dropdown menu, you can choose to write the SQL audit trail to a file or to audit events in the Windows Security log or Application event log. If you choose a file, you must specify a path for the file.
- Click OK.
- You will now find the new audit configuration in Object Explorer below Audits. Right-click the new audit configuration and select Enable Audit from the menu.
- Click Close in the Enable Audit dialog.
How to Create a Database Audit Object
To create a SQL Server audit object for database-level auditing, you need to have created at least one server-level audit object first.
-
- Expand Databases in Object Explorer and expand the database on which you want to configure auditing.
- Expand the Security folder, right click Database Audit Specifications and select New Database Audit Specification from the menu.
- Under the Actions section of the dialog box, use the dropdown menus to configure one or more audit action types, selecting the statements you want to audit (for example DELETE or INSERT), the object class on which the action is performed, and so on.
- When finished, click OK and then enable the audit object by right-clicking it and selecting Enable Database Audit Specification.
How to View SQL Audit Logs
C2 Audit SQL Server audit logs are stored in the default data directory of the SQL Server instance. Each log file can be a maximum of 200 megabytes and a new file is automatically created when the limit is reached.
A native solution that is recommended to view SQL Server audit logs is called Log File Viewer. To use it, do the following:
- In SQL Server Management Studio, under the Object Explorer panel, expand Security. Right-click the audit object that you want to view and select View Audit Logs.
- In the Log File Viewer, the logs will be displayed on the right side. Regardless of whether the logs are written to a file or to the Windows Event Log, Log File Viewer will display the logs.
- At the top of Log File Viewer, you can click Filter to customize which log entries are displayed. SQL Server file logs are saved in .sqlaudit format and are not readable, so Log File Explorer allows you to click Export to save logs to a comma-delimited .log file format.
How Lepide Auditor Helps Audit SQL Server
The Lepide SQL Server Auditor provides you with a simple means of seeing who, what, where and when changes are made to your SQL servers and automatically sends you real-time or threshold-based alerts and detailed reports when needed. It even helps you keep track of the overall health of your SQL Servers.
To Enable SQL Server Auditing using the Lepide Solution
The native way to enable auditing is time consuming as it involves several steps as described previously in this article. Enabling SQL server auditing using the Lepide Solution is a very straightforward one step process:
-
- From the settings screen, under Actions select Start Audit:
SQL Server auditing will now be enabled
With the Lepide Solution implemented into your organization, all aspects of SQL server can be audited with ease. All changes made to the configuration of SQL users, permissions, logins, triggers, databases, and other SQL elements are audited. The raw logs are aggregated into meaningful audit data, saved long-term in a SQL Server database, and displayed in predefined audit reports. You can customize these reports to create a long audit-trail to understand how a change was made and to track a specific critical change.
Lepide SQL Server Reports
Here are two examples of SQL Server Reports from the Lepide SQL Server Auditor. This first is the All Database Object Modification Report and the second is the All Server Object Modification Report.
The information these reports show includes Who made a change, When it was made, the specific Operation carried out and Where from.
The reports can be filtered, grouped, saved and exported.
Key Takeaways
- SQL Server Auditing is the process of tracking and recording database engine events to detect security issues, ensure compliance, and maintain data integrity.
- Three main auditing methods are available: C2 Auditing (legacy), Common Criteria compliance (modern standard), and SQL Server Audit (flexible, customizable framework).
- Server audit specifications track server-level events, while database audit specifications track events within specific databases.
- Audit logs can be stored in files, Windows Security log, or Application event log, and viewed using the built-in Log File Viewer.
- Third-party solutions like Lepide SQL Server Auditor can simplify the auditing process with one-click setup and pre-built reports.
Frequently Asked Questions
SQL Server audit logs can be stored in three locations: a file path you specify (saved as .sqlaudit files in the default data directory), the Windows Security log, or the Windows Application event log. The storage location is configured when creating the audit object.
C2 Auditing is a legacy (now deprecated) auditing mode that records detailed security events. Common Criteria compliance is the modern international security standard (ISO/IEC 15408) that provides additional security features including Residual Information Protection (RIP), login statistics, and stricter permission handling. Microsoft recommends using Common Criteria compliance instead of C2 Auditing.
Open the Log File Viewer by right-clicking your audit object and selecting View Audit Logs. In the Log File Viewer, click the Export button at the top to save the logs to a comma-delimited .log file format that can be opened in Excel or other applications.
You need CONTROL SERVER or ALTER ANY SERVER AUDIT permission to create and manage server audits. For database audit specifications, you need ALTER ANY DATABASE AUDIT permission on the database.