Auditing SQL Server on Amazon EC2 or Amazon RDS Custom DB instances

This section provides information about auditing options for SQL Server on Amazon EC2 and Amazon RDS Custom, including creating server and database audits, viewing audit logs, and monitoring results.

Prerequisites

• Database login with ALTER ANY SERVER AUDIT or CONTROL SERVER permission

Supported versions

• Any edition of SQL Server version 2016 and later

Using C2 audit mode

C2 audit mode audits events such as user logins, stored procedure calls, and creation and deletion of objects. This mode can generate lot of data because it audits everything or nothing. C2 audit logs are stored in the default data directory of the SQL Server instance. Each log file can be a maximum of 200 MB. A new file is automatically created when this limit is reached. You can enable C2 auditing by using SQL Server Management Studio. For more information, see the Microsoft SQL Server documentation.

Important

Microsoft plans to remove C2 audit mode in a future version of SQL Server. We recommend that you avoid using this feature.

To use C2 audit mode to audit failed logins:

1. In SQL Server Management Studio, connect to the SQL Server instance that you want to enable auditing for.

2. Select the SQL Server instance, right-click and choose Properties, and then choose Security.

3. For Login auditing, choose a configuration option. You can audit failed logins only, successful logins only, both, or none. (The default is failed logins only.)

4. For Options, select Enable C2 audit tracing.

Creating and viewing audits

Creating server audits

A server audit in SQL Server collects instance-level or database-level actions to monitor. Audit output is saved to an audit destination file path, a Windows security log, or an application log.

To create a server audit:

1. In SQL Server Management Studio, in Object Explorer, expand Security, right-click Audits, and then choose New Audit. This creates a new SQL Server Audit object for server-level auditing.

2. For Audit destination, choose a file, a security log, or an application log.

3. If you selected a file as the target, specify the location of the folder.

4. Configure other options, and then choose OK.

5. To enable the audit, right-click the new audit configuration, and then choose Enable Audit.

For more information, see the Microsoft SQL Server documentation.

Creating server audit specifications

The server audit specification collects many server-level action groups raised by the SQL Server Extended Events feature. You can include audit action groups in a server audit specification. These actions are sent to the audit that records them in the target file or log.

To create a server audit specification:

1. In SQL Server Management Studio, in Object Explorer, expand Security, right-click Server Audit Specifications, and then choose New Server Audit Specification.

2. For Audit, choose the server audit you created earlier.

3. For Actions, choose the audit action type that specifies the server-level audit action groups and audit actions that you want to capture, and then choose OK.

4. To enable the server audit specification, right-click the new specification, and then choose Enable Server Audit Specification.

For more information, see Create a Server Audit and Server Audit Specification and SQL Server Audit Action Groups and Actions in the Microsoft SQL Server documentation.

Creating database audit specifications

You can create a database audit specification object for database-level auditing. This specification specifies the database-level audit action groups and audit actions to capture.

To create a database audit specification:

1. In SQL Server Management Studio, in Object Explorer, expand the database that you want to audit.

2. Expand the Security folder, right-click Database Audit Specifications, and then choose New Database Audit Specification.

3. For Actions, configure one or more database audit action types. Select the statements that you want to audit (such as DELETE or INSERT) and the object class to perform the action on.

4. When your selections are complete, choose OK.

5. To enable the database audit specification, right-click the new specification, and then choose Enable Database Audit Specification.

For more information, see Create a server audit and database audit specification and SQL Server Audit Action Groups and Actions in the Microsoft SQL Server documentation.

Viewing SQL Server audit logs

To view audit logs:

1. In SQL Server Management Studio, right-click the SQL Server Audit object, and then choose View Audit Logs.

   The Log File Viewer displays the audit log regardless of its location (a file or the Windows Event Log).

2. To customize the log entries that are displayed, choose Filter.

3. To export the log to a log file, choose Export.

4. When you've finished viewing the log, choose Close.

For more information, see the Microsoft SQL Server documentation.

Monitoring

You can monitor audit logs that are logged to an audit file, an application or security event log, or an audit table in the database by using monitoring solutions such as Nagios. A monitoring solution that's integrated with a ticketing or alerting mechanism can generate real-time alerts and incidents to notify the systems administrator or database administrator.