In today’s Information Era, data is being collected on a massive scale. With the ease of access to this data, it has become crucial to implement standardized auditing requirements to track and investigate any suspicious activities. Microsoft has introduced the Auditing feature in SQL Server 2008 onwards to address this need.
Auditing in SQL Server prior to 2008
Before SQL Server 2008, auditing had to be done using a combination of features such as Login Auditing, C2 auditing, Triggers and event notifications, and SQL Trace in conjunction with SQL Profiler. However, this approach was cumbersome and involved a significant amount of setup. The data accumulated by these methods were logged in different ways to various locations, making it hard to assimilate. Additionally, there could be potential performance impacts associated with some of these actions.
Auditing in SQL Server 2008 onwards
SQL Server auditing is a new feature that makes use of extended events to allow you to audit everything that happens in your server. This includes server setting changes, as well as modifications to specific tables in the database. The audit information is then written to the Windows security log, the Windows application log, or to a flat file.
In SQL Server 2008, Auditing was an enterprise-only feature. However, in SQL Server 2012, server auditing has been made available to all editions, while database auditing remains for use by enterprise customers only.
Extended Events
Extended events are a highly configurable architecture used to handle events occurring in SQL Server. They have minimal impact on performance as they are built into the SQL Server code. Extended events make use of packages to group objects together. One of these packages is the SecAudit package, which is used by SQL Audit. Unfortunately, the events in this package are not accessible externally.
Auditing Components
The SQL Server auditing feature encompasses three main components:
- The Server Audit
- The Server Audit Specification
- The Database Audit Specification
The Server Audit
The Server Audit is the parent component of a SQL Server audit and can contain both Server Audit Specifications and/or Database Audit Specifications. It resides in the master database and is used to define where the audit information will be stored, file rollover policy, the queue delay, and how SQL Server should react in case auditing is not possible.
In the audit configuration, you can specify the Server Audit name, the queue delay (which determines whether the audit is processed synchronously or asynchronously), and the action to take in the event that audit logging is unable to continue.
The Server Audit Specification
The Server Audit Specification is used to define what needs to be audited at a server level. It is found under the security node in SQL Server and can be created with SQL Server Management Studio or using Transact SQL. The Server Audit Specification is disabled by default and needs to be enabled individually.
The Database Audit Specification
The Database Audit Specification audits events at a database level. It is unfortunately only available in the Enterprise edition of SQL Server. The Database Audit Specification allows auditing at the object or user level, but not at the column level. It can be created under the Security node of the relevant database.
User Defined Audit Events
One of the new features in SQL Server 2012 is the ability to create User Defined Audit Events. These events can be used to integrate third-party applications with SQL Server Audit. User defined audit events are created using the sp_audit_write procedure, which accepts parameters such as user-defined event ID, success status, and additional information.
Reading Audit File Data
When auditing information is written to a file target, it is done in binary format. The table-valued function fn_get_audit_file() is used to read this data. It accepts parameters such as file pattern, initial file name, and audit record offset.
Securing the Audit Logs
The audit logs themselves need to be protected from unauthorized access and modification. One way to increase security is to write the audit logs to a file server share on a different server to which the sysadmin does not have permission. Another option is to write to the Windows Security log, which requires adding the SQL Server Service account to the Generate Security Audits policy and changing the Audit Object Access policy.
Best Practices
Here are some best practices to consider when implementing SQL Server auditing:
- Write audit logs to a centralized location for easier processing
- Load the logs into a database for better management
- Use a file as a target for optimal performance
- Use targeted auditing to minimize collected data and improve performance
- Ensure the roll-over policy of the Windows logs aligns with your audit strategy
Conclusion
SQL Server Auditing is a powerful feature that should be used with careful planning and consideration. It is essential to have a clear understanding of what needs to be audited, who needs access to the information, and how it will be accessed. Successful auditing also relies on proper storage, processing, and monitoring of the audit data. By following best practices and considering the specific requirements of your organization, SQL Server Auditing can be an effective tool for maintaining data security and compliance.