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.
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.
Copy bucket ARN and store it in a notepad file.
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.
It opens the Key Management Service and lists all keys create earlier.
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.
It gives you a warning message. Accept the warning and click on Disable key.
Click on 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.
It marks the status as pending deletion. AWS automatically deletes the key after 7 days.
Create an AWS KMS key for backup encryption
Click on Create Key in the KMS console and provides five steps to complete.
Add alias and description
In this step, enter the key alias and description for this key.
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.
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.
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.
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.
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.
Click on Finish, and it creates the AWS key for you.
Click on the key and note down the ARN. We will use this ARN to encrypt or decrypt backups in the S3 bucket.
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.
|
1 2 3 4 5 |
EXEC msdb.dbo.rds_backup_database @source_db_name = 'TestDB', @s3_arn_to_backup_to = 'arn:aws:s3:::rdstestsql/TestDB_Full29092019.bak', @kms_master_key_arn = 'arn:aws:kms:us-east-1:147081669821:key/4c9f0250-5661-462b-946f-6ec7be70aa8a', @overwrite_S3_backup_file = 1; |
By executing this command, it starts a task for taking backup. In the output, it shows the task details along with the key ARN.
Check the backup status using procedure msdb.dbo.rds_task_status command.
|
1 |
exec msdb..rds_task_status @task_id= 21 |
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.
Click on Add and select rdstestrole from the available users or roles list.
Now, we can see rdstestrole in the key user’s list.
Execute the backup command again in SSMS and check the backup status. Backup is successful now.
Go to the S3 bucket and verify the AWS RDS SQL Server backup file exists.
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.
|
1 2 3 |
exec msdb.dbo.rds_restore_database @restore_db_name='TestDB_Restore', @s3_arn_to_restore_from='arn:aws:s3:::rdstestsql/TestDB_Full29092019.bak' |
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.
|
1 2 3 4 |
exec msdb.dbo.rds_restore_database @restore_db_name='TestDB_Restore', @s3_arn_to_restore_from='arn:aws:s3:::rdstestsql/TestDB_Full29092019.bak', @kms_master_key_arn = 'arn:aws:kms:us-east-1:147081669821:key/4c9f0250-5661-462b-946f-6ec7be70aa8a' |
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.
Refresh the databases list in SSMS for AWS RDS SQL Server instance, and you can see the restored database is online.
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.
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.
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023