Secure RDS SQL Server: KMS, TLS, security groups

Sign in and start the SQL lab:

cd C:\aws-lab
aws login --profile saat-admin
$env:AWS_PROFILE = 'saat-admin'
$v = Get-Content C:\aws-lab\saat-vpc.json | ConvertFrom-Json
.\Start-SqlLab.ps1

1Check encryption at rest and the certificate

Paste this whole block:

aws rds describe-db-instances --db-instance-identifier saat-sql --query "DBInstances[0].[StorageEncrypted,CACertificateIdentifier,CertificateDetails.ValidTill]" --output text
aws rds describe-db-instances --db-instance-identifier saat-sql --query "DBInstances[0].KmsKeyId" --output text | ForEach-Object { aws kms describe-key --key-id $_ --query "KeyMetadata.[KeyManager,Description]" --output text }
aws rds describe-certificates --query "Certificates[?CertificateIdentifier=='rds-ca-rsa2048-g1'].[CertificateIdentifier,ValidTill,CustomerOverride]" --output text

You should see something like this:

True    rds-ca-rsa2048-g1       2027-10-20T08:12:00+00:00
AWS     Default key that protects my RDS database volumes when no other key is defined
rds-ca-rsa2048-g1       2061-05-25T23:34:57+00:00     False

Storage uses the AWS managed key aws/rds. Production often uses a customer managed key so access to snapshots can be controlled and audited separately. The server certificate expires in about a year and renews automatically; the CA itself lasts much longer.

2Force TLS on every connection

Paste this whole block. The reboot takes a few minutes:

'[ { "ParameterName": "rds.force_ssl", "ParameterValue": "1", "ApplyMethod": "pending-reboot" } ]' | Set-Content force-ssl.json
aws rds modify-db-parameter-group --db-parameter-group-name saat-sqlserver-ex-16 --parameters file://force-ssl.json | Out-Null
Start-Sleep 20
aws rds describe-db-instances --db-instance-identifier saat-sql --query "DBInstances[0].DBParameterGroups[0].ParameterApplyStatus" --output text
aws rds reboot-db-instance --db-instance-identifier saat-sql --query "DBInstance.DBInstanceStatus" --output text
aws rds wait db-instance-available --db-instance-identifier saat-sql
aws rds describe-db-instances --db-instance-identifier saat-sql --query "DBInstances[0].DBParameterGroups[0].ParameterApplyStatus" --output text
sqlcmd -S "127.0.0.1,11433" -U saatadmin -C -N true -h -1 -Q "SET NOCOUNT ON; SELECT encrypt_option FROM sys.dm_exec_connections WHERE session_id = @@SPID"

You should see something like this:

pending-reboot
rebooting
in-sync
TRUE

The tunnel window keeps working through the reboot, because each new connection is forwarded fresh. From now on, any client that doesn’t encrypt is refused. -C still trusts the certificate without checking it, because your laptop connects to 127.0.0.1 rather than the RDS name; apps inside the VPC should validate the certificate against the RDS CA bundle.

3Create a least-privilege app login

Paste this whole block:

$appPw = aws secretsmanager get-random-password --password-length 24 --exclude-punctuation --require-each-included-type --query RandomPassword --output text
sqlcmd -S "127.0.0.1,11433" -U saatadmin -C -Q "CREATE LOGIN saat_app WITH PASSWORD = '$appPw', DEFAULT_DATABASE = SaatShop"
sqlcmd -S "127.0.0.1,11433" -U saatadmin -C -d SaatShop -Q "CREATE USER saat_app FOR LOGIN saat_app; ALTER ROLE db_datareader ADD MEMBER saat_app; ALTER ROLE db_datawriter ADD MEMBER saat_app;"
$adminPw = $env:SQLCMDPASSWORD; $env:SQLCMDPASSWORD = $appPw
sqlcmd -S "127.0.0.1,11433" -U saat_app -C -d SaatShop -h -1 -Q "SET NOCOUNT ON; SELECT COUNT(*) FROM dbo.Orders"
sqlcmd -S "127.0.0.1,11433" -U saat_app -C -d SaatShop -Q "CREATE TABLE dbo.Hack (Id int)"
$env:SQLCMDPASSWORD = $adminPw

You should see something like this:

3
Msg 262, Level 14, State 1, Server EC2AMAZ-ABC123, Line 1
CREATE TABLE permission denied in database 'SaatShop'.

The app login reads the three orders but can’t change the schema. If the app is ever compromised, the attacker gets row access to one database, not control of the server. In production the password goes into Secrets Manager and the app reads it with its role (Lesson 31).

4Review the network rule

Paste this whole block:

aws ec2 describe-security-group-rules --filters "Name=group-id,Values=$($v.DbSg)" --query 'SecurityGroupRules[?IsEgress==`false`].[IpProtocol,FromPort,ReferencedGroupInfo.GroupId,CidrIpv4]' --output text
$v.AppSg

You should see something like this:

tcp     1433    sg-0c1d2e3f4a5b60003    None
sg-0c1d2e3f4a5b60003

One inbound rule: TCP 1433 from saat-app-sg, no CIDR ranges at all. That’s the shape to aim for in production too: reference security groups, not IP ranges, and give admins access through a controlled path.

✅ Checkpoint: Storage is encrypted, rds.force_ssl is in-sync and your session shows encrypt_option TRUE, saat_app can read but not create tables, and saat-db-sg allows 1433 only from saat-app-sg.