Auditing Amazon RDS for SQL Server DB instances - AWS Prescriptive Guidance
PrerequisitesSupported versionsUsing C2 audit modeCreating and viewing auditsMonitoring

Auditing Amazon RDS for SQL Server DB instances

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

Prerequisites

  • An Amazon Simple Storage Service (Amazon S3) bucket for storing the audit files

  • An AWS Identity and Access Management (IAM) role for accessing the S3 bucket

  • A database login with the ALTER ANY SERVER AUDIT or CONTROL SERVER permission

Supported versions

  • For Amazon RDS for SQL Server 2014, all editions support server-level audits. Enterprise edition also supports database-level audits.

  • Starting with SQL Server 2016 (13.x) SP1, all editions support both server-level and database-level audits.

  • Amazon RDS currently supports SQL Server audits in all AWS Regions except Middle East (Bahrain). For the latest information, see Support for SQL Server Audit in the Amazon RDS documentation.

Using C2 audit mode

C2 audit mode is a parameter in the Amazon RDS for SQL Server DB parameter group. It is disabled by default. You can enable it by updating the parameter value to 1. When C2 audit mode is enabled, it 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.

Important

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

Creating and viewing audits

You can audit Amazon RDS for SQL Server databases by using built-in SQL Server audit mechanisms that involve creating audits and audit specifications. 

  • Audit logs are uploaded to an S3 bucket by using an IAM role that has the necessary permissions to access the bucket. 

  • You can choose the IAM role, the S3 bucket, compression, and the retention period when you create the option group. The maximum retention period time is 35 days.

  • You create the option group and attach it to a new or existing Amazon RDS for SQL Server DB instance.  The audit logs are stored in D:\rdsdbdata\SQLAudit. 

  • After SQL Server finishes writing to an audit log file or when the file reaches its size limit, Amazon RDS uploads the file to your S3 bucket.

  • If you enable retention, Amazon RDS moves the file into the retention folder at D:\rdsdbdata\SQLAudit\transmitted. Audit records are kept on the DB instance until the audit log file is uploaded. 

  • You can also find audit records by querying for dbo.rds_fn_get_audit_file. 

For Multi-AZ instances, the database audit specification objects are replicated to all nodes.  Server audit and server audit specifications aren't replicated to all nodes, so you should create these manually.

Configuring the option group

Follow these steps to configure an option group to perform a SQL Server audit on your Amazon RDS for SQL Server DB instance. For detailed instructions, see SQL Server Audit in the Amazon RDS documentation.

  • Create an option group.

  • Add the SQLSERVER_AUDIT option to the option group.

  • For S3 destination, create a new bucket or select an existing bucket for the audit logs.

  • For IAM role, create a new role or choose an existing role with the required policies. For more information, see Manually creating an IAM role for SQL Server Audit in the IAM documentation.

  • Expand Additional information and select Enable compression to compress audit logs (recommended).

  • To keep the audit logs for the DB instance, select Enable retention and specify a retention period (up to a maximum of 35 days).

  • Apply the options group to a new or existing Amazon RDS for SQL Server DB instance.

    • For a new DB instance, apply the option group when you launch the instance.

    • For an existing DB instance, modify the instance and attach the option group.

Creating audits

To create a server audit, use the following script. This script creates the audit file in the file path that you specify. For syntax, arguments, and examples, see the Microsoft SQL Server documentation. To avoid errors, review the list of limitations listed in Amazon RDS documentation.

--Creating the server audit
 use master
 GO
 CREATE SERVER AUDIT [Audit-<<servername>>]
 TO FILE  ( FILEPATH = N'D:\rdsdbdata\SQLAudit', MAXSIZE = 2 MB, RESERVE_DISK_SPACE = OFF)
 WITH ( QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE)
 GO
-- Enabling the server audit  
 ALTER SERVER AUDIT [Audit-<<servername>>] WITH (STATE = ON) ;
 GO

Creating audit specifications

After you create a server audit, you can record server-level events by creating a server audit specification with the following code. This specification determines what will be checked during the server audit. For syntax, arguments, and examples, see the Microsoft SQL Server documentation. The following specification audits failed login actions and tracks the creation, alteration, and deletion of server objects. For a list of actions, see the Microsoft SQL Server documentation.

--Creating server audit specification
 USE [master]
 GO
 CREATE SERVER AUDIT SPECIFICATION [Audit-Spec-<<servername>>]
 FOR SERVER AUDIT  [Audit-<<servername>>]
 ADD (FAILED_LOGIN_GROUP), ADD (SERVER_OBJECT_CHANGE_GROUP)
 GO
--Enables the audit 
 ALTER SERVER AUDIT [Audit-<<servername>>]  
 WITH (STATE = ON);  
 GO

You can use the following code to create a database audit specification that records database-level events. This example audits INSERT actions. For syntax, arguments, and more examples, see the Microsoft SQL Server documentation.

--Creating database audit specification
 USE [<<DBName>>]
 GO
 
 CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-<<DBName>>]
 FOR SERVER AUDIT [Audit-<<ServerName>>]
 ADD (INSERT ON DATABASE::[<<DBName>>] BY [dbo])
 WITH (STATE = ON)
 GO

Viewing audit logs

Use the following query to view the audit logs. Audit logs are kept in the DB instance until they are uploaded to Amazon S3. If you enable retention for the SQLSERVER_AUDIT option, Amazon RDS moves the file into the retention folder D:\rdsdbdata\SQLAudit\transmitted.

You can also view the audit records in the retention folder by changing the filter to D:\rdsdbdata\SQLAudit\transmitted\*.sqlaudit.

--Viewing audit logs
SELECT   * 
	FROM     msdb.dbo.rds_fn_get_audit_file
	             ('D:\rdsdbdata\SQLAudit\*.sqlaudit'
	             , default
	             , default )
--Viewing audit logs in retention folder
SELECT   * 
	FROM     msdb.dbo.rds_fn_get_audit_file
	             ('D:\rdsdbdata\SQLAudit\transmitted\*.sqlaudit'
	             , default
	             , default )

Additional options for auditing SQL Server databases are discussed in the following AWS and Microsoft documentation:

Monitoring

You can use database activity streams in Amazon RDS to integrate SQL Server audit events with database activity monitoring tools from Imperva, McAfee, and IBM. For more information, see Auditing in Microsoft SQL Server in the Amazon RDS documentation.