SQL Server auditing

What is SQL Server auditing?

SQL Server auditing involves tracking and reviewing all activities happening on SQL Servers to detect potential threats and vulnerabilities. In simple terms, it allows you to monitor and record every change to the server settings. Additionally, you can easily keep track of all server activities, like who modified what values, in a specific table in the database. By analyzing suspicious log events, you can identify unauthorized access to the network.

How is auditing different from SQL Server logging?

SQL Server logging involves the recording of various system events, errors, and transactional activities within the SQL Server environment, aiding in troubleshooting, performance analysis, and disaster recovery. On the other hand, SQL Server auditing focuses specifically on tracking and monitoring user actions, security-related events, and compliance-related activities to ensure data security, regulatory compliance, and accountability. While logging captures a broad range of system activities, auditing is more targeted towards user actions and security events, providing detailed insights into who accessed the database, what actions were performed, and when they occurred.

SQL Server auditing types

There are two types of SQL Server auditing:

Server-level auditing

Server-level auditing in SQL Server involves tracking events and actions at the server level. This type of auditing captures activities that occur across the entire SQL Server instance, including logins, configuration changes, and security-related events. Server-level auditing provides administrators with a comprehensive view of server activities and helps them detect potential security threats and compliance violations.

Server-level audit action groups categorize auditable events into logical groups, making it easier to configure auditing settings based on specific requirements. Some common server-level audit action groups include: AUDIT_CHANGE_GROUP, APPLICATION_ROLE_CHANGE_PASSWORD_GROUP, and SECURITY_CHANGE_GROUP.

By configuring server-level audit specifications, administrators can define the scope of auditing, specify audit actions to be tracked, and determine where audit logs are stored.

Database-level auditing

Database-level auditing focuses on monitoring activities within specific databases hosted on the SQL Server instance. This type of auditing allows administrators to track data access, modifications, schema changes, and other database-specific events. Database-level auditing provides granular visibility into database activities, helping in troubleshooting issues, ensuring data integrity, and meeting compliance requirements.

Database-level audit action groups categorize auditable events related to database operations, login attempts, and other database-specific activities. Common database-level audit action groups include: DATABASE_OPERATION_GROUP, DATABASE_LOGON_GROUP, and SCHEMA_OBJECT_ACCESS_GROUP.

By configuring database-level audit specifications, administrators can tailor auditing settings to specific databases, enabling targeted monitoring of critical data assets and sensitive operations.

SQL Server auditing features

SQL Server auditing offers a range of features to meet diverse auditing requirements and regulatory compliance standards. These features enable organizations to define what to audit, configure server settings and schema changes, and track data modifications effectively. Here is more information on some key SQL Server auditing features:

C2 auditing

C2 auditing is a security standard defined by the U.S. Department of Defense (DoD) for evaluating the security features and capabilities of computer systems. It focuses on recording detailed information beyond SQL Server activities, including who triggered the event, which database was affected, the server name, event type, and the outcome of the event. C2 auditing provides comprehensive audit trails for security analysis and compliance purposes.

Common Criteria compliance

Common Criteria is an internationally recognized set of guidelines for evaluating and certifying the security features of IT products and systems. SQL Server offers features that enable compliance with Common Criteria requirements, including residual information protection, login statistics viewing, and ensuring that table-level DENY permissions take precedence over column-level GRANT permissions. Common Criteria compliance ensures adherence to rigorous security standards and regulatory requirements.

Login auditing

Login auditing in SQL Server monitors user authentication and login activities, capturing both successful and failed login attempts. This feature writes login-related events to the SQL Server error log, providing administrators with visibility into user access patterns, authentication failures, and potential security threats. Login auditing helps organizations identify unauthorized access attempts and enforce access controls effectively.

SQL Trace

SQL Trace is an event-driven monitoring tool in SQL Server that captures and records user activities and system events. It allows administrators to define custom trace events and filters to capture specific types of activities, such as query executions, database modifications, and error conditions. SQL Trace provides detailed information for performance analysis, troubleshooting, and auditing purposes.

Extended Events

Extended Events is a lightweight and highly configurable event-handling framework introduced in SQL Server. It offers a flexible mechanism for capturing and analyzing a wide range of events and performance metrics. Extended Events can be used for auditing purposes by defining event sessions to capture specific events, actions, and conditions of interest. It provides granular control over event collection and minimizes performance overhead.

Change Data Capture (CDC)

CDC is a feature in SQL Server that tracks and records all insert, update, and delete activities performed on tables in a database. CDC captures changes to data at the row level and stores them in change tables, allowing administrators to track historical data modifications accurately. CDC is particularly useful for auditing data changes, data replication, and data integration scenarios.

Data Manipulation Language (DML), Data Definition Language (DDL), and login triggers

SQL Server supports triggers for DML, DDL, and login events. Triggers are programmable database objects that automatically execute in response to specified events, such as data modifications (e.g., insert, update, delete), schema changes (e.g., create, alter, drop), and login or logout actions. Triggers can be used for auditing purposes to enforce business rules, capture audit trail information, and regulate database operations effectively.

How to perform SQL Server auditing

These are the components of SQL Server auditing:

• SQL Server Audit object
• Audit specifications
• Target

An SQL Server Audit object is an object created in the audited SQL Server instance. You define the target for monitoring events at the server and database levels. You can define multiple audits for a single SQL Server instance, and each audit stores audit information in a separate target file. The destination type (e.g., binary file, security protocol, or application protocol) and path are defined. The audit does not specify what was recorded, like event type, object, or database.

Now, let's create an SQL Server Audit event.

Creating an SQL Server Audit

• The first step is to create an audit and define the target. There are two methods for creating an SQL Server Audit.

  • Using SQL Server Management Studio (GUI features): In the Object Explorer panel, expand the Security folder, right-click Audits, and select New Audit.

  • Using Transact-SQL commands:

    CREATE SERVER AUDIT [ZohoAudit_DDL_Access] TO FILE
(
FILEPATH = N'C:\TestAudits\'
,MAXSIZE = 10 MB
)
WITH
(
QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)
ALTER SERVER AUDIT [ZohoAudit_DDL_Access]WITH (STATE = ON)
GO

• Choose either of the two audit specifications: server audit specification or database audit specification.
• Enable the audit specification.
• Read the audit events by using Windows Event Viewer and Log File Viewer.

Creating an SQL Server Audit object

• Open SQL Server Management Studio.
• Click Create Audit to create an SQL Server Audit, which will open the Create Audit dialog box.
• Provide a name for your audit in the Audit name field. Choose the Audit destination (preferably File).
• Choose the File path for the audit.
• Set the Maximum file size to 1000 (in MB, GB, TB, or Unlimited).
• Check the box for Reserve disk space.

Viewing audit logs

• Open SQL Server Management Studio.
• In the Object Explorer panel, expand the Security folder.
• Right-click the audit object to view the audit log report.

SQL Server Audit offers a built-in tool called Log File Viewer, designed to simplify the customization and filtering of log entries. Typically, all SQL Server audit files are stored in the SQLAUDIT format, which is difficult to read. However, with Log File Viewer, you can utilize the LOG file format feature to save and export audit reports in a more accessible format.

The best way to mitigate the risk of data leaks is to audit your SQL Server database proactively. Apart from monitoring, auditing, notifying about, and reporting on the critical changes made on the server, SQL Server Audit also offers comprehensive SQL Server security insights. With these, IT administrators can take appropriate action to enhance their enterprise-wide network data security through the powerful features provided by SQL Server Audit.