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.
- Creating an option group and add Transparent Data Encryption (TDE) option
- Associating the option group to the DB instance
- 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.
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.
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.
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.
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.
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.
In the database options section, select the Option group you created above and click on Continue at the bottom of the page.
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.
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”
Execute the following script on the master database to know the name of the certificate.
|
1 2 3 4 |
USE [master] GO SELECT name FROM sys.certificates WHERE name LIKE 'RDSTDECertificate%' GO |
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.
|
1 2 3 4 5 6 |
USE [TDEDemo] GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE [RDSTDECertificate20190720T093919] GO |
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.
|
1 2 3 |
ALTER DATABASE [TDEDemo] SET ENCRYPTION ON GO |
Execute the following script to verify if the encryption on the database is enabled or not.
|
1 2 3 4 5 6 |
USE [master] GO SELECT name FROM sys.databases WHERE is_encrypted = 1 GO SELECT db_name(database_id) as DatabaseName, * FROM sys.dm_database_encryption_keys GO |
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.
To disable TDE on the instance, remove the databases from encryption first. Execute the following script to remove encryption on the database.
|
1 2 3 |
ALTER DATABASE [TDEDemo] SET ENCRYPTION OFF GO |
Check if the decryption is completed or not. Execute the following query. encryption_state should be 1
|
1 2 |
SELECT db_name(database_id) as DatabaseName, * FROM sys.dm_database_encryption_keys GO |
Drop the encryption key on the database.
|
1 2 3 4 5 6 7 |
USE [TDEDemo] GO DROP DATABASE ENCRYPTION KEY GO ALTER DATABASE [TDEDemo] SET RECOVERY SIMPLE GO |
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.
- Geo Replication on Transparent Data Encryption (TDE) enabled Azure SQL databases - October 24, 2019
- Overview of the Collate SQL command - October 22, 2019
- Recover a lost SA password - September 20, 2019