Monday, April 15, 2024
No menu items!
HomeDatabase ManagementHow to enable and optimize audits on Amazon RDS for SQL Server

How to enable and optimize audits on Amazon RDS for SQL Server

Organizations are usually required by regulations or law to maintain database audit logs to demonstrate compliance for various data privacy and regulatory obligations. For example, the PCI-DSS framework requires financial organizations to keep audit logs for data security and adherence.

In this post, we show how to effectively and efficiently set up audits in Amazon Relational Database Service (Amazon RDS) for SQL Server. We also discuss best practices to optimize your auditing configuration and explore auditing in different use cases. SQL Server provides extensive capabilities to log events to track a wide variety of events from the database to server level.

In addition to compliance, SQL server audit logs help with the following as a reactive measure.

Data integrity – Auditing can help check data integrity and maintain data quality by identifying if data is tampered.
Security – Audit logs can help detect unauthorized access to sensitive data. This information can be used to investigate security incidents and identify potential vulnerabilities in the system.
Accountability – By maintaining a record of all activities, organizations can identify who made changes and when.
Performance optimization – Audit logs also provide insights into how the database is being used and help identify areas for performance optimization.

We can utilize the built-in SQL Server auditing mechanism to enable auditing for Amazon RDS for SQL Server. Amazon RDS allows us to configure a retention period to keep logs to the DB instance, which helps in providing a mechanism to move the completed audit logs on an Amazon Simple Storage Service (Amazon S3) bucket. The logs can later on be fed to any other AWS service or 3rd party tooling for easier consumption. They can also be retained for historical records

Auditing is a resource-intensive task for SQL Server. Non-optimal audit configurations can result in performance bottlenecks. It is critical to have a balance between audit requirements and performance.

Solution overview

Manual configuration for auditing can be time consuming if the database has multiple users and objects. We will showcase how you can automate the process to set up audits in large scale RDS for SQL Server database systems.

In this post we will cover the following:

Steps to Configure Server level Audit and Database level Audit in Amazon RDS for SQL server
Understand the best practice to optimize the compute utilization for Auditing.
How Audit configuration can be limited to specific Objects, Actions and Users.
How to Use TSQL to Automate creation of Database Level Auditing for minimum compute utilization.

We can broadly categorize Auditing in SQL server into two levels:

Server-level audit – This is the configuration done on the instance level. You can configure several instance-level tasks and create server audit specifications with actions to be audited via this audit type. Actions and events that rely on instance-level operations and don’t necessarily include database access are captured here.
Database-level audit – This specifies audit events at the database level. Actions such as table data access, updates, or deletes can be captured using this audit type.

In Amazon RDS, starting with SQL Server 2014, all editions of SQL Server support server-level audits, and the 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.

Prerequisites

Complete the following prerequisite steps:

Create an RDS for SQL Server instance for auditing.
Establish a connection to the RDS instance. For more information, refer to Creating and connecting to a Microsoft SQL Server DB instance.
Create and configure a custom option group with the auditing option. For instructions, refer to Working with option groups.
Create an AWS Identity and Access Management (IAM) role with the ARN in the format arn:aws:iam::account-id:role/role-name.

For more information about the IAM role and S3 bucket settings, refer to Support for SQL Server Audit.

Configure a server-level audit and server audit specification

To create a server-level audit, we need to configure an audit and audit specification. An audit houses the configurations and rules that the engine follows, such as log file location, max file size, and so on. Server level audit specifications contain the actions that the customer can choose to be audited on the entire instance level.

After you create and configure these resources, actions are audited as per the specifications and logged on the OS level as per the audit file.

We also define the storage location and other properties such as file size and failure action.

Create a server-level audit

To create your audit, complete the following steps:

Connect to the instance via SQL Server Management Studio (SSMS).
Expand Security in the navigation pane.
Choose (right-click) Audits and choose New Audit.
For Audit name, enter a name. It can be anything except with RDS_ as the suffix.

For On Audit Log Failure, select an action as per your requirement.

Don’t configure SQL Server to shut down the DB instance if it fails to write the audit record.

For Audit destination, choose Path, and enter the following path to store the audit logs: D:rdsdbdataSQLAudit.

This path cannot be changed when enabling audits in Amazon RDS.

Configure the maximum file size.

Configure MAX_FILES. MAXSIZE between 2–50 MB in case of Amazon RDS.

Choose OK to finish.

The restrictions mentioned in the point above are due to the current limitations on Amazon RDS.

Create a server level audit specification

In the audit specification, we define the events that we want to be logged in the audit data. Complete the following steps:

Connect to the instance via SSMS.
Expand Security in the navigation pane.
Choose (right-click) Server Audit Specifications and choose New Server Audit Specification.
For Name, enter a name.
For Audit¸ choose the audit you created.

Choose the appropriate values for Audit Action Type, Object Class, Object Schema, Object Name¸ and Principal to audit.

There are multiple events that can be captured; you can select events based on your audit requirement. In this post, we discuss a few of the audit action types that can be logged.

Choose OK to finish.

Configure a database-level audit specification

The database audit specification collects database-level audit actions. You can add either audit action groups or audit events to a database audit specification.

You can use the server-level audit configuration to create a database-level audit specification. Complete the following steps:

Connect to the instance via SSMS.
Expand Security in the navigation pane.
Choose (right-click) Database Audit Specifications and choose New Database Audit Specification.
For Name, enter a name.
For Audit¸ choose the audit you created.

Choose the appropriate values for Audit Action Type, Object Class, Object Schema, Object Name and Principal that need to be audited.

Choose OK to finish.

Auditing Best practices

SQL Server provides a variety of actions and events to log. The following are common Audit action types which are frequently utilized in Auditing. Server level auditing are for the entire instance and the database audit specifications are mapped to an individual database. Each server-level audit in RDS for SQL Server can have maximum 1 database audit specification and 1 server audit specification mapped to it

Server-level audit specifications
Database-Level-Auditing specifications

SERVER_OPERATION_GROUP – This event is raised when security audit operations such as altering settings, resources, external access, or authorization are used.

SELECT / UPDATE /EXECUTE / INSERT

DELETE – these event is raised whenever DML is issued by the SQL server engine

FAILED_LOGIN_GROUP – Indicates that a principal tried to log in to SQL Server and failed. Events in this class are raised by new connections or by connections that are reused from a connection pool.
DATABASE_OWNERSHIP_CHANGE_GROUP – This event is raised when you use the ALTER AUTHORIZATION statement to change the owner of a database, and the permissions that are required to do that are selected. This event is raised for any database ownership change on any database on the server.

SUCCESSFUL_LOGIN_GROUP – Indicates that a principal has successfully logged in to SQL Server. Events in this class are raised by new connections or by connections that are reused from a connection pool.
DATABASE_PERMISSION_CHANGE_GROUP – This event is raised whenever a GRANT, REVOKE, or DENY is issued for a statement permission by any principal in SQL Server (this applies to database-only events, such as granting permissions on a database).

Auditing should be carefully configured, especially for instances that have a high level of user activity. In most cases, especially with a large number of database objects, it’s very easy to audit everything. However, that can result in performance bottlenecks. To make the best use of compute and minimal logging, consider the following best practices:

Identify the type of events you need to audit, such as logins, failed logins, or failed access to data. Prioritize the events that are most critical for your business.
Ensure auditing is configured only on databases that are of interest.
Filtering is key to optimizing audits. Configure your audit to capture events only from relevant users.
Select the event type depending on the application use case, such as INSERT, UPDATE, DELETE, or SELECT. You should only audit events that are critical for your use case.
Filter relevant objects. For example, to have auditing enabled on PII data, you might want to filter audit events based on table names that start with the keyword Customer. Alternatively, you can have a requirement to skip all the objects where the table name starts with keywords such as TEMP, BACKUP or ARCHIVE.
You should monitor free space, CPU, Memory with the FreeStorage, CPUUtilization and FreeableMemory Amazon CloudWatch metrics respectively.

Demonstration

In this section we do the following actions in order to demonstrate various methods of audit implementations.

1 – Create a sample database

2 – Automate user-based logging

3 – Automate object-specific logging

4 – Automate action-specific logging

Scripts in point 2, 3 and 4 create separate stored procedures each catering to audit use cases and needs to be modified and executed as per business and auditing requirement.

Create a Sample database

Run the following script on the RDS instance to create a sample database with tables and users, we will utilize this database to demonstrate the Audit automation in next steps.

— Create the database
CREATE DATABASE sample_db;
GO

— Use the sample database
USE sample_db;
GO

— Drop the procedure if it exists
IF OBJECT_ID(‘Create_sample_database_with_usrs’, ‘P’) IS NOT NULL
DROP PROCEDURE Create_sample_database_with_usrs;
GO

— Create the procedure
CREATE PROCEDURE Create_sample_database_with_usrs
AS
BEGIN
DECLARE @i INT = 1;
DECLARE @table_name NVARCHAR(50);
DECLARE @user_name NVARCHAR(50);
DECLARE @sql NVARCHAR(MAX);

— Create customer tables
WHILE @i <= 3
BEGIN
SET @table_name = CONCAT(‘customer_’, CAST(@i AS NVARCHAR));
SET @sql = CONCAT(‘CREATE TABLE ‘, @table_name, ‘ (id INT PRIMARY KEY, name NVARCHAR(50));’);
EXEC sp_executesql @sql;
SET @i = @i + 1;
END;
SET @i = 1;

— Create payment tables
WHILE @i <= 2
BEGIN
SET @table_name = CONCAT(‘payment_’, CAST(@i AS NVARCHAR));
SET @sql = CONCAT(‘CREATE TABLE ‘, @table_name, ‘ (id INT PRIMARY KEY, amount DECIMAL(10, 2));’);
EXEC sp_executesql @sql;
SET @i = @i + 1;
END;

SET @i = 1;

— Create archive tables
WHILE @i <= 4
BEGIN
SET @table_name = CONCAT(‘archive_’, CAST(@i AS NVARCHAR));
SET @sql = CONCAT(‘CREATE TABLE ‘, @table_name, ‘ (id INT PRIMARY KEY, archived_date DATE);’);
EXEC sp_executesql @sql;
SET @i = @i + 1;
END;
SET @i = 1;

— Create users

WHILE @i <= 3
BEGIN
SET @user_name = CONCAT(‘user’, CAST(@i AS NVARCHAR));
SET @sql = CONCAT(‘CREATE LOGIN ‘, @user_name, ‘ WITH PASSWORD = ”password@123”;’);
EXEC sp_executesql @sql;
SET @sql = CONCAT(‘CREATE USER ‘, @user_name, ‘ FOR LOGIN ‘, @user_name, ‘;’);
EXEC sp_executesql @sql;
SET @sql = CONCAT(‘GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA :: [dbo] TO ‘, @user_name, ‘;’);
EXEC sp_executesql @sql;
SET @i = @i + 1;
END;
END;
GO

— Call the procedure to generate tables
EXEC Create_sample_database_with_usrs;
GO

Automate User-based logging

It’s not always required to audit all the users in the database. Some users are required to access sensitive data—for example, an application user that writes or reads the sensitive data. Those users can be excluded in the filtering process. To achieve this, complete the following steps:

In SSMS, expand the databases on which the audit needs to be created.
Expand the Security menu.
Choose (right-click) Database Audit Specification and choose Properties.
Choose the options icon (three dots) next to the row for the column principal name and choose the user that needs to be audited.

Manual configuration for this procedure can be time consuming if the instance has hundreds of users and databases. In such cases, you can automate this task through T-SQL. Use the following script to automate this audit configuration:

USE sample_db
go
— Create the procedure for custom auditing
IF OBJECT_ID(‘dbo.Custom_User_Auditing’, ‘P’) IS NOT NULL
DROP PROCEDURE dbo.Custom_User_Auditing;
GO

CREATE PROCEDURE dbo.Custom_User_Auditing
@audit_specification_name NVARCHAR(MAX),
@user_exclude NVARCHAR(MAX)
AS
BEGIN
DECLARE @db_cursor2 CURSOR;
DECLARE @db_cursor CURSOR;
DECLARE @user_name VARCHAR(100);
DECLARE @table_name VARCHAR(100);
DECLARE @alter_statement VARCHAR(500);
DECLARE @actiontoinclude VARCHAR(500);

— Open cursor for users
SET @db_cursor2 = CURSOR FOR
SELECT name
FROM sys.database_principals
WHERE type NOT IN (‘A’, ‘G’, ‘R’, ‘X’)
AND sid IS NOT NULL
AND name != ‘guest’
AND name != ‘dbo’
AND name != @user_exclude;

OPEN @db_cursor2;

FETCH NEXT FROM @db_cursor2 INTO @user_name;

WHILE @@FETCH_STATUS = 0
BEGIN
— Open cursor for tables
SET @db_cursor = CURSOR FOR
SELECT name
FROM sys.dm_db_index_operational_stats(db_id(), NULL, NULL, NULL), sysobjects
WHERE object_id = id
AND xtype = ‘U’

OPEN @db_cursor;

FETCH NEXT FROM @db_cursor INTO @table_name;

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @table_name;

SET @actiontoinclude = ‘SELECT’

SET @alter_statement = ‘Alter DATABASE AUDIT SPECIFICATION ‘ + QUOTENAME(@audit_specification_name) + ‘ ADD (‘ + @actiontoinclude + ‘ ON OBJECT::[dbo].’ + @table_name + ‘ BY ‘ + @user_name + ‘)’;
PRINT @alter_statement;

EXEC (@alter_statement);
SET @actiontoinclude = ‘INSERT’
SET @alter_statement = ‘ALTER DATABASE AUDIT SPECIFICATION ‘ + QUOTENAME(@audit_specification_name) + ‘ ADD (‘ + @actiontoinclude + ‘ ON OBJECT::[dbo].’ + @table_name + ‘ BY ‘ + @user_name + ‘)’;
PRINT @alter_statement;

EXEC (@alter_statement);

SET @actiontoinclude = ‘UPDATE’
SET @alter_statement = ‘ALTER DATABASE AUDIT SPECIFICATION ‘ + QUOTENAME(@audit_specification_name) + ‘ ADD (‘ + @actiontoinclude + ‘ ON OBJECT::[dbo].’ + @table_name + ‘ BY ‘ + @user_name + ‘)’;
PRINT @alter_statement;

EXEC (@alter_statement);
SET @actiontoinclude = ‘DELETE’
SET @alter_statement = ‘ALTER DATABASE AUDIT SPECIFICATION ‘ + QUOTENAME(@audit_specification_name) + ‘ ADD (‘ + @actiontoinclude + ‘ ON OBJECT::[dbo].’ + @table_name + ‘ BY ‘ + @user_name + ‘)’;
PRINT @alter_statement;

EXEC (@alter_statement);

FETCH NEXT FROM @db_cursor INTO @table_name;
END

CLOSE @db_cursor;
DEALLOCATE @db_cursor;

FETCH NEXT FROM @db_cursor2 INTO @user_name;
END

CLOSE @db_cursor2;
DEALLOCATE @db_cursor2;
END
GO

USE master
GO

— Create the database audit specification
DECLARE @audit_specification_name NVARCHAR(MAX);
SET @audit_specification_name = ‘Audits_’ + REPLACE(REPLACE(CONVERT(NVARCHAR(30), GETDATE(), 120), ‘-‘, ”), ‘ ‘, ‘_’);
PRINT @audit_specification_name

IF NOT EXISTS (SELECT * FROM sys.server_audits WHERE name = @audit_specification_name)
BEGIN
EXEC(‘CREATE SERVER AUDIT [‘ + @audit_specification_name + ‘]
TO FILE
(
FILEPATH = N”D:rdsdbdataSQLAudit”,
MAXSIZE = 20 MB,
MAX_ROLLOVER_FILES = 2147483647
)
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE)’);

EXEC(‘ALTER SERVER AUDIT [‘ + @audit_specification_name + ‘] WITH (STATE = ON)’);
END

use sample_db
DECLARE @audit_specification_db_name NVARCHAR(MAX);
SET @audit_specification_db_name = ‘DatabaseAuditSpecification_’ + REPLACE(REPLACE(CONVERT(NVARCHAR(30), GETDATE(), 120), ‘-‘, ”), ‘ ‘, ‘_’);
PRINT @audit_specification_db_name

DECLARE @sql NVARCHAR(MAX);
SET @sql = ‘CREATE DATABASE AUDIT SPECIFICATION [‘ + @audit_specification_db_name + ‘] FOR SERVER AUDIT [‘ + @audit_specification_name + ‘];’

EXEC sp_executesql @sql;

exec sample_db.dbo.Custom_User_Auditing @audit_specification_db_name, user1
Go

Automate Object-specific logging

Make sure to include only the required tables and objects in auditing. Tables and objects that aren’t important to your use case should be excluded to avoid overburdening the engine and the I/O subsystem of SQL Server.

You can use the following script to automate this audit configuration:

USE sample_db
go

— Create the procedure for custom auditing
IF OBJECT_ID(‘dbo.Custom_Object_Auditing’, ‘P’) IS NOT NULL
DROP PROCEDURE dbo.Custom_Object_Auditing;
GO

CREATE PROCEDURE dbo.Custom_Object_Auditing
@audit_specification_name NVARCHAR(MAX),
@table_exclude NVARCHAR(MAX),
@actiontoinclude NVARCHAR(MAX)
AS
BEGIN
DECLARE @db_cursor2 CURSOR;
DECLARE @db_cursor CURSOR;
DECLARE @user_name VARCHAR(100);
DECLARE @table_name VARCHAR(100);
DECLARE @alter_statement VARCHAR(500);
DECLARE @user_exclude as VARCHAR(30)

— Open cursor for users
SET @db_cursor2 = CURSOR FOR
SELECT name
FROM sys.database_principals
WHERE type NOT IN (‘A’, ‘G’, ‘R’, ‘X’)
AND sid IS NOT NULL
AND name != ‘guest’
AND name != ‘dbo’
OPEN @db_cursor2;
FETCH NEXT FROM @db_cursor2 INTO @user_name;
WHILE @@FETCH_STATUS = 0
BEGIN

— Open cursor for tables
SET @db_cursor = CURSOR FOR
SELECT name
FROM sys.dm_db_index_operational_stats(db_id(), NULL, NULL,
NULL), sysobjects
WHERE object_id = id
AND xtype = ‘U’
AND name != @table_exclude;
OPEN @db_cursor;
FETCH NEXT FROM @db_cursor INTO @table_name;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @table_name;
SET @alter_statement = ‘ALTER DATABASE AUDIT SPECIFICATION ‘ +
QUOTENAME(@audit_specification_name) + ‘ ADD (‘ + @actiontoinclude + ‘ ON
OBJECT::[dbo].’ + @table_name + ‘ BY ‘ + @user_name + ‘)’;

PRINT @alter_statement;
EXEC (@alter_statement);

FETCH NEXT FROM @db_cursor INTO @table_name;
END
CLOSE @db_cursor;
DEALLOCATE @db_cursor;
FETCH NEXT FROM @db_cursor2 INTO @user_name;
END

CLOSE @db_cursor2;
DEALLOCATE @db_cursor2;
END
GO

USE master
GO

— Create the database audit specification
DECLARE @audit_specification_name NVARCHAR(MAX);
SET @audit_specification_name = ‘Audits_’ + REPLACE(REPLACE(CONVERT(NVARCHAR(30), GETDATE(), 120), ‘-‘, ”), ‘ ‘, ‘_’);
PRINT @audit_specification_name
IF NOT EXISTS (SELECT * FROM sys.server_audits WHERE name = @audit_specification_name)
BEGIN
EXEC(‘CREATE SERVER AUDIT [‘ + @audit_specification_name + ‘]
TO FILE
(
FILEPATH = N”D:rdsdbdataSQLAudit”,
MAXSIZE = 20 MB,
MAX_ROLLOVER_FILES = 2147483647
)

WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE)’);
EXEC(‘ALTER SERVER AUDIT [‘ + @audit_specification_name + ‘] WITH (STATE = ON)’);
END

use sample_db
DECLARE @audit_specification_db_name NVARCHAR(MAX);
SET @audit_specification_db_name = ‘DatabaseAuditSpecification_’ + REPLACE(REPLACE(CONVERT(NVARCHAR(30), GETDATE(), 120), ‘-‘, ”), ‘ ‘, ‘_’);
PRINT @audit_specification_db_name
DECLARE @sql NVARCHAR(MAX);
SET @sql = ‘CREATE DATABASE AUDIT SPECIFICATION [‘ + @audit_specification_db_name + ‘] FOR SERVER AUDIT [‘ + @audit_specification_name + ‘];’
EXEC sp_executesql @sql;
exec sample_db.dbo.Custom_Object_Auditing @audit_specification_db_name, ‘archive_2’, ‘SELECT’
Go

Automate Action-specific logging

In this approach, a particular action needs to be logged irrespective of which user performs it or on which database object. For example, in the case of PII data, SELECT can be audited. If there is FinOps data, DML operations can be audited.

You can use the following script to automate this audit configuration:

USE sample_db
go

— Create the procedure for custom auditing
IF OBJECT_ID(‘dbo.Custom_Action_Auditing’, ‘P’) IS NOT NULL
DROP PROCEDURE dbo.Custom_Action_Auditing;
GO

CREATE PROCEDURE dbo.Custom_Action_Auditing
@audit_specification_name NVARCHAR(MAX),
@actiontoinclude NVARCHAR(MAX)
AS
BEGIN
DECLARE @db_cursor2 CURSOR;
DECLARE @db_cursor CURSOR;
DECLARE @user_name VARCHAR(100);
DECLARE @table_name VARCHAR(100);
DECLARE @alter_statement VARCHAR(500);
DECLARE @table_exclude NVARCHAR(MAX);
DECLARE @user_exclude NVARCHAR(MAX);

— Open cursor for users
SET @db_cursor2 = CURSOR FOR
SELECT name
FROM sys.database_principals
WHERE type NOT IN (‘A’, ‘G’, ‘R’, ‘X’)
AND sid IS NOT NULL
AND name != ‘guest’
AND name != ‘dbo’
OPEN @db_cursor2;
FETCH NEXT FROM @db_cursor2 INTO @user_name;
WHILE @@FETCH_STATUS = 0
BEGIN

— Open cursor for tables
SET @db_cursor = CURSOR FOR
SELECT name
FROM sys.dm_db_index_operational_stats(db_id(), NULL, NULL, NULL),
sysobjects
WHERE object_id = id
AND xtype = ‘U’
OPEN @db_cursor;
FETCH NEXT FROM @db_cursor INTO @table_name;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @table_name;
SET @alter_statement = ‘ALTER DATABASE AUDIT SPECIFICATION ‘ +
QUOTENAME(@audit_specification_name) + ‘ ADD (‘ + @actiontoinclude + ‘ ON
OBJECT::[dbo].’ + @table_name + ‘ BY ‘ + @user_name + ‘)’;
PRINT @alter_statement;
EXEC (@alter_statement);
FETCH NEXT FROM @db_cursor INTO @table_name;
END

CLOSE @db_cursor;
DEALLOCATE @db_cursor;
FETCH NEXT FROM @db_cursor2 INTO @user_name;
end

CLOSE @db_cursor2;
DEALLOCATE @db_cursor2;
END
GO

USE master
GO

— Create the database audit specification
DECLARE @audit_specification_name NVARCHAR(MAX);
SET @audit_specification_name = ‘Audits_’ + REPLACE(REPLACE(CONVERT(NVARCHAR(30), GETDATE(), 120), ‘-‘, ”), ‘ ‘, ‘_’);
PRINT @audit_specification_name
IF NOT EXISTS (SELECT * FROM sys.server_audits WHERE name = @audit_specification_name)
BEGIN
EXEC(‘CREATE SERVER AUDIT [‘ + @audit_specification_name + ‘]
TO FILE
(
FILEPATH = N”D:rdsdbdataSQLAudit”,
MAXSIZE = 20 MB,
MAX_ROLLOVER_FILES = 2147483647
)
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE)’);
EXEC(‘ALTER SERVER AUDIT [‘ + @audit_specification_name + ‘] WITH (STATE = ON)’);
END

use sample_db
DECLARE @audit_specification_db_name NVARCHAR(MAX);
SET @audit_specification_db_name = ‘DatabaseAuditSpecification_’ + REPLACE(REPLACE(CONVERT(NVARCHAR(30), GETDATE(), 120), ‘-‘, ”), ‘ ‘, ‘_’);
PRINT @audit_specification_db_name
DECLARE @sql NVARCHAR(MAX);
SET @sql = ‘CREATE DATABASE AUDIT SPECIFICATION [‘ + @audit_specification_db_name + ‘] FOR SERVER AUDIT [‘ + @audit_specification_name + ‘];’
EXEC sp_executesql @sql;
exec sample_db.dbo.Custom_Action_Auditing @audit_specification_db_name, ‘SELECT’
Go

To view the audits captured, follow the steps under Viewing audit logs in the RDS Developer Guide

Clean up

To clean up your resources

Delete the RDS instance. Note – This will delete the entire instance. Please make sure to keep snapshots as part of recovery if required by your business.
You can do this via the Amazon RDS console or the AWS Command Line Interface (AWS CLI).

For more information, refer to Deleting a DB instance.

Summary

In this post, we discussed some key considerations and various options to set up, optimize, and automate audit creation in Amazon RDS for SQL Server. These steps are vital to limit resource consumption by auditing in SQL Server. Additionally, in large database systems, the setup can be a huge operational overhead in itself. The automation process as defined in the post helps reduce the time taken to set up the audit so that only the necessary actions, objects, and users are attributed into the auditing process. We demonstrated how to configure and test auditing in Amazon RDS for SQL Server.

Try out the solution and if you have any comments or questions, leave them in the comments section.

About the Authors

Arun Pandey is a Senior Database Specialist Solutions Architect at AWS. With over 18 years of experience in application engineering and infra-architecture, Arun helps digitally native companies in India build resilient and scalable database platforms, which aids in solving complex business problems and innovating faster on AWS.

Nirupam Datta is a Cloud Support DBA at AWS. With over 11 years of experience in database engineering and infra-architecture, Nirupam is a Subject Matter Expert in in Amazon RDS core systems and Amazon RDS for SQL Server. He provides guidance and technical assistance to customers, assisting them to navigate their journey in the AWS Cloud.

Siddharth Joshi is a Technical Account Manager at AWS with 15 years of experience. At AWS, he works with ISV customers to help them achieve operational excellence, among other technical guidance. He is passionate about observability and automation

Read MoreAWS Database Blog

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments