Ranga Babu
option group to enable Transparent Data Encryption in AWS RDS SQL Server

Transparent Data Encryption (TDE) in AWS RDS SQL Server

July 23, 2019 by

In this article, we will review Transparent Data Encryption (TDE) in AWS RDS SQL Server. AWS RDS supports TDE on SQL Server Enterprise edition of 2012,2014,2016 and 2017 editions.

The following are the steps involved in enabling Transparent Data Encryption (TDE) on Amazon RDS SQL Server instance.

  1. Creating an option group and add Transparent Data Encryption (TDE) option
  2. Associating the option group to the DB instance
  3. Creating database encryption key (DEK) on the database and enable encryption on the database

Let’s go over these steps one by one.

Creating an option group and add TDE option

Log in to the AWS console. Search for RDS in the services and click on RDS managed relational database service as shown in the below image.

RDS Service

In the AWS RDS console, Click on Options Groups. This page shows a list of options groups which has default option groups created by the system and the custom option groups created by users.

Now we need to create a custom option group.

Click on Create Group.

option groups in AWS RDS SQL Server

Enter the name of the option group, description and select the engine as “sqlserver-ee” as Transparent Data Encryption (TDE) in RDS is supported only in SQL Server enterprise edition. Select the major engine version. If you are using SQL Server 2017 enterprise edition, then select the major version as 14.00. Click on Create button as shown below.

option group to enable Transparent Data Encryption in AWS RDS

Once the option group is created, navigate to ‘Option groups’ page in the AWS RDS console. Select the option you just created and click on the Add Option.

option group to enable Transparent Data Encryption in AWS RDS SQL Server

Select the option TRANSPARENT_DATA_ENCRYPTION and select Immediately in scheduling as the option group is a new one and there are no associated DB instances to the option group. Click on Add option.

Transparent Data Encryption in AWS RDS option group

Associating the option group to the database instance

Once we create the option group with option TRANSPARENT_DATA_ENCRYPTION, we need to associate the option group to the database instance.

Click on the Databases in the RDS console to navigate to the databases page. In the databases page, you will see the list of database instances. Select the database instance on which you want to enable Transparent Data Encryption (TDE). Click on Modify.

RDS SQL Server instance

In the database options section, select the Option group you created above and click on Continue at the bottom of the page.

associate option group with Transparent Data Encryption option to the database instance

In the scheduling modifications section, select apply during the next scheduled maintenance window if you want to associate the option group to the database instance during the maintenance window.

Select apply immediately if you want to associate the option group to the database instance immediately. Any other pending database instance modifications will also be applied along with the option group. The summary of modifications will show the list of the modifications. Please select the scheduling of modification option with care as some modifications may lead to database instance restart.

As I am doing it for demo purpose, I am applying the modifications immediately. Click on Modify DB Instance.

modify the database instance to enable Transparent Data Encryption

The database instance will go into modifying state as soon as click on Modify DB Instance. Wait until the status is changed and instance becomes Available.

Once the instance is available, log in to the database instance using the SQL Server management studio.

By default, a certificate is already created on the master database when you associate option group with TRANSPARENT_DATA_ENCRYPTION enabled to the DB instance.

The name of the certificate will be like “RDSTDECertificate” suffixed by timestamp. Please refer to the below image. We cannot create a certificate in the master database unlike in on-premises SQL Server. It throws error “user does not have permission to perform this action”

certificate on master database

Execute the following script on the master database to know the name of the certificate.

Creating database encryption key (DEK) on the database and enable encryption on the database

Now we have the certificate in the master database which will be used to create the database encryption key.

Use the database on which you are going to enable encryption and execute the following T-SQL script to create a database encryption key. In my case, the name of the certificate created on the master database is “RDSTDECertificate20190720T093919”. Replace the certificate name with yours.

Once the database encryption key is created, enable the database for encryption. Execute the following T-SQL script and replace “TDEDemo” with the name the database on which you are going to enable encryption.

Execute the following script to verify if the encryption on the database is enabled or not.

Removing Transparent Data Encryption (TDE) on AWS RDS instance

We cannot modify the instance to associate to the default option group when an encrypted object exists in the database. To change the associated option group to default or another option group with Transparent Data Encryption (TDE) disabled, we must remove encryption on the databases.

error modifying the database instance

To disable TDE on the instance, remove the databases from encryption first. Execute the following script to remove encryption on the database.

Check if the decryption is completed or not. Execute the following query. encryption_state should be 1

Drop the encryption key on the database.

Once all the objects in the database are decrypted and encrypted logs are truncated, you can modify the database instance to associate the default option group to the database instance.

Conclusion

In this article, we explored how to enable Transparent Data Encryption (TDE) option in AWS RDS SQL Server and enable the database for encryption using the default certificate created in the master database. In case you have any questions, please feel free to ask in the comment section below.

Ranga Babu

Ranga Babu

SQL Server DBA, Developer with good experience in SQL Server administration, development, performance tuning, monitoring, high availability and disaster recovery technologies
Ranga Babu
168 Views