AWS Prescriptive Guidance

Auditing SQL Server instances,

database objects, and logins in
Amazon RDS and Amazon EC2
 AWS Prescriptive Guidance Auditing SQL
Server instances, database objects, and
logins in Amazon RDS and Amazon EC2

AWS Prescriptive Guidance: Auditing SQL Server instances, database

objects, and logins in Amazon RDS and Amazon EC2
Copyright © 2023 Amazon Web Services, Inc. and/or its affiliates. All rights reserved.

Amazon's trademarks and trade dress may not be used in connection with any product or service that is not
Amazon's, in any manner that is likely to cause confusion among customers, or in any manner that disparages or
discredits Amazon. All other trademarks not owned by Amazon are the property of their respective owners, who may
or may not be affiliated with, connected to, or sponsored by Amazon.
 AWS Prescriptive Guidance Auditing SQL
Server instances, database objects, and
logins in Amazon RDS and Amazon EC2

Table of Contents
Introduction ...................................................................................................................................... 1
Targeted business outcomes ........................................................................................................ 1
Overview ........................................................................................................................................... 3
Auditing levels ........................................................................................................................... 3
Flow chart ................................................................................................................................. 3
Advantages and disadvantages of auditing .................................................................................... 4
Auditing Amazon RDS for SQL Server DB instances ................................................................................ 6
Prerequisites .............................................................................................................................. 6
Supported versions .................................................................................................................... 6
Using C2 audit mode .................................................................................................................. 6
Creating and viewing audits ........................................................................................................ 6
Configuring the option group .............................................................................................. 7
Creating audits .................................................................................................................. 7
Creating audit specifications ................................................................................................ 7
Viewing audit logs ............................................................................................................. 8
Monitoring ................................................................................................................................ 9
Auditing SQL Server on Amazon EC2 or Amazon RDS Custom DB instances .............................................. 10
Prerequisites ............................................................................................................................ 10
Supported versions ................................................................................................................... 10
Using C2 audit mode ................................................................................................................ 10
Creating and viewing audits ...................................................................................................... 10
Creating server audits ....................................................................................................... 10
Creating server audit specifications ..................................................................................... 11
Creating database audit specifications ................................................................................. 11
Viewing SQL Server audit logs ........................................................................................... 11
Monitoring ................................................................................................................................ 9
Storage and compute requirements for auditing .................................................................................. 13
Best practices .................................................................................................................................. 14
FAQ ................................................................................................................................................ 15
What are the main components of the SQL Server audit feature? ................................................... 15
What are some critical events I should consider auditing? .............................................................. 15
Why is it important to audit failed logins, login changes, and user changes? ..................................... 15
Why is it important to audit schema changes? ............................................................................. 15
Why is it important to audit the auditing system? ........................................................................ 15
How can I use triggers to audit database changes? ....................................................................... 16
What are the advantages and disadvantages of using CDC to audit database changes? Which
versions support CDC? .............................................................................................................. 16
Resources ........................................................................................................................................ 17
Document history ............................................................................................................................. 18
Glossary .......................................................................................................................................... 19
Database terms ........................................................................................................................ 19
Security terms ......................................................................................................................... 20

iii
 AWS Prescriptive Guidance Auditing SQL
Server instances, database objects, and
logins in Amazon RDS and Amazon EC2
Targeted business outcomes

Auditing SQL Server instances,

database objects, and logins in
Amazon RDS and Amazon EC2
Ashish Srivastava, Bhavani Akundi, and Sreenivas Nettem, Amazon Web Services (AWS)

April 2023 (document history (p. 18))

This guide explains how to implement the SQL Server auditing process for SQL Server on Amazon Elastic
Compute Cloud (Amazon EC2) and Amazon Relational Database Service (Amazon RDS) for SQL Server
database instances.

Database auditing is an IT auditing method for certifying that organizational data is secure. It involves
evaluating data and logging key critical business operations on databases.

Database auditing has become mandatory, especially when the data includes personally identifiable
information (PII) and has to adhere to security and compliance guidelines. Some guidelines involve data
types and recommendations issued by a country's governance policies. An auditing process requires
evidence, which can be extracted from database logs. Auditing helps prevent unauthorized access to
data. By tracking data usage, you can investigate false activity and take appropriate actions. Database
auditing for data confidentiality, integrity, and accessibility help ensure that data is protected. To prevent
data violations, the best practice is to have both database security and auditing in place.

SQL Server auditing is a requirement for complying with security, financial, and healthcare standards
such as ISO/IEC 27001, the Payment Card Industry Data Security Standard (PCI DSS), BASEL III, the
European Union General Data Protection Regulation (GDPR), Information Governance (IG), and the
Health Insurance Portability and Accountability Act (HIPAA).

Targeted business outcomes

Organizations implement database and SQL Server auditing for several reasons, including the following:

• Auditors need meaningful and contextual data for compliance and auditing. DB audit logs are suitable
for DBA teams but not for auditors.
• The ability to generate critical alerts in case of a security breach is a basic requirement for large-
scale software. You can use audit logs for this purpose, because the logging information assists in
identifying and tracking control checks.
• Database auditing provides information such as the following:
• Who accessed the data―for example, DBAs, developers, auditors, extract, transform, and load (ETL)
processes, DevOps engineers?
• What was the earlier state of the data?
• When was the data updated, what was modified, and why?
• Did an authorized person approve the request?
• Are internal users using their privileges properly?
• Because audit trails help identify infiltrators, they help deter insiders. People who know that their
actions are scrutinized are less likely to access unauthorized databases or tamper with specific data.

1
 AWS Prescriptive Guidance Auditing SQL
Server instances, database objects, and
logins in Amazon RDS and Amazon EC2
Targeted business outcomes
• Finance, medical, energy, food service, public works, and many other industries need to analyze
data access and produce detailed reports regularly for government agencies. For example, HIPAA
regulations require healthcare providers to deliver audit trails that detail who accessed the data in
their records, down to the row and record level. GDPR has similar requirements. The Sarbanes Oxley
Act (SOX) places a wide range of accounting regulations on public corporations. These organizations
need to analyze data access and produce detailed reports regularly.

2
 AWS Prescriptive Guidance Auditing SQL
Server instances, database objects, and
logins in Amazon RDS and Amazon EC2
Auditing levels

Overview of SQL Server auditing

levels and process
The following sections provide information about server-level and database-level auditing, auditing
processes, benefits, and disadvantages.

Auditing levels
Auditing can be performed at the SQL Server level, at the database level (which involves logging the
events associated with actions), or at both levels.

Server-level auditing

You can use the SQL Server Audit object to audit and collect actions to monitor. You can specify a single
instance of server-level actions and groups of actions. You can also create multiple audits for each SQL
Server instance.

SQL Server level auditing involves server-level configuration parameters such as xp_cmdshell and max
server memory.  For more information about server memory configurations, see the Microsoft SQL
Server documentation.

Database-level auditing

Database-level auditing involves capturing the actions of database users for security purposes. For
example, you can use database-level auditing to ensure that unauthorized users and processes cannot
access the database, and to verify that rules are applied to restrict any unauthorized activities. Examples
of database-level auditing include capturing all INSERT, UPDATE, DELETE, and TRIGGERS operations on
the database.

This guide provides instructions and examples of both auditing levels.

Flow chart
The following flow chart illustrates the SQL Server auditing process. When a user or process logs in to
the database system, their login credentials are validated. If the login is valid, the audit process checks
for authorization. If the user or process is authorized to perform the action, they can complete the
action, and the audited data is logged in the database audit table.

3
 AWS Prescriptive Guidance Auditing SQL
Server instances, database objects, and
logins in Amazon RDS and Amazon EC2
Advantages and disadvantages of auditing

Advantages and disadvantages of auditing

Advantages

• Helps reduce security violation incidents or any other actions that might result in an unauthorized
disclosure of classified information.
• Helps identify security gaps and vulnerabilities, including illicit access to database resources, data, or
operations.

4
 AWS Prescriptive Guidance Auditing SQL
Server instances, database objects, and
logins in Amazon RDS and Amazon EC2
Advantages and disadvantages of auditing
• Provides an audit trail of activities so you can verify and track all types of transactions and processes,
and trace queries to analyze performance.
• Makes organizations more accountable, because they can review tracked audit information and provide
feedback to meet security goals and performance objectives.

Disadvantages

• In general, performance impact is minimal. However, if auditing involves a high volume of transaction
tracking, it can require additional resources.
• Might generate too many reports and documents to view, and might require providing feedback to
multiple management and security teams.
• Consumption of storage resources to store the audit files might be high.
• Additional maintenance is required to archive or purge old audit data, or to move tables to different
database file groups or storage.

5
 AWS Prescriptive Guidance Auditing SQL
Server instances, database objects, and
logins in Amazon RDS and Amazon EC2
Prerequisites

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 or CONTROL 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.

6
 AWS Prescriptive Guidance Auditing SQL
Server instances, database objects, and
logins in Amazon RDS and Amazon EC2
Configuring the option group
• 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. To avoid
errors, review the list of limitations listed in Amazon RDS 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.

7
 AWS Prescriptive Guidance Auditing SQL
Server instances, database objects, and
logins in Amazon RDS and Amazon EC2
Viewing audit logs
For syntax, arguments, and examples, see the Microsoft SQL Server documentation. The following
specification audits failed login actions and tracks the creation, alteration, and deletion of server objects.
For a list of actions, 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.

8
 AWS Prescriptive Guidance Auditing SQL
Server instances, database objects, and
logins in Amazon RDS and Amazon EC2
Monitoring
• 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.

9
 AWS Prescriptive Guidance Auditing SQL
Server instances, database objects, and
logins in Amazon RDS and Amazon EC2
Prerequisites

Auditing SQL Server on Amazon EC2

or Amazon RDS Custom DB instances
This section provides information about auditing options for SQL Server on Amazon EC2 and Amazon
RDS Custom, including creating server and database audits, viewing audit logs, and monitoring results.

Prerequisites
• Database login with ALTER ANY SERVER AUDIT or CONTROL SERVER permission

Supported versions
• Any edition of SQL Server version 2016 and later

Using C2 audit mode

C2 audit mode 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. C2 audit logs are
stored in the default data directory of the SQL Server instance. Each log file can be a maximum of 200
MB. A new file is automatically created when this limit is reached. You can enable C2 auditing by using
SQL Server Management Studio. For more information, see the Microsoft SQL Server documentation.
Important
Microsoft plans to remove C2 audit mode in a future version of SQL Server. We recommend that
you avoid using this feature.

To use C2 audit mode to audit failed logins:

1. In SQL Server Management Studio, connect to the SQL Server instance that you want to enable
auditing for.
2. Select the SQL Server instance, right-click and choose Properties, and then choose Security.
3. For Login auditing, choose a configuration option. You can audit failed logins only, successful logins
only, both, or none. (The default is failed logins only.)
4. For Options, select Enable C2 audit tracing.

Creating and viewing audits

Creating server audits
A server audit in SQL Server collects instance-level or database-level actions to monitor. Audit output is
saved to an audit destination file path, a Windows security log, or an application log.

10
 AWS Prescriptive Guidance Auditing SQL
Server instances, database objects, and
logins in Amazon RDS and Amazon EC2
Creating server audit specifications
To create a server audit:

1. In SQL Server Management Studio, in Object Explorer, expand Security, right-click Audits, and then
choose New Audit. This creates a new SQL Server Audit object for server-level auditing.
2. For Audit destination, choose a file, a security log, or an application log.
3. If you selected a file as the target, specify the location of the folder.
4. Configure other options, and then choose OK.
5. To enable the audit, right-click the new audit configuration, and then choose Enable Audit.

For more information, see the Microsoft SQL Server documentation.

Creating server audit specifications

The server audit specification collects many server-level action groups raised by the SQL Server Extended
Events feature. You can include audit action groups in a server audit specification. These actions are sent
to the audit that records them in the target file or log.

To create a server audit specification:

1. In SQL Server Management Studio, in Object Explorer, expand Security, right-click Server Audit
Specifications, and then choose New Server Audit Specification.
2. For Audit, choose the server audit you created earlier.
3. For Actions, choose the audit action type that specifies the server-level audit action groups and audit
actions that you want to capture, and then choose OK.
4. To enable the server audit specification, right-click the new specification, and then choose Enable
Server Audit Specification.

For more information, see Create a Server Audit and Server Audit Specification and SQL Server Audit
Action Groups and Actions in the Microsoft SQL Server documentation.

Creating database audit specifications

You can create a database audit specification object for database-level auditing. This specification
specifies the database-level audit action groups and audit actions to capture.

To create a database audit specification:

1. In SQL Server Management Studio, in Object Explorer, expand the database that you want to audit.
2. Expand the Security folder, right-click Database Audit Specifications, and then choose New Database
Audit Specification.
3. For Actions, configure one or more database audit action types. Select the statements that you want
to audit (such as DELETE or INSERT) and the object class to perform the action on.
4. When your selections are complete, choose OK.
5. To enable the database audit specification, right-click the new specification, and then choose Enable
Database Audit Specification.

For more information, see Create a server audit and database audit specification and SQL Server Audit
Action Groups and Actions in the Microsoft SQL Server documentation.

Viewing SQL Server audit logs

To view audit logs:

11
 AWS Prescriptive Guidance Auditing SQL
Server instances, database objects, and
logins in Amazon RDS and Amazon EC2
Monitoring
1. In SQL Server Management Studio, right-click the SQL Server Audit object, and then choose View
Audit Logs.

The Log File Viewer displays the audit log regardless of its location (a file or the Windows Event Log).
2. To customize the log entries that are displayed, choose Filter.
3. To export the log to a log file, choose Export.
4. When you've finished viewing the log, choose Close.

For more information, see the Microsoft SQL Server documentation.

Monitoring
You can monitor audit logs that are logged to an audit file, an application or security event log, or an
audit table in the database by using monitoring solutions such as Nagios. A monitoring solution that's
integrated with a ticketing or alerting mechanism can generate real-time alerts and incidents to notify
the systems administrator or database administrator.

12
 AWS Prescriptive Guidance Auditing SQL
Server instances, database objects, and
logins in Amazon RDS and Amazon EC2

Storage and compute requirements

for auditing
For Amazon RDS

• Audit logs are first stored locally to disk at the location D:\rdsdbdata\SQLAudit. Amazon RDS then
uploads these files to an S3 bucket that's configured in the SQLSERVER_AUDIT option group by using
the specified IAM role.
• 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 to Amazon S3.
• Make sure to provision sufficient storage space for the instance based on the retention period.
• CPU consumption for running audits is generally minimal. Monitor CPU usage when you run audit
queries and size the Amazon RDS DB instance accordingly. You can monitor Amazon RDS metrics with
Amazon CloudWatch.

For Amazon EC2

• Make sure that there is sufficient storage space provisioned on the drive that stores the audit log files
based on retention period.
• CPU consumption for running audits is generally minimal. Monitor CPU usage when you run audit
queries and size the EC2 instance accordingly. You can use Amazon CloudWatch to monitor EC2
instances.

13
 AWS Prescriptive Guidance Auditing SQL
Server instances, database objects, and
logins in Amazon RDS and Amazon EC2

Best practices for auditing SQL

Server on AWS
When you audit SQL Server databases on AWS, follow these best practices.

• Understand audit requirements. Check if the audit solution has to meet compliance requirements
such as GDPR or HIPAA. For example, the audit solution might have to track and log all changes
performed on critical data such as PII and financial information. 
• Define the audit scope. Decide if you need to audit all SQL Server instances or only specific instances
that host critical databases. At the database level, determine whether you need to audit all tables or
only tables that contain critical data.
• Identify the list of events that you want to track and log. For example, your audit list might include
login failures, login permission changes, new logins and users, and deleted logins and users.
• Choose the right audit tool. For example, if you want to audit only login and logout events, you can
use an error log or extended events. If you want to audit data manipulation language (DML) changes,
use change data capture (CDC), change tracking, or temporal tables. If you want to audit changes at
the instance and database level, use the SQL Server audit feature. Or you can use a third-party audit
tool such as ApexSQL Audit.
• Set up real-time alerts to proactively notify the DBAs or the security team when a specific action
doesn't meet compliance requirements.
• Review audit data periodically by creating a simple dashboard or a report that reads the audit data
and filters the actions that you are interested in.
• Set up an alert to monitor the changes performed on the auditing solution.
• Define retention policies for the audit data based on your company's requirements, and archive the
old audit data.

14
 AWS Prescriptive Guidance Auditing SQL
Server instances, database objects, and
logins in Amazon RDS and Amazon EC2
What are the main components
of the SQL Server audit feature?

FAQ
This section provides answers to frequently asked questions about auditing SQL Server instances on
Amazon RDS and Amazon EC2.

What are the main components of the SQL Server

audit feature?
The SQL Server audit feature has three main components:

• SQL Server Audit objects define the path to store the audit information, the auditing synchronization
mode, the audit file rollover mechanism, and the action to be performed in case of audit failures.
• Server audit specifications track and log the changes that are performed at the SQL Server instance
level and events that are raised by the SQL Server Extended Events feature.
• Database audit specifications track and log different types of actions that are performed at the
database level and events that are raised by the SQL Server Extended Events feature.

What are some critical events I should consider

auditing?
Critical events include failed logins, login changes, user changes, schema changes, and audit changes.

Why is it important to audit failed logins, login

changes, and user changes?
For example, excessive failed login attempts or user permission changes might signal that an attack is in
progress.

Why is it important to audit schema changes?

We recommend that you track all database schema changes to detect any changes that weren't
authorized.

Why is it important to audit the auditing system?

Auditing the changes in your SQL Server auditing solution helps you catch unauthorized users who might
be trying to disable the auditing process to perform non-compliant or illegal activities. This audit also
helps you meet auditor requirements for the integrity of audit solution logs by providing evidence that
covers all scenarios. Another simple use for this audit is to remind the database administrator to reenable
the audit in case it was disabled for maintenance purposes.

15
 AWS Prescriptive Guidance Auditing SQL
Server instances, database objects, and
logins in Amazon RDS and Amazon EC2
How can I use triggers to audit database changes?

How can I use triggers to audit database changes?

You can create triggers on tables that contain critical data to log modified or inserted data, and to
compare the data before and after the modification. You can use the INSTEAD OF trigger to prevent
changes on a specific table and to log the failed action.

What are the advantages and disadvantages of

using CDC to audit database changes? Which
versions support CDC?
Change data capture (CDC) is supported in all editions of SQL Server 2016 and later. In earlier versions,
only the Enterprise edition supports CDC.

Here are some of the advantages of using CDC to audit database changes:

• You can use CDC as an asynchronous SQL Server audit solution, to track data manipulation language
(DML) operations on tables.
• CDC tracks INSERT, UPDATE, and DELETE operations on database tables, and records detailed
information about these changes in mirrored tables.
• CDC depends on the SQL Server transaction log as the source of data changes.
• You can easily configure CDC by using Transact-SQL commands.

Disadvantages:

• CDC doesn't handle data definition language (DDL) changes on CDC-enabled tables automatically. It
requires extra effort to reflect DDL changes in the tracking table.
• CDC provides no option to track the SELECT statement.
• SQL Server keeps CDC tracking data in the change table for only a configurable number of days.
• CDC jobs will not work unless the SQL Server agent service is running.

16
 AWS Prescriptive Guidance Auditing SQL
Server instances, database objects, and
logins in Amazon RDS and Amazon EC2

Resources
• SQL Server Audit (Amazon RDS documentation)
• How to enable auditing for Amazon RDS for SQL Server (Amazon RDS for SQL Server Workshop)
• Working with Amazon RDS event notification (Amazon RDS documentation)
• Audit and accountability in Amazon EC2 (Amazon EC2 documentation)
• Migrating SQL Server databases to the AWS Cloud (AWS Prescriptive Guidance)
• Best practices for deploying Microsoft SQL Server on Amazon EC2 (AWS Prescriptive Guidance)

17
 AWS Prescriptive Guidance Auditing SQL
Server instances, database objects, and
logins in Amazon RDS and Amazon EC2

Document history
The following table describes significant changes to this guide. If you want to be notified about future
updates, you can subscribe to an RSS feed.

Change Description Date

Initial publication (p. 18) — April 20, 2023

18
 AWS Prescriptive Guidance Auditing SQL
Server instances, database objects, and
logins in Amazon RDS and Amazon EC2
Database terms

AWS Prescriptive Guidance glossary

The following are commonly used terms in strategies, guides, and patterns provided by AWS Prescriptive
Guidance. To suggest entries, please use the Provide feedback link at the end of the glossary.

Database terms
atomicity, consistency, isolation, durability (ACID)

A set of software properties that guarantee the data validity and operational reliability of a
database, even in the case of errors, power failures, or other problems.
big-endian system

A system that stores the most significant byte first. See also endianness (p. 19).
bloom filter

A probabilistic, memory-efficient data structure that is used to test whether an element is a member
of a set.
buffer cache

The memory area where the most frequently accessed data is stored.
change data capture (CDC)

The process of tracking changes to a data source, such as a database table, and recording metadata
about the change. You can use CDC for various purposes, such as auditing or replicating changes in a
target system to maintain synchronization.
cold cache

A buffer cache that is empty, not well populated, or contains stale or irrelevant data. This affects
performance because the database instance must read from the main memory or disk, which is
slower than reading from the buffer cache.
configuration management database (CMDB)

A repository that stores and manages information about a database and its IT environment,
including both hardware and software components and their configurations.
database definition language (DDL)

Statements or commands for creating or modifying the structure of tables and objects in a database.
database manipulation language (DML)

Statements or commands for modifying (inserting, updating, and deleting) information in a

database.
endianness

The order in which bytes are stored in computer memory. Big-endian systems store the most
significant byte first. Little-endian systems store the least significant byte first.
little-endian system

A system that stores the least significant byte first. See also endianness (p. 19).

19
 AWS Prescriptive Guidance Auditing SQL
Server instances, database objects, and
logins in Amazon RDS and Amazon EC2
Security terms
query plan regression

When a database service optimizer chooses a less optimal plan than it did before a given change
to the database environment. This can be caused by changes to statistics, constraints, environment
settings, query parameter bindings, and updates to the database engine.
read replica

A copy of a database that’s used for read-only purposes. You can route queries to the read replica to
reduce the load on your primary database.
vacuuming

A database maintenance operation that involves cleaning up after incremental updates to reclaim
storage and improve performance.
warm cache

A buffer cache that contains current, relevant data that is frequently accessed. The database instance
can read from the buffer cache, which is faster than reading from the main memory or disk.

Security terms
attribute-based access control (ABAC)

The practice of creating fine-grained permissions based on user attributes, such as department,
job role, and team name. For more information, see ABAC for AWS in the AWS Identity and Access
Management (IAM) documentation.
asymmetric encryption

An encryption algorithm that uses a pair of keys, a public key for encryption and a private key for
decryption. You can share the public key because it isn’t used for decryption, but access to the
private key should be highly restricted.
behavior graph

A unified, interactive view of resource behavior and interactions over time. You can use a behavior
graph with Amazon Detective to examine failed logon attempts, suspicious API calls, and similar
actions. For more information, see Data in a behavior graph in the Detective documentation.
client-side encryption

Encryption of data locally, before the target AWS service receives it.
conformance pack

A collection of AWS Config rules and remediation actions that you can assemble to customize your
compliance and security checks. You can deploy a conformance pack as a single entity in an AWS
account and Region, or across an organization, by using a YAML template. For more information, see
Conformance packs in the AWS Config documentation.
data at rest

Data that is stationary in your network, such as data that is in storage.

data classification

A process for identifying and categorizing the data in your network based on its criticality and
sensitivity. It is a critical component of any cybersecurity risk management strategy because it helps
you determine the appropriate protection and retention controls for the data. Data classification is a
component of the security pillar in the AWS Well-Architected Framework. For more information, see
Data classification.

20
 AWS Prescriptive Guidance Auditing SQL
Server instances, database objects, and
logins in Amazon RDS and Amazon EC2
Security terms
data in transit

Data that is actively moving through your network, such as between network resources.
defense-in-depth

An information security approach in which a series of security mechanisms and controls are
thoughtfully layered throughout a computer network to protect the confidentiality, integrity, and
availability of the network and the data within. When you adopt this strategy on AWS, you add
multiple controls at different layers of the AWS Organizations structure to help secure resources.
delegated administrator

In AWS Organizations, a compatible service can register an AWS member account to administer the
organization’s accounts and manage permissions for that service. This account is called the delegated
administrator for that service. For more information and a list of compatible services, see Services
that work with AWS Organizations in the AWS Organizations documentation.
detective control

A security control that is designed to detect, log, and alert after an event has occurred. These
controls are a second line of defense, alerting you to security events that bypassed the preventative
controls in place. For more information, see Detective controls in Implementing security controls on
AWS.
encryption key

A cryptographic string of randomized bits that is generated by an encryption algorithm. Keys can
vary in length, and each key is designed to be unpredictable and unique.
endpoint service

A service that you can host in a virtual private cloud (VPC) to share with other users. You can create
an endpoint service with AWS PrivateLink and grant permissions to other AWS accounts or to IAM
principals. These accounts or principals can connect to your endpoint service privately by creating
interface VPC endpoints. For more information, see Create an endpoint service in the Amazon VPC
documentation.
envelope encryption

The process of encrypting an encryption key with another encryption key. For more information, see
Envelope encryption in the AWS Key Management Service (AWS KMS) documentation.
geographic restrictions (geo blocking)

In Amazon CloudFront, an option to prevent users in specific countries from accessing content
distributions. You can use an allow list or block list to specify approved and banned countries. For
more information, see Restricting the geographic distribution of your content in the CloudFront
documentation.
guardrail

A high-level rule that helps govern resources, policies, and compliance across organizational units
(OUs). Preventive guardrails enforce policies to ensure alignment to compliance standards. They are
implemented by using service control policies and IAM permissions boundaries. Detective guardrails
detect policy violations and compliance issues, and generate alerts for remediation. They are
implemented by using AWS Config, AWS Security Hub, Amazon GuardDuty, AWS Trusted Advisor,
Amazon Inspector, and custom AWS Lambda checks.
identity-based policy

A policy attached to one or more IAM principals that defines their permissions within the AWS Cloud
environment.
inbound (ingress) VPC

In an AWS multi-account architecture, a VPC that accepts, inspects, and routes network connections
from outside an application. The AWS Security Reference Architecture recommends setting up your

21
 AWS Prescriptive Guidance Auditing SQL
Server instances, database objects, and
logins in Amazon RDS and Amazon EC2
Security terms
Network account with inbound, outbound, and inspection VPCs to protect the two-way interface
between your application and the broader internet.
inspection VPC

In an AWS multi-account architecture, a centralized VPC that manages inspections of network traffic
between VPCs (in the same or different AWS Regions), the internet, and on-premises networks. The
AWS Security Reference Architecture recommends setting up your Network account with inbound,
outbound, and inspection VPCs to protect the two-way interface between your application and the
broader internet.
least privilege

The security best practice of granting the minimum permissions required to perform a task. For
more information, see Apply least-privilege permissions in the IAM documentation.
member account

All AWS accounts other than the management account that are part of an organization in AWS
Organizations. An account can be a member of only one organization at a time.
organization trail

A trail that’s created by AWS CloudTrail that logs all events for all AWS accounts in an organization
in AWS Organizations. This trail is created in each AWS account that’s part of the organization and
tracks the activity in each account. For more information, see Creating a trail for an organization in
the CloudTrail documentation.
outbound (egress) VPC

In an AWS multi-account architecture, a VPC that handles network connections that are initiated
from within an application. The AWS Security Reference Architecture recommends setting up your
Network account with inbound, outbound, and inspection VPCs to protect the two-way interface
between your application and the broader internet.
origin access control (OAC)

In CloudFront, an enhanced option for restricting access to secure your Amazon Simple Storage
Service (Amazon S3) content. OAC supports all S3 buckets in all AWS Regions, server-side encryption
with AWS KMS (SSE-KMS), and dynamic PUT and DELETE requests to the S3 bucket.
origin access identity (OAI)

In CloudFront, an option for restricting access to secure your Amazon S3 content. When you use
OAI, CloudFront creates a principal that Amazon S3 can authenticate with. Authenticated principals
can access content in an S3 bucket only through a specific CloudFront distribution. See also
OAC (p. 22), which provides more granular and enhanced access control.
permissions boundary

An IAM management policy that is attached to IAM principals to set the maximum permissions
that the user or role can have. For more information, see Permissions boundaries in the IAM
documentation.
policy

An object that can define permissions (see identity-based policy (p. 21)), specify access conditions
(see resource-based policy (p. 23)), or define the maximum permissions for all accounts in an
organization in AWS Organizations (see service control policy (p. 23)).
preventative control

A security control that is designed to prevent an event from occurring. These controls are a first line
of defense to help prevent unauthorized access or unwanted changes to your network. For more
information, see Preventative controls in Implementing security controls on AWS.

22
 AWS Prescriptive Guidance Auditing SQL
Server instances, database objects, and
logins in Amazon RDS and Amazon EC2
Security terms
principal

An entity in AWS that can perform actions and access resources. This entity is typically a root user
for an AWS account, an IAM role, or a user. For more information, see Principal in Roles terms and
concepts in the IAM documentation.
ransomware

A malicious software that is designed to block access to a computer system or data until a payment
is made.
resource-based policy

A policy attached to a resource, such as an Amazon S3 bucket, an endpoint, or an encryption key.

This type of policy specifies which principals are allowed access, supported actions, and any other
conditions that must be met.
responsive control

A security control that is designed to drive remediation of adverse events or deviations from your
security baseline. For more information, see Responsive controls in Implementing security controls on
AWS.
SAML 2.0

An open standard that many identity providers (IdPs) use. This feature enables federated single
sign-on (SSO), so users can log into the AWS Management Console or call the AWS API operations
without you having to create user in IAM for everyone in your organization. For more information
about SAML 2.0-based federation, see About SAML 2.0-based federation in the IAM documentation.
security control

A technical or administrative guardrail that prevents, detects, or reduces the ability of a threat
actor to exploit a security vulnerability. There are three primary types of security controls:
preventative (p. 22), detective (p. 21), and responsive (p. 23).
security hardening

The process of reducing the attack surface to make it more resistant to attacks. This can include
actions such as removing resources that are no longer needed, implementing the security best
practice of granting least privilege, or deactivating unnecessary features in configuration files.
security information and event management (SIEM) system

Tools and services that combine security information management (SIM) and security event
management (SEM) systems. A SIEM system collects, monitors, and analyzes data from servers,
networks, devices, and other sources to detect threats and security breaches, and to generate alerts.
server-side encryption

Encryption of data at its destination, by the AWS service that receives it.
service control policy (SCP)

A policy that provides centralized control over permissions for all accounts in an organization in AWS
Organizations. SCPs define guardrails or set limits on actions that an administrator can delegate to
users or roles. You can use SCPs as allow lists or deny lists, to specify which services or actions are
permitted or prohibited. For more information, see Service control policies in the AWS Organizations
documentation.
shared responsibility model

A model describing the responsibility you share with AWS for cloud security and compliance. AWS is
responsible for security of the cloud, whereas you are responsible for security in the cloud. For more
information, see Shared responsibility model.

23
 AWS Prescriptive Guidance Auditing SQL
Server instances, database objects, and
logins in Amazon RDS and Amazon EC2
Security terms
symmetric encryption

An encryption algorithm that uses the same key to encrypt and decrypt the data.
trusted access

Granting permissions to a service that you specify to perform tasks in your organization in AWS
Organizations and in its accounts on your behalf. The trusted service creates a service-linked
role in each account, when that role is needed, to perform management tasks for you. For more
information, see Using AWS Organizations with other AWS services in the AWS Organizations
documentation.
workload

A collection of resources and code that delivers business value, such as a customer-facing application
or backend process.

24