MSSQL server audit

Enable MSSQL Server Auditing on AWS RDS

To enable MSSQL server auditing on AWS RDS, please follow these steps:

1. Login to AWS console and go to RDS.
2. Create an option group under RDS > Option groups
3. Give name as 'SqlServerAudit'
4. Provide description as 'Option group for SQL Server audit'
5. Choose the engine as same as the one used while creating the database in RDS.
6. Choose the latest engine version. Save.
7. Go to RDS > Option groups and select the radio button on the option group you just created
8. Click on Add Option.
9. Select SQLSERVER_AUDIT under “Option name”
10. Choose the S3 bucket name where you want to keep the audit files once they grow more than the specified limit.
11. Choose the appropriate IAM role with write access to the S3 bucket.
12. Scheduling > Immediately and then click on “Add Option“.
13. Go to RDS > Databases and select the database where you want to attach the auditing. Click on Modify button
14. Go to Database options and then in the “Option Group“ dropdown select the option group created in step 5.
15. Continue, apply immediately and then click on Modify DB instance.

SQL auditing logs will now be uploaded to the S3 bucket.

After doing the above setup, enable audits at the server and database level by performing the following steps on the database (master):

1. Provide permissions to the user who is going to create audits
USE master;
GRANT ALTER ANY SERVER AUDIT TO <user_creating_auditing>;

USE <database>;
GRANT ALTER ANY DATABASE AUDIT TO <user_creating_auditing>
2. Define SQL Server Audit
CREATE SERVER AUDIT [TestDBAudit] TO FILE
(      FILEPATH = N'D:\rdsdbdata\SQLAudit\'
      ,MAXSIZE = 10 MB
)
WITH
      (QUEUE_DELAY = 1000
      ,ON_FAILURE = CONTINUE
)
ALTER SERVER AUDIT [TestDBAudit] WITH (STATE = ON)
3. Define server audit specifications
CREATE SERVER AUDIT SPECIFICATION [SERVER_LEVEL_AUDIT_SPEC]
FOR SERVER AUDIT [TestDBAudit]
ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP), 
ADD (AUDIT_CHANGE_GROUP),
... (other action groups)
WITH (STATE = ON)
4. Define database audit specifications
use <the_db_to_use>;
CREATE DATABASE AUDIT SPECIFICATION [TEST_DB_AUDIT_SPEC]
FOR SERVER AUDIT [TestDBAudit]
ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP) ,
ADD (AUDIT_CHANGE_GROUP),
... (other action groups)
WITH (STATE = ON)

To view the audit logs on RDS, run the following query. The logs will be returned until they are archived and uploaded to the S3 bucket (step 9 above):

SELECT * FROM msdb.dbo.rds_fn_get_audit_file
('D:\rdsdbdata\SQLAudit\*.sqlaudit' , default , default )

The audit logs will be pushed to the configured S3 bucket as soon as they reach the configured size (10 MB in our case). You can download the logs from there afterward.

Note: There is no additional performance overhead by enabling the audits. Refer to Microsoft forums for more details: Performance Impact for SQL Server Audit and Microsoft Documentation.