Rajendra Gupta
Verify backup file in S3 bucket

Encrypted Backup and Restore in AWS RDS SQL Server

October 4, 2019 by

In the article, Recover Data in AWS RDS SQL Server, we explored the process of native backup and restoration for the AWS RDS SQL Server database. It involves the following steps:

  • Take full database backup into S3 bucket
  • Restore backup from S3 bucket in RDS instance

Consider a scenario that your database contains customer personal data such as account number, credit card details, social security number. You do not want to expose personal identification data to everyone.

You have given access to an S3 bucket to users in the organization to upload, download data. We took database backup as well in the same S3 bucket. Any user access to the S3 bucket can download the database backup file and restore it to retrieve the data. We cannot change the S3 bucket, and we want to secure the backup file. Do you know how to do it?

In this article, let’s explore the process of securing the database backup file.

Prerequisites

You should have the following set up before proceeding with this article:

Amazon RDS SQL Server instance

I have myrdsinstance RDS SQL instance for this article in a us-east-1f availability zone. It is running the SQL Server Express edition.

Amazon RDS SQL Server instance

S3 bucket

You should have an S3 bucket to store the backup file. In this article, we use [rdstestsql] S3 bucket. Currently, it does not contain any files.

S3 bucket

Copy bucket ARN and store it in a notepad file.

S3 bucket ARN

We will use the AWS Key Management Service (AWS KMS) in this article. Let’s take an overview of this.

AWS Key Management Service (AWS KMS)

KMS is a service in AWS to create, delete and control keys to encrypt data stored in the S3 bucket. It provides the following benefits in AWS:

  • It is a fully managed service from AWS. We can generate the keys, define user permissions, durability and security of the keys
  • AWS provides centralized management for the KMS. We can use KMS console to import, delete, rotate, manage the keys using both AWS console and CLI
  • KMS integrates with the Cloudtrail and stores all API request to meet compliance requirement and auditing
  • We can use it to encrypt or decrypt data using AWS KMS keys
  • It is a low-cost service, and you do not require a commitment for a specific duration. You can start using it and pay only $1\month to store a key in AWS KMS

You can find key management service in the Security, Identity and Compliance section of services.

AWS Key Management Service

It opens the Key Management Service and lists all keys create earlier.

existing keys

Let’s delete this key so that we can start from scratch. We cannot directly delete the key. We need to disable it and then delete it after a specific time.

Select the key and disable it.

Disable key

It gives you a warning message. Accept the warning and click on Disable key.

warning message

Click on Schedule key deletion.

Schedule key deletion

AWS does not allow you to delete the keys instantaneously. It retains the keys for the specific duration so that you can use it to decrypt the object if required. It keeps the keys for a minimum of 7 days and removes it after that.

I set the waiting time 7 days to delete the KMS key in AWS. We can choose any period between 7 and 30 days.

Schedule key deletion waiting time

It marks the status as pending deletion. AWS automatically deletes the key after 7 days.

Pending deletion message

Create an AWS KMS key for backup encryption

Click on Create Key in the KMS console and provides five steps to complete.

  1. Add alias and description

    In this step, enter the key alias and description for this key.

    Add alias and description

  2. Add tags

    In the second step, add a tag for this key. It is Metadata for the key. Specify the tag key and value for the tag.

    Add tags

  3. Define key administrative permissions

    In this step, we define the user or role that can administer this key. It lists all available users and roles that you can select.

    Define key administrative permissions

    It also gives you an option for key deletion. We can allow or deny key administrators to delete this key. By default, key administrators can delete this key.

    Key deletion

  4. Define key usage permissions

    In this step, we can select the user or role that can use this key for encryption and decryption. I will skip this step as well at this point.

    Define key usage permissions

  5. Review and edit key policy

    In step 5, it gives you a JSON statement for the key policy. You can review this JSON and make changes if required.

    You should modify JSON only if you are confident in JSON language otherwise make changes in the KMS GUI.

    Review and edit key policy

    Click on Finish, and it creates the AWS key for you.

    Customer managed key

    Click on the key and note down the ARN. We will use this ARN to encrypt or decrypt backups in the S3 bucket.

    Key ARN

Take Encrypted database backup in AWS RDS SQL Server

We use msdb.dbo.rds_backup_database Procedure to take native backups in AWS RDS SQL Server. We use @key_master_key_arn to specify key ARN while taking database backup. It is an optional argument. If we do not specify this argument, AWS RDS takes backup without encryption.

By executing this command, it starts a task for taking backup. In the output, it shows the task details along with the key ARN.

backup task

Check the backup status using procedure msdb.dbo.rds_task_status command.

error message

AWS RDS SQL Server backup task failed with the following error message:

[2019-09-30 04:41:22.127] Aborted the task because of a task failure or an overlap with your preferred backup window for RDS automated backup. [2019-09-30 04:41:22.127] Task has been aborted [2019-09-30 04:41:22.127] User: arn:aws:sts::147081669821:assumed-role/rdstestrole/RDS-SqlServerBackupRestore is not authorized to perform: kms:DescribeKey on resource: arn:aws:kms:us-east-1:147081669821:key/4c9f0250-5661-462b-946f-6ec7be70aa8a

According to the error message, rdstestrole is not authorized to use this key. We did not add any user or role while creating a KMS key to use this key for encryption or decryption purposes.

In the key, go to key users sections, and we do not see any resources here.

key users

Click on Add and select rdstestrole from the available users or roles list.

Add key users

Now, we can see rdstestrole in the key user’s list.

Add role in KMS user

Execute the backup command again in SSMS and check the backup status. Backup is successful now.

backup status

Go to the S3 bucket and verify the AWS RDS SQL Server backup file exists.

Verify backup file in S3 bucket

Restore encrypted backups in AWS RDS SQL Server

Suppose user with access on S3 bucket downloads this backup file and tries to restore. This user does not have the key to decrypt it. It uses the msdb.dbo.rds_restore_database command without a key to decrypt backup file.

Database restoration failed because SQL Server cannot process the media family. It cannot verify the content of the backup file. We have encrypted the backup file. Therefore, SQL Server cannot read the content of backup and restore the database. It requires the key to decrypt the object and restore database.

[2019-09-30 04:57:22.593] The media family on device ’79EE41FA-66AE-4088-BD5D-4FCD40D82DD1′ is incorrectly formed. SQL Server cannot process this media family. [2019-09-30 04:57:22.600] RESTORE FILELIST is terminating abnormally. [2019-09-30 04:57:22.793] Aborted the task because of a task failure or a concurrent RESTORE_DB request. [2019-09-30 04:57:22.913] Task has been aborted [2019-09-30 04:57:22.917] Empty restore file list result retrieved.

Execute the restore command in AWS RDS SQL Server with the key ARN. This key ARN should be same that we used while taking database backup.

We might get the following error backup if the user or role in which we are performing DB restore does not have permission to use the KMS key. If we are using a different user or role for restoration, we should provide access to that user or role for the database backup key.

[2019-09-29 16:14:22.413] Aborted the task because of a task failure or an overlap with your preferred backup window for RDS automated backup. [2019-09-29 16:14:22.420] Task has been aborted [2019-09-29 16:14:22.427] User: arn:aws:sts::147081669821:assumed-role/rdstestrole/RDS-SqlServerBackupRestore is not authorized to perform: kms:DescribeKey on resource: arn:aws:kms:us-east-1:147081669821:key/c83d9ec1-c447-41d0-8bf5-d9a8e7bdb4cf

Check the status of database restoration for AWS RDS SQL Server, and we can see it is successful.

Restore encrypted backups in AWS RDS SQL Server

Refresh the databases list in SSMS for AWS RDS SQL Server instance, and you can see the restored database is online.

Verify database

AWS CloudTrail to view KMS events

As discussed earlier, we can use CloudTrail service to view the key events for KMS. Go to Services and in the Management & Governance section, click on CloudTrail.

It launches the CloudTrail, and we can see event history on the dashboard. You can see events for KMS such as Createkey, Createalias, Updatekeydescription, PutkeyPolicy.

CloudTrail service

Expand the particular key event and click on View event to get event details. it gives event information in a JSON format as shown in the following image. View event details

Conclusion

In this article, we explored the Key management service in AWS and its usage for backup encryption, decryption in AWS RDS SQL Server. You should make use for backup encryption to protect sensitive database backup and prevent from unauthorized access.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

72 Views