Amazon RDS for SQL Server – Encrypted Backup/Restore

19 Sep, 2016 | 6 minutes read
|

Amazon Relational Database Service (Amazon RDS) supports native backup and restore for Microsoft SQL Server databases using full backup files (.bak files). You can import and export SQL Server databases in a single, easily portable file. After you create a full backup of your on-premises database, upload it on Amazon Simple Storage Service (Amazon S3), and then restore the backup file onto an existing Amazon RDS DB instance running SQL Server.

The reverse flow is also possible. You can back up an Amazon RDS SQL Server database, store it on Amazon S3, and then restore the backup file onto an on-premises server, or a different Amazon RDS DB instance running SQL Server.

The following diagram shows the supported scenarios.

Amazon RDS for SQL Server - Encrypted Backup/Restore

For more information, please visit: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html

There are several important rule constraints when restoring RDS instances as a Database engineer:

  • You can’t back up to, or restore from, an Amazon S3 bucket in a different region than your Amazon RDS DB instance
  • You can’t restore a backup file to the same RDS DB instance that was used to create the backup file. Instead, restore the backup file to a new RDS DB instance. Renaming the database is not a workaround for this limitation;
  • You can’t restore the same backup file to the same RDS DB instance multiple times. That is, you can’t restore a backup file to a DB instance that already contains the database that you are restoring. Renaming the database is not a workaround for this limitation.

The sections below depict the procedure for restoring on-premises SQL Server database backup into an AWS RDS SQL Server instance running in the cloud.

Prepare RDS instance

In order to prepare the RDS instance for restoration we shall execute the following steps:

  • Create SQL Server RDS instance. In database options, leave the default Option Group
  • Remember the username and password specified during RDS instance creation. You will need them to connect to the instance;
  • If you are not using bastion host (in this walkthrough we are not) add a security group that allows external connections to the instance (port 1433 to be available) in order to connect from MS SQL Management studio.

Creation of the database takes 10-15 minutes.

Create Master Encryption Key (KMS)

If you don’t have an existing AWS Key Management Service (KMS) encryption key, you should create one. For that purpose, under IAM -> Encryption keys, follow the screen wizard and create a new encryption key.

Create an Options group and add an Option

SQL Server Native Backup/Restore procedure with Amazon S3 located backups supports backup encryption using KMS across all SQL Server editions.

You can enable this feature by adding the SQL_SERVER_BACKUP_RESTORE option to an Option Group and associating the Option Group with your RDS SQL Server instance. In particular:

  • Create Option Group
  • Add SQL_SERVER_BACKUP_RESTORE option, select S3 bucket for the backup/restore file, select encryption with the KMS key you created in the previous step (used *.bak files encryption/decryption), and finally, choose existing IAM role or create a new one.

Your screen, for Adding the SQL_SERVER_BACKUP_RESTORE option, should look like this:

The screen for Adding the SQL_SERVER_BACKUP_RESTORE option

We’ve created a new role iw_backup_restore_role with permissions to List, Get, and Put objects in the S3 bucket iw-sql-server-client-encrypted-files. Also, we’ve enabled Encryption, using master key sql_backup_restore (previously created under IAM – Encryption keys).

Note: iw_backup_restore_role role policy shall contain the following policy statements:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "kms:Decrypt",
        "kms:Encrypt",
        "kms:DescribeKey",
        "kms:GenerateDataKey"
      ],
      "Resource": [
        "arn:aws:kms:us-east-1:xxxxxxxxxxxx:key\/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
      ]
    },
    {
      "Effect": "Allow",
      "Action": [
        "s3:ListBucket",
        "s3:GetBucketLocation"
      ],
      "Resource": [
        "arn:aws:s3:::iw-sql-server-client-encrypted-files"
      ]
    },
    {
      "Effect": "Allow",
      "Action": [
        "s3:GetObjectMetaData",
        "s3:GetObject",
        "s3:PutObject",
        "s3:ListMultipartUploadParts",
        "s3:AbortMultipartUpload"
      ],
      "Resource": [
        "arn:aws:s3:::iw-sql-server-client-encrypted-files\/*"
      ]
    }
  ]
}

Add Option group to RDS instance

After the Option Group is created and the option for SQL_SERVER_BACKUP_RESTORE is added to the group, the group must be assigned to the RDS instance. In order to do that, you have to remove the current default Option Group and add the new one.

Select your SQL Server RDS instance and choose Actions -> Modify. On the modification screen, navigate to the section Database Options and select your Option Group (iw-backup-restore-option-group):

The section Database Options where you select your Option Group (iw-backup-restore-option-group)

Check the option Apply immediately and choose Continue -> Apply changes. Under Actions -> See details, you can monitor changes applying to the Options Group.

After the Option Group becomes available, you can use the instance’s endpoint in order to connect from MS SQL Management Studio.

instance’s endpoint

Upload client-side encrypted file to S3

Having in mind that we configured the SQL_SERVER_BACKUP_RESTORE option with Encryption, the backup files uploaded to the S3 bucket MUST be encrypted. However, here is the tricky part. AWS supports two types of encryption for the objects stored in the bucket: Server Side Encryption (SSE) and Client Side Encryption (CSE). In order to use the SQL Server backup/restore option the backup files MUST be Client Side Encrypted with the KMS key configured.

Client Side, KMS Encrypted backup file can be created only by using AWS SDK (.NET, Java, Ruby). The code excerpt below demonstrates how to do that:

    string bucketName = "iw-sql-server-client-encrypted-files";
    string backupFileName = "<filename>.bak";
    var client = new Amazon.KeyManagementService.AmazonKeyManagementServiceClient(RegionEndpoint.USEast1);
    var algorithm = new KMSAlgorithm(client, "<<kms-key-name-goes-here>>");
    EncryptionMaterials encryptionMaterials = new EncryptionMaterials(algorithm);
    using (var s3client = new AmazonS3EncryptionClient(encryptionMaterials))
    {
        PutObjectRequest request = new PutObjectRequest();
        request.Key = Path.GetFileName(backupFileName);
        request.FilePath = @"EncryptedBackups\" + backupFileName;
        request.BucketName = bucketName;
        s3client.PutObject(request);
    }

You can check if the uploaded file is encrypted. In the S3 bucket, select the uploaded file and check the info under the Metadata properties. You should expect several metadata entries having the following keys:

x-amz-meta-x-amz-key

x-amz-meta-x-amz-matdesc

x-amz-meta-x-amz-iv

Restore database from client side encrypted backup file located on S3

Once we have the SQL Server backup file uploaded in S3, we can proceed with the actual restoring procedure. In order to restore the RDS database from the encrypted backup file stored in the S3 bucket, you must invoke the T-SQL procedure with arguments specifying the correct KMS key name and S3 backup location.

Here is the T-SQL command that is used for database Restore:

exec msdb.dbo.rds_restore_database
@restore_db_name=’database_name’,
@s3_arn_to_restore_from=’arn:aws:s3:::bucket_name/file_name_and_extension’,
@kms_master_key_arn=’arn:aws:kms:region:account-id:key/key-id’;

In our case, that would be:

exec msdb.dbo.rds_restore_database
@restore_db_name=’savetool’,
@s3_arn_to_restore_from=’arn:aws:s3:::iw-sql-server-client-encrypted-files /<filename>.bak’,
@kms_master_key_arn=’arn:aws:kms:us-east-1:xxxxxxxxxxxx:key/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx’;

If you are doing something wrong during the database restore procedure, the errors logged are not very descriptive and user-friendly.

For example, if you try to restore from an SSE encrypted backup file, you will get an error similar to:

Aborted the task because of a task failure or a concurrent RESTORE_DB request. Object metadata (x-amz-key) is incomplete, and cannot decrypt the given S3 object.

If you try restoring the database without providing the key, you will get an error:

Task execution has started. The media family on device ‘A8CC538B-E965-472C-BA49-2F556993784B’ is incorrectly formed. SQL Server cannot process this media family. RESTORE FILELIST is terminating abnormally. Aborted the task because of a task failure or a concurrent RESTORE_DB request. <filename>.bak: S3 processing has been aborted Invalid attempt to read when no data is present. 

If you try to restore the database without providing the right key, you will get an error:

Aborted the task because of a task failure or a concurrent RESTORE_DB request.

Key ‘arn:aws:kms:us-east-1: xxxxxxxxxxxx:key/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx’ does not exist

If everything goes well, you will get the following successfully processing message trace: 

Task execution has started.

 <filename>.bak: Completed processing 96.74% of S3 chunks.

99 percent processed.

100 percent processed.

….

RESTORE DATABASE successfully processed 290 pages in 0.350 seconds (6.460 MB/sec).

<filename>.bak: S3 processing completed successfully

Command execution completed successfully.

The native Backup/Restore functionality has been a long waiting feature to be added to the set of existing RDS tools. Now that we have it, the companies are able to move their on-premises MS SQL databases into the AWS cloud by using well-defined, secure, and straightforward procedures.

Author :

Katerina Packova