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
orCONTROL 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
--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
--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:
-
SQL Server Extended Events: See the AWS blog post Set up Extended Events in Amazon RDS for SQL Server
. -
SQL Server triggers: See Creating a rule that triggers on an Amazon RDS event in the Amazon RDS documentation.
-
Change tracking: See Track data changes
in the Microsoft SQL Server documentation. -
Change data capture: See Using change data capture in the Amazon RDS documentation.
-
C2 audit mode parameter: See c2 audit mode Server Configuration Option
in the Microsoft SQL Server 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.