FAQ

This section provides answers to frequently asked questions about auditing SQL Server instances on Amazon RDS and Amazon EC2.

What are the main components of the SQL Server audit feature?

The SQL Server audit feature has three main components:

• SQL Server Audit objects define the path to store the audit information, the auditing synchronization mode, the audit file rollover mechanism, and the action to be performed in case of audit failures.

• Server audit specifications track and log the changes that are performed at the SQL Server instance level and events that are raised by the SQL Server Extended Events feature.

• Database audit specifications track and log different types of actions that are performed at the database level and events that are raised by the SQL Server Extended Events feature.

What are some critical events I should consider auditing?

Critical events include failed logins, login changes, user changes, schema changes, and audit changes.

Why is it important to audit failed logins, login changes, and user changes?

For example, excessive failed login attempts or user permission changes might signal that an attack is in progress.

Why is it important to audit schema changes?

We recommend that you track all database schema changes to detect any changes that weren't authorized.

Why is it important to audit the auditing system?

Auditing the changes in your SQL Server auditing solution helps you catch unauthorized users who might be trying to disable the auditing process to perform non-compliant or illegal activities. This audit also helps you meet auditor requirements for the integrity of audit solution logs by providing evidence that covers all scenarios. Another simple use for this audit is to remind the database administrator to reenable the audit in case it was disabled for maintenance purposes.

How can I use triggers to audit database changes?

You can create triggers on tables that contain critical data to log modified or inserted data, and to compare the data before and after the modification. You can use the INSTEAD OF trigger to prevent changes on a specific table and to log the failed action.

What are the advantages and disadvantages of using CDC to audit database changes? Which versions support CDC?

Change data capture (CDC) is supported in all editions of SQL Server 2016 and later. In earlier versions, only the Enterprise edition supports CDC.

Here are some of the advantages of using CDC to audit database changes:

• You can use CDC as an asynchronous SQL Server audit solution, to track data manipulation language (DML) operations on tables.

• CDC tracks INSERT, UPDATE, and DELETE operations on database tables, and records detailed information about these changes in mirrored tables.

• CDC depends on the SQL Server transaction log as the source of data changes.

• You can easily configure CDC by using Transact-SQL commands.

Disadvantages:

• CDC doesn't handle data definition language (DDL) changes on CDC-enabled tables automatically. It requires extra effort to reflect DDL changes in the tracking table.

• CDC provides no option to track the SELECT statement.

• SQL Server keeps CDC tracking data in the change table for only a configurable number of days.

• CDC jobs will not work unless the SQL Server agent service is running.