The Ultimate Guide to AWS RDS SQL Server Administration WITH VLDBs (Part 5)

33. The Two Paths of Encryption: TDE vs. AWS KMS

When you have 100TB of data, "encryption at rest" is mandatory for compliance. 

However, there are two ways to do it in RDS, and choosing the wrong one can complicate your life.

A. AWS KMS (Storage-Level Encryption)

This is the default and recommended method. AWS encrypts the underlying EBS volumes, snapshots, and backups using Envelope Encryption.

Performance: Almost zero impact. The encryption happens at the hardware/hypervisor layer.

Management: Transparent. SQL Server doesn't even know it's encrypted.

B. Transparent Data Encryption (TDE)

This is a SQL Server feature. The engine itself encrypts the .mdf and .ldf files.

Use Case: Choose this only if your compliance auditor strictly requires "Database-Level Encryption" or if you need to move encrypted .bak files to an on-premise server.

Performance: Can add 3-5% CPU overhead. On a 100TB VLDB, this can be substantial during heavy I/O.

Requirement: Requires SQL Server Enterprise Edition (or Standard Edition in version 2022+).

Feature	AWS KMS (Recommended)	SQL Server TDE
Layers Encrypted	Storage, Backups, Snapshots	Database Files (.mdf, .ldf)
CPU Overhead	Negligible	3-5%
Key Management	AWS KMS	RDS Option Groups

---

34. Auditing: Tracking Every Breath

In a mission-critical environment, "Who deleted that row?" is a question you must be able to answer in seconds.

A. The RDS Limitation

In a standard SQL Server, you use the "Server Properties" in SSMS to enable login auditing.

 In RDS, this is disabled. You cannot modify the Windows Registry.

B. The Solution: SQL Server Audit + CloudWatch

To capture successful/failed logins and data changes, you must use the SQLSERVER_AUDIT option.

Step 1: The Option Group

Go to the RDS Console -> Option Groups.

Create a new group and add the SQLSERVER_AUDIT option.

Specify an IAM role that allows RDS to write to CloudWatch Logs.

Step 2: The SQL Audit Script

Once the option is enabled, you create the audit inside SQL Server.

-- 1. Create the Server Audit

CREATE SERVER AUDIT [Company_Security_Audit]

TO EXTERNAL_ADMINISTRATOR -- This sends it to the RDS Option Group

WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);

-- 2. Create the Specification (What to track?)

CREATE SERVER AUDIT SPECIFICATION [Track_Logins_And_Drops]

FOR SERVER AUDIT [Company_Security_Audit]

    ADD (FAILED_LOGIN_GROUP),

    ADD (SUCCESSFUL_LOGIN_GROUP),

    ADD (DATABASE_OBJECT_CHANGE_GROUP); 

-- Tracks DROP/ALTER table

-- 3. Enable it

ALTER SERVER AUDIT [Company_Security_Audit] WITH (STATE = ON);

ALTER SERVER AUDIT SPECIFICATION [Track_Logins_And_Drops] WITH (STATE = ON);

35. Compliance: Meeting HIPAA and SOC2 Standards

If you are managing 100TB of Protected Health Information (PHI) or financial data:

Business Associate Agreement (BAA): Before storing PHI, your company must sign a BAA with AWS.

IAM Database Authentication: Use this to eliminate "Shared Passwords." Users log in using an AWS STS token that expires every 15 minutes.

Data Masking: Use Dynamic Data Masking (DDM) to hide sensitive data (like credit card numbers) from DBAs and developers while allowing them to see the rest of the row.

Script: Masking a Column

ALTER TABLE Users 

ALTER COLUMN SocialSecurityNumber ADD MASKED WITH (FUNCTION = 'partial(0, "XXX-XX-", 4)');

36. Monitoring Security with AWS GuardDuty

AWS GuardDuty now supports RDS Protection. It uses Machine Learning to analyze your login patterns. If it sees a login from a "Tor" exit node or an unusual IP address trying to brute-force your 100TB database, it will trigger an alert.

37. The "Master Key" Philosophy: Key Rotation

A security best practice is to rotate your encryption keys every year.

AWS KMS: You can enable "Automatic Key Rotation" with a single click. AWS will keep the old key to decrypt old snapshots but use the new key for all new data.

TDE: Rotation is manual and requires re-encrypting the database—a process that can take days for a 100TB database. This is another reason to prefer KMS over TDE.

---

38. Summary Checklist: The Secure DBA

Frequency	Task	Tool
Daily	Review CloudWatch for "Failed Login" spikes.	CloudWatch
Monthly	Review IAM permissions; remove "zombie" users.	AWS IAM
Quarterly	Run a "Data Discovery" scan for unmasked PII.	SQL Scripts
Yearly	Verify KMS Key Rotation is active.	AWS KMS

---