Auditing SQL Server Database User Activity in AWS RDS: A Step-by-Step Guide

Auditing SQL Server Database User Activity in AWS RDS: A Step-by-Step Guide

Auditing user activity in SQL Server on AWS RDS involves leveraging AWS-native tools combined with SQL Server's built-in features. in this article I provide a detailed guide for setting up and managing auditing:

---

Step 1: Enable SQL Server Audit in AWS RDS

SQL Server Audit is supported on RDS and can track user activity. Here's how to enable and configure it:

1.1 Configure an Audit Parameter Group

1. Log in to the AWS Management Console.
2. Navigate to RDS > Parameter Groups.

3. Create a new parameter group for your SQL Server instance:

   • Choose Parameter group family matching your SQL Server version.
   • Set the name, e.g., sqlserver-audit-group.

4. Edit the parameter group:

   • Search for the parameter rds.sqlserver_audit and set it to 1 (enabled).
   • Save the changes.

5. Associate the parameter group with your RDS instance:

   • Go to RDS Instances and select your SQL Server instance.
   • Modify the instance and change the parameter group to the new one.
   • Apply changes (you may need to reboot the instance for changes to take effect).

---

Step 2: Set Up SQL Server Audit

Once the audit feature is enabled, configure it at the database level.

2.1 Create an Audit Object

This defines where the audit logs will be stored.

USE master;
GO
CREATE SERVER AUDIT AuditToFile
TO FILE (FILEPATH = 'D:\rdsdbdata\SQLAudit\');
GO

2.2 Create an Audit Specification

Define the events to capture in the audit.

CREATE SERVER AUDIT SPECIFICATION AuditUserLogins
FOR SERVER AUDIT AuditToFile
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (FAILED_LOGIN_GROUP);
GO

2.3 Enable the Audit and Specification

ALTER SERVER AUDIT AuditToFile WITH (STATE = ON);
ALTER SERVER AUDIT SPECIFICATION AuditUserLogins WITH (STATE = ON);
GO

---

Step 3: Access and Review Audit Logs

Audit logs for RDS SQL Server are stored in the default directory (D:\rdsdbdata\SQLAudit\) and can be accessed via the AWS Management Console.

1. Navigate to RDS > Your Instance > Logs and Events.
2. Locate logs with the prefix SQL_AUDIT_LOG.
3. Download the logs to review them locally.

Alternatively, query the logs directly using the SQL Server function:

SELECT * 
FROM sys.fn_get_audit_file ('D:\rdsdbdata\SQLAudit\*.sqlaudit', DEFAULT, DEFAULT);

---

Step 4: Use CloudWatch for Enhanced Monitoring

Integrate SQL Server activity logs with AWS CloudWatch for centralized monitoring and alerting.

4.1 Enable Enhanced Monitoring

1. In the RDS Console, go to your SQL Server instance.
2. Enable Enhanced Monitoring and set the monitoring interval.

4.2 Stream Audit Logs to CloudWatch

1. Navigate to RDS > Log Exports.
2. Enable SQL Server Audit Logs for export to CloudWatch.
3. In CloudWatch, create a log group and associate the logs with it.

4.3 Set Up CloudWatch Alerts

1. Create a metric filter for specific events (e.g., failed logins).
2. Configure an alarm to notify you when thresholds are breached.

---

Step 5: Query User Activity with Dynamic Management Views (DMVs)

Leverage SQL Server DMVs to query real-time user activity.

5.1 Track Active Sessions

SELECT session_id, login_name, host_name, program_name, database_id
FROM sys.dm_exec_sessions
WHERE is_user_process = 1;

5.2 Review Recent Logins

SELECT login_time, session_id, login_name, client_net_address
FROM sys.dm_exec_connections
JOIN sys.dm_exec_sessions
ON sys.dm_exec_connections.session_id = sys.dm_exec_sessions.session_id;

5.3 Monitor Query Activity

SELECT r.session_id, s.login_name, s.host_name, t.text AS query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
JOIN sys.dm_exec_sessions s
ON r.session_id = s.session_id;

---

Step 6: Automate Alerts and Notifications

6.1 Use Event Notifications

Set up event notifications for specific actions, such as failed logins or schema changes.

CREATE EVENT NOTIFICATION FailedLoginAlert
ON SERVER
FOR FAILED_LOGIN
TO SERVICE 'MyService'
GO

6.2 Configure Alerts in AWS RDS

Use the AWS EventBridge to trigger actions (e.g., email notifications) for specific RDS events.

---

Step 7: Best Practices for RDS SQL Server Auditing

1. Minimize Audit Overhead:
   • Audit only the necessary events to reduce performance impact.
2. Secure Audit Logs:
   • Restrict access to audit logs in RDS and CloudWatch.
3. Regularly Review Logs:
   • Analyze audit logs periodically for anomalies or suspicious activity.
4. Automate Responses:
   • Use AWS automation tools to handle critical events like repeated failed logins.
5. Enable Encryption:
   • Ensure audit logs and database communications are encrypted.

---

Conclusion

Auditing user activity in SQL Server on AWS RDS combines SQL Server’s robust auditing features with AWS's monitoring and alerting capabilities. By following this step-by-step guide, you can ensure a secure, compliant, and well-monitored SQL Server environment. Regular audits help mitigate risks, detect anomalies, and maintain database integrity, which is essential for modern, data-driven organizations.