Audit Amazon RDS for SQL Server using database activity streams

Amazon Relational Database Service (Amazon RDS) for SQL Server now supports database activity streams to provide a near-real-time stream of database activities in your relational database. To protect your database from internal and external threats, and to meet compliance and regulatory requirements, you can easily integrate the database activity stream with third-party database activity monitoring tools to monitor and audit database activities. The database activities, such as failed logins, are asynchronously pushed to an encrypted data stream, which is provisioned for your database instance with Amazon Kinesis Data Streams.

In this post, we show you how to enable database activity streams for a near-real-time data stream of database activity running on Amazon RDS for SQL Server. After you enable the database activity stream, you can monitor and set alarms for auditing activity in the database.

Solution overview

To implement the solution, complete the following high-level steps:

Create an AWS Key Management Service (AWS KMS) key for the database activity stream.
Create the sample database on Amazon RDS for SQL Server.
Create the server and database audit specifications.
Start the database activity stream.
Analyze database activity.
Create an Amazon Simple Storage Service (Amazon S3) bucket to store the logs.
Configure an Amazon Kinesis Data Firehose delivery stream to store the logs in the S3 bucket.

The following diagram illustrates the solution architecture.

Prerequisites

In Amazon RDS for SQL Server, database activity streams have the following requirements:

Supported DB instance classes for database activity streams
Supported SQL Server version (SQL Server 2016 and onwards, all editions)
A customer managed key in AWS Key Management Service (AWS KMS)

Create a KMS key for the database activity stream

The database activity stream requires a KMS key that you manage in order to encrypt and decrypt the logged database activity. You can’t use the default Amazon RDS KMS key for database activity streaming; you need to create a new customer managed KMS key. For more information, see Creating keys.

On the AWS KMS console, choose Create a key.
For Key type, select Symmetric.
For Key usage, select Encrypt and decrypt.
Choose Next.
In the Add Labels section, under Alias, enter a name for the key.
For Description, enter a description for the key, such as Customer managed Key for Amazon RDS for SQL Server Database Activity Streaming (DAS).
Choose Next.
Under Define Key Administrative permissions, choose the AWS Identity and Access Management (IAM) users and roles who can administer this key through the AWS KMS API.
Choose Next.
In the Define key usage permissions section, choose the IAM user or role who will manage the key.
Choose Next.
Review the key policy and choose Finish.

Create the sample database on Amazon RDS for SQL Server

We first create a sample database testDB and then a table TestTable. Complete the following steps:

Open SQL Server Management Studio (SSMS).
Connect to the RDS for SQL Server database instance.
Choose New Query.
Enter the following query and choose Execute:

CREATE DATABASE testDB
Go
Use testDB
Go
CREATE TABLE [testDB].[dbo].[TestTable](
textA varchar (6000),
 textB varchar (6000)
)

Modify server-level audit specification and create database audit specification

By default, Amazon RDS audit specification tracks failed logins. Therefore, let’s enable the audit to collect successful logins to track all the login attempts.

Let’s start with server-level audit specifications.

In SSMS, choose New Query.

Enter the following query, then choose Execute:

USE [master]
GO

ALTER SERVER AUDIT SPECIFICATION [RDS_DAS_SERVER_AUDIT_SPEC]
WITH (STATE = OFF)

ALTER SERVER AUDIT SPECIFICATION [RDS_DAS_SERVER_AUDIT_SPEC]
ADD (SUCCESSFUL_LOGIN_GROUP)
WITH (STATE = ON)
GO

For database-level audits, we audit DML statements such as SELECT and INSERT on the test table.

Choose New Query.

Enter the following query, then choose Execute:

USE [testDB]
Go
CREATE DATABASE AUDIT SPECIFICATION [RDS_DAS_DB_testDB]
FOR SERVER AUDIT [RDS_DAS_AUDIT]
ADD (SELECT ON OBJECT::[dbo].[TestTable] BY [public]),
ADD (INSERT ON OBJECT::[dbo].[TestTable] BY [public])
WITH (STATE = ON)
Go

After the specifications are set up, let’s start the database activity stream.

Start the database activity stream

To start the database activity stream on Amazon RDS for SQL Server, complete the following steps:

On the Amazon RDS console, in the navigation pane, choose Databases.
Select the RDS for SQL Server database instance on which you want to start an activity stream.
On the Actions menu, choose Start database activity stream.
For AWS KMS key, choose the KMS key you created earlier.
In the Scheduling section, select Immediately.
Choose Start database activity stream.

Enabling this feature doesn’t require downtime or a reboot.

Monitor the status change from Configuring activity stream to Available on the Databases page.
Navigate to the Kinesis Data Streams console and ensure that the status of the data stream is Active.

Note that Server-Side Encryption for Kinesis Data Streams should remain disabled. Enabling encryption will directly impact the database activity streams.

Analyze the database activity stream using the Kinesis client application

To record the database activity, let’s run a few INSERT and SELECT statements.

In SSMS, choose New Query.

Enter the following query, then choose Execute.

USE [testDB]
Go
INSERT INTO [testDB].[dbo].[TestTable]
VALUES ('Bob', 'Johnson');
INSERT INTO [testDB].[dbo].[TestTable]
VALUES ('Jinnie', 'Johnson');
INSERT INTO [testDB].[dbo].[TestTable]
VALUES ('Rob', 'Brown');

select * from [testDB].[dbo].[TestTable]

To read the audit data from the Kinesis data stream, we should decrypt the data using the same KMS key we created earlier.

In the following sample output, we used the Kinesis Client Library for Java to extract the data from the Kinesis data stream and show the audit record and its fields:

{
  "type": "DatabaseActivityMonitoringRecord",
  "clusterId": "",
  "instanceId": "db- HAFFQILMCDWKPXURN67M5TQFXY",
  "databaseActivityEventList": [
    {
      "class ": "TABLE",
      "clientApplication": "Microsoft SQL Server Management Studio - Query",
      "command": "INSERT",
      "commandText": "INSERT INTO [testDB].[dbo].[TestTable]\r\nVALUES ('Jinnie', 'Johnson')",
      "databaseName": "testDB",
      "dbProtocol": "SQLSERVER",
      "dbUserName": "admin",
      "endTime": null,
      "errorMessage": null,
      "exitCode": 1,
      "logTime": "2023-02-02 22:01:49.5283055+00",
      "netProtocol": null,
      "objectName": "TestTable",
      "objectType": "TABLE",
      "paramList": null,
      "pid": null,
      "remoteHost": " 10.145.xx.xx",
      "remotePort": null,
      "rowCount": 0,
      "serverHost": "172. 30.2.173",
      "serverType": "SQLSERVER",
      "serverVersion": "15.00.4236.7.v1.R1",
      "serviceName": "sqlserver-se",
      "sessionId": 75,
      "startTime": null,
      "statementId": "0xaffb4ff267b e9d45a3e7518553d73a40",
      "substatementId": 1,
      "transactionId": "22914 7",
      "type": "record",
      "engineNativeAuditFields": {}
    }
  ]
}

Create an S3 bucket to store the stream logs

Additionally, you can export the log stream to Amazon S3 for third-party monitoring and audit the applications.

You can create an S3 bucket to store the stream logs using the Amazon S3 console, AWS SDKs,

or the AWS Command Line Interface (AWS CLI). For instructions, refer to Creating a bucket.

To archive and store the records for a long duration, configure the S3 Lifecycle.

Configure the Kinesis Data Firehose delivery stream

After you activate the database activity stream, you must create your Firehose delivery stream. Complete the following steps:

On the Kinesis Data Firehose console, choose Create delivery stream.
For Source, choose Amazon Kinesis Data Streams.
For Destination, choose Amazon S3.
For Delivery stream name, enter a name for your delivery stream.

Modify audit specifications

To modify the audit specifications, complete the following steps:

On the Amazon RDS console, in the navigation pane, choose Databases to display a list of your DB instances.
Select your RDS for SQL Server instance.
On the Actions menu, choose Modify database activity stream.
Select Unlocked and choose Modify DB activity stream.
Modify the audit specifications in the RDS for SQL Server instance as needed.
After the modification is complete, repeat the previous steps and select Locked.

Stop the database activity stream

To stop the database activity stream on Amazon RDS for SQL Server, complete the following steps:

On the Amazon RDS console, in the navigation pane, choose Databases to display a list of your DB instances.
Select your RDS for SQL Server instance.
On the Actions menu, choose Stop database activity stream.
Select Apply immediately.

A message appears confirming that the database activity stream is stopping.

Ensure that the status of the database changes from Configuring activity stream to Available.

Conclusion

In this post, we provided the steps to configure database activity streams on an RDS for SQL Server instance to meet your database audit requirements. We explained the various methods to visualize the audit data for monitoring and alerts. This feature can help you monitor and record user activities performed on the database objects. We recommend you review your audit requirements, consider the performance implication and the type of activities to track, and adhere to your compliance standards before using this feature on Amazon RDS for SQL Server.

About the authors

Vikas Babu Gali is a Sr. Specialist Solutions Architect, focusing on Microsoft Workloads at Amazon Web Services. As a native of India, Vikas enjoys playing Cricket and spending time with his family and friends outdoors.

Sudhir Amin is a Database Specialist Solutions Architect at Amazon Web Services. In his role based out of New York, he provides architectural guidance and technical assistance to enterprise customers across different industry verticals, accelerating their cloud adoption. He is a big fan of snooker, combat sports such as boxing and UFC, and loves traveling to countries with rich wildlife reserves where he gets to see world’s most majestic animals up close.

AWS Database Blog