aws rds 加密_AWS RDS SQL Server中的加密备份和还原

aws rds 加密

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:

在文章“ 在AWS RDS SQL Server中恢复数据”中 ，我们探讨了AWS RDS SQL Server数据库的本机备份和还原过程。 它涉及以下步骤：

• Take full database backup into S3 bucket
  将完整的数据库备份到S3存储桶中
• Restore backup from S3 bucket in RDS instance
  从RDS实例中的S3存储桶还原备份

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?

您已向组织中的用户授予了对S3存储桶的访问权限，以上传，下载数据。 我们也在同一S3存储桶中进行了数据库备份。 任何访问S3存储桶的用户都可以下载数据库备份文件，并将其还原以检索数据。 我们无法更改S3存储桶，我们想保护备份文件。 你知道怎么做吗？

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实例 (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.

我在us-east-1f可用区中有myrdsinstance RDS SQL实例。 它正在运行SQL Server Express版本。

S3斗 (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存储桶来存储备份文件。 在本文中，我们使用[rdstestsql] S3存储桶。 当前，它不包含任何文件。

Copy bucket ARN and store it in a notepad file.

复制存储桶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） 。 让我们对此进行概述。

AWS密钥管理服务（AWS KMS） (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:

KMS是AWS中的一项服务，用于创建，删除和控制密钥以加密存储在S3存储桶中的数据。 它在AWS中提供了以下好处：

• It is a fully managed service from AWS. We can generate the keys, define user permissions, durability and security of the keys
  它是AWS的完全托管服务。 我们可以生成密钥，定义用户权限，密钥的持久性和安全性
• 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
  AWS为KMS提供集中管理。 我们可以使用AWS控制台和CLI使用KMS控制台导入，删除，旋转，管理密钥
• KMS integrates with the Cloudtrail and stores all API request to meet compliance requirement and auditing
  KMS与Cloudtrail集成并存储所有API请求，以满足合规性要求和审核
• We can use it to encrypt or decrypt data using AWS KMS keys
  我们可以使用它通过AWS KMS密钥来加密或解密数据
• 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
  这是一项低成本服务，您不需要在特定期间内做出承诺。 您可以开始使用它，每月仅需支付$ 1即可将密钥存储在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.

AWS不允许您立即删除密钥。 它会在特定持续时间内保留密钥，以便您可以根据需要使用它来解密对象。 它将密钥保留至少7天，然后将其删除。

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

我将等待时间设置为7天，以删除AWS中的KMS密钥。 我们可以选择7到30天之间的任何时间段。

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

它将状态标记为待删除。 AWS将在7天后自动删除密钥。

创建用于备份加密的AWS KMS密钥 (Create an AWS KMS key for backup encryption)

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

在KMS控制台中单击“创建密钥”，并提供五个步骤来完成。

1. 添加别名和描述 (Add alias and description)

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

   在此步骤中，输入密钥别名和该密钥的描述。

   

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.

   在第二步中，为此键添加一个标签。 它是密钥的元数据。 指定标签的键和值。

   

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.

   在此步骤中，我们定义可以管理此密钥的用户或角色。 它列出了您可以选择的所有可用用户和角色。

   

   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.

   它还为您提供了删除密钥的选项。 我们可以允许或拒绝密钥管理员删除此密钥。 默认情况下，密钥管理员可以删除此密钥。

   

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.

   在此步骤中，我们可以选择可以使用此密钥进行加密和解密的用户或角色。 此时，我也将跳过此步骤。

   

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.

   在步骤5中，它将为您提供密钥策略的JSON语句。 您可以查看此JSON并根据需要进行更改。

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

   仅当您对JSON语言有信心时，才应修改JSON，否则请在KMS GUI中进行更改。

   

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

   单击“完成”，它会为您创建AWS密钥。

   

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

   单击键并记下ARN。 我们将使用此ARN对S3存储桶中的备份进行加密或解密。

   

在AWS RDS SQL Server中进行加密的数据库备份 (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.

我们使用msdb.dbo.rds_backup_database Procedure在AWS RDS SQL Server中进行本机备份。 在进行数据库备份时，我们使用@key_master_key_arn指定密钥ARN。 它是一个可选参数。 如果我们不指定此参数，则AWS RDS将进行不加密的备份。

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.

通过执行此命令，它开始执行备份任务。 在输出中，它显示任务详细信息以及键ARN。

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

使用过程msdb.dbo.rds_task_status命令检查备份状态。

exec msdb..rds_task_status @task_id= 21

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

AWS RDS SQL Server备份任务失败，并显示以下错误消息：

[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

[2019-09-30 04：41：22.127]由于任务失败或与RDS自动备份的首选备份窗口重叠而终止了任务。 [2019-09-30 04：41：22.127]任务已中止[2019-09-30 04：41：22.127]用户：arn：aws：sts :: 147081669821：assumed-role / rdstestrole / RDS-SqlServerBackupRestore不是授权执行：kms：Describe资源关键：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.

根据错误消息，没有授权rdstestrole使用此密钥。 创建KMS密钥以将此密钥用于加密或解密目的时，我们没有添加任何用户或角色。

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.

单击添加，然后从可用的用户或角色列表中选择rdstestrole。

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

现在，我们可以在关键用户列表中看到rdstestrole。

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

在SSMS中再次执行backup命令，并检查备份状态。 现在备份成功。

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

转到S3存储桶，并验证AWS RDS SQL Server备份文件是否存在。

在AWS RDS SQL Server中还原加密的备份 (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.

假设具有S3存储桶访问权限的用户下载此备份文件并尝试还原。 该用户没有解密它的密钥。 它使用不带密钥的msdb.dbo.rds_restore_database命令来解密备份文件。

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.

数据库还原失败，因为SQL Server无法处理媒体系列。 它无法验证备份文件的内容。 我们已经加密了备份文件。 因此，SQL Server无法读取备份的内容并还原数据库。 它需要密钥来解密对象并还原数据库。

[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.

[2019-09-30 04：57：22.593]设备'79EE41FA-66AE-4088-BD5D-4FCD40D82DD1'上的媒体系列格式不正确。 SQL Server无法处理此媒体系列。 [2019-09-30 04：57：22.600] RESTORE FILELIST正在异常终止。 [2019-09-30 04：57：22.793]由于任务失败或并发RESTORE_DB请求而中止了任务。 [2019-09-30 04：57：22.913]任务已中止[2019-09-30 04：57：22.917]检索到空的还原文件列表结果。

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.

使用密钥ARN在AWS RDS SQL Server中执行restore命令。 该密钥ARN应该与我们在进行数据库备份时使用的密钥相同。

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.

如果正在执行数据库还原的用户或角色没有使用KMS密钥的权限，我们可能会得到以下错误备份。 如果我们使用其他用户或角色进行还原，则应为数据库备份密钥提供对该用户或角色的访问。

[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

[2019-09-29 16：14：22.413]由于任务失败或与RDS自动备份的首选备份窗口重叠而终止了任务。 [2019-09-29 16：14：22.420]任务已中止[2019-09-29 16：14：22.427]用户：arn：aws：sts :: 147081669821：assumed-role / rdstestrole / RDS-SqlServerBackupRestore不是授权执行：kms：DescribeKey资源：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.

检查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.

刷新SSMS中用于AWS RDS SQL Server实例的数据库列表，您可以看到已还原的数据库处于联机状态。

AWS CloudTrail查看KMS事件 (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.

如前所述，我们可以使用CloudTrail服务查看KMS的关键事件。 转到服务，然后在“ 管理和治理”部分中，单击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，我们可以在仪表板上查看事件历史记录。 您可以看到KMS的事件，例如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.

展开特定的按键事件，然后单击“查看事件”以获取事件详细信息。 它以JSON格式提供事件信息，如下图所示。

结论 (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.

在本文中，我们探讨了AWS中的密钥管理服务及其在AWS RDS SQL Server中用于备份加密，解密的用法。 您应该利用备份加密来保护敏感的数据库备份并防止未经授权的访问。

翻译自: https://www.sqlshack.com/encrypted-backup-and-restore-in-aws-rds-sql-server/

aws rds 加密