Enable transparent data encryption in Amazon RDS for SQL Server - AWS Prescriptive Guidance
SummaryPrerequisites and limitationsArchitectureToolsEpicsRelated resources

Enable transparent data encryption in Amazon RDS for SQL Server

Created by Ranga Cherukuri (AWS)

Environment: PoC or pilot

Technologies: Security, identity, compliance; Databases

Workload: Microsoft

AWS services: Amazon RDS

Summary

This pattern describes how to implement transparent data encryption (TDE) in Amazon Relational Database Service (Amazon RDS) for SQL Server to encrypt data at rest.

Prerequisites and limitations

Prerequisites 

  • An active AWS account

  • An Amazon RDS for SQL Server DB instance

Product versions

Amazon RDS currently supports TDE for the following SQL Server versions and editions:

  • SQL Server 2012 Enterprise Edition

  • SQL Server 2014 Enterprise Edition

  • SQL Server 2016 Enterprise Edition

  • SQL Server 2017 Enterprise Edition

  • SQL Server 2019 Standard and Enterprise Editions

For the latest information about supported versions and editions, see Support for Transparent Data Encryption in SQL Server in the Amazon RDS documentation.

Architecture

Technology stack

  • Amazon RDS for SQL Server

Architecture

Architecture for enabling TDE for Amazon RDS for SQL Server databases

Tools

Tools

  • Microsoft SQL Server Management Studio (SSMS) is an integrated environment for managing a SQL Server infrastructure. It provides a user interface and a group of tools with rich script editors that interact with SQL Server.

Epics

TaskDescriptionSkills required

Open the Amazon RDS console.

Sign in to the AWS Management Console and open the Amazon RDS console.

Developer, DBA

Create an option group.

In the navigation pane, choose Option groups, Create group. Choose sqlserver-ee as the DB engine, and then select the engine version.

Developer, DBA

Add the TRANSPARENT_DATA_ENCRYPTION option.

Edit the option group you created and add the option called TRANSPARENT_DATA_ENCRYPTION.

Developer, DBA
TaskDescriptionSkills required

Choose the DB instance.

In the Amazon RDS console, in the navigation pane, choose Databases, and then choose the DB instance you want to associate with the option group.

Developer, DBA

Associate the DB instance with the option group.

Choose Modify, and then use the Option group setting to associate the SQL Server DB instance with the option group you created earlier.

Developer, DBA

Apply the changes.

Apply the changes immediately or during the next maintenance window, as desired.

Developer, DBA

Get the certificate name.

Get the default certificate name by using the following query.

USE [master]
GO
SELECT name FROM sys.certificates WHERE name LIKE 'RDSTDECertificate%'
GO
Developer, DBA
TaskDescriptionSkills required

Connect to the Amazon RDS for SQL Server DB instance using SSMS.

For instructions, see Using SSMS in the Microsoft documentation.

Developer, DBA

Create the database encryption key by using the default certificate.

Create a database encryption key by using the default certificate name you got earlier. Use the following T-SQL query to create a database encryption key. You can specify the AES_256 algorithm instead of AES_128.

USE [Databasename]
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE [certificatename]
GO
Developer, DBA

Enable the encryption on the database.

Use the following T-SQL query to enable database encryption.

ALTER DATABASE [Database Name]
SET ENCRYPTION ON
GO
Developer, DBA

Check the status of encryption.

Use the following T-SQL query to check the status of encryption.

SELECT DB_NAME(database_id) AS DatabaseName, encryption_state, percent_complete FROM sys.dm_database_encryption_keys
Developer, DBA

Related resources