Client-Side Encryption and Decryption of Microsoft SQL Server Backups for Use with Amazon RDS

This blog post walks you through how to securely encrypt a Microsoft SQL Server backup file and restore the encrypted backup to an Amazon RDS for SQL Server instance. You perform this process using Amazon Simple Storage Service (Amazon S3) and AWS Key Management Service (AWS KMS). This post details the encryption and steps required to encrypt the backup file, how to upload it to S3, and how to ready the encrypted backup for restoration by Amazon Relational Database Service (Amazon RDS). The post concludes with a link to another post that details step by step how to import the encrypted backup file into Amazon RDS.

Technologies involved
AWS Key Management Service (AWS KMS) is a managed service that makes it easy for you to create and control the encryption keys used to encrypt your data. It uses hardware security modules (HSMs) to protect the security of your keys. AWS KMS is integrated with several other AWS services to help you protect the data that you store with these services. AWS KMS is also integrated with AWS CloudTrail to provide you with logs of all key usage to help meet your regulatory and compliance needs.

Amazon Simple Storage Service (Amazon S3) is object storage built to store and retrieve any amount of data from anywhere—websites and mobile apps, corporate applications, and data from IoT sensors or devices. It’s designed to deliver 99.999999999 percent durability, and has many customers that each store billions of objects and exabytes of data. You can use it for media storage and distribution, as a data lake for big data analytics, as a backup target, and as the storage tier for serverless computing applications.

Amazon RDS for SQL Server makes it easy to set up, operate, and scale Microsoft SQL Server deployments in the cloud. With Amazon RDS, you can deploy multiple editions of SQL Server (2008 R2, 2012, 2014, and 2016) including Express, Web, Standard, and Enterprise, in minutes with cost-efficient and resizable compute capacity. Amazon RDS frees you up to focus on application development by managing time-consuming database administration tasks including provisioning, backups, software patching, monitoring, and hardware scaling.

Overall process
The following sections go into each step in greater detail, but overall the process for securely restoring an encrypted Microsoft SQL Server backup file to RDS is as so:

1. Generate a KMS data encryption key
2. Encrypt the backup file on-premises using the KMS-generated data encryption key
3. Upload the encrypted file to S3
4. Update the S3 object’s metadata with the object’s encryption details
5. Restore the backup to an instance of RDS

AWS KMS encryption
AWS KMS uses envelope encryption to secure customer data. KMS encrypts a customer data file using a data key (referred to in KMS terms as a data encryption key, DEK). KMS then encrypts the data key using a key encryption key (referred to in KMS terms as a customer master key, CMK). KMS then stores this encrypted data key alongside the encrypted customer data to serve future decryption operations.

Envelope encryption provides customers with the benefits of layered security combined with symmetric encryption. It reduces the effort required to rotate encryption keys, and it provides unique data encryption keys for every encrypted customer object.

AWS KMS generates data keys upon request and secures the data key by performing AES 256 encryption using your customer master key (CMK). You can create or import one or more customer master keys into KMS for use during encryption. The steps for creation of a CMK and generation of a data encryption key follow. After creation of the CMK, you can request a data encryption key from KMS by specifying the CMK you want to use and the specification for the data encryption key.

Note: Amazon RDS requires that you use AES-256 cipher block chaining (CBC) for backup encryption.

The generated data encryption key is returned as a JSON string. The unencrypted base 64–encoded encryption key is returned with a name of Plaintext. The encrypted version of the same key is returned as CiphertextBlob. This encrypted data key is stored later with the encrypted BAK file as S3 object metadata.

The illustration following shows KMS CMK creation and DEK generation using the AWS Command Line Interface (AWS CLI).

Note: The CMK and data key must be generated in the same AWS Region as the RDS instance so that the instance can decrypt and import the backup.

AWS KMS client-side encryption with Amazon S3
S3 supports multiple modes of encryption of customer data to include both server-side and client-side encryption. During server-side encryption (SSE), S3 encrypts customer data as it’s received using either an internal S3 key or a KMS-managed key. S3 also supports client-side encryption (CSE). CSE enables customers to encrypt their data on-premises, upload the data to S3, and still have that data securely available to other services and consumers, in this case RDS.

In general, client-side encryption works in concert with KMS and S3. An on-premises software package retrieves a data encryption key from KMS and creates a ciphertext using the data key. It then uploads the ciphertext and encrypted data key to S3 to be consumed by other services. When decrypting the data stored in S3, services and other users require permissions to access the S3 object and the KMS CMK that protects the encrypted data key.

Specifically, in terms of RDS, customers must use AES-256 with cipher block chaining (CBC) and PKCS5 padding to perform the client-side encryption. After encryption, the resulting encrypted object should be uploaded to S3 with the following metadata attached to the object:

• x-amz-key
• x-amz-iv
• x-amz-matdesc

For example, using the Python script following to encrypt a BAK file produces the following output.

You store as metadata the initialization vector (IV), the encrypted data key retrieved earlier from KMS, and the identifier for the KMS CMK used to create the data key. You store these with the encrypted RDS backup in S3.

The first metadata key, x-amz-key, contains the base 64–encoded encrypted data encryption key, named CiphertextBlob, returned by KMS. The second key, x-amz-iv, contains the base 64–encoded initialization vector that was used when encrypting the object. The third key, x-amz-matdesc, contains a JSON-formatted string that specifies the KMS CMK identifier.

When the backup is encrypted and stored in S3, you can import it into RDS. The following table shows example S3-CSE metadata.

This post uses a custom Python script to encrypt the Microsoft SQL Server backup. RDS performs and uses standard AES-256 encryption using CBC mode in concert with KMS. This approach means that you can use almost any tool to perform the encryption on-premises.

Some tools, such as OpenSSL, aescrypt, or GPG, incorporate a salt or derive the initialization vector or encryption key from a passphrase as part of their encryption. In this example, both the encryption key (CiphertextBlob) and the initialization vector must be stored with the S3 object and used as returned by KMS. Therefore, using a tool such as OpenSSL doesn’t provide the desired result. The problem is that the tool might consider the KMS-provided data encryption key to be a passphrase, from which OpenSSL should derive its own encryption key.

The example following uses Python to control the encryption process. However, you can use any encryption software that supports AES-256 CBC and that can take an encryption key, returning the initialization vector.

Additionally, your code might benefit from the Python implementation of the AWS Encryption SDK available on GitHub. For more information, see the AWS Encryption SDK documentation.

Import into Amazon RDS for SQL Server
Now you have a Microsoft SQL Server backup encrypted and uploaded to S3, and metadata populated to reflect the encryption parameters that you used. You are ready to restore the backup to an Amazon RDS for SQL Server instance. To find a detailed outline of the steps necessary to back up data out of or restore data into Amazon RDS, see this blog post on migrating SQL Server workloads to RDS.

The post outlines use of the stored procedure msdb.dbo.rds_restore_database to decrypt the S3 object and restore the database into RDS. The steps are summarized following:

1. Configure RDS option groups to grant the RDS instance access to the appropriate S3 object and KMS key.
2. Execute the RDS stored procedure to restore the database from the S3 object, specifying the associated KMS CMK.

The following shows an example.

The stored procedure creates a task to read in the S3 object and decrypt it, restoring the database to the database named in the stored procedure call.

By using the rds_task_status stored procedure, you can track the progress of the restoration. When the task is completed, the database is available for use. You can delete the S3 object if it’s no longer required.

Export from Amazon RDS for SQL Server
The same blog post discussed previously also outlines how to export an encrypted backup of your database from Amazon RDS for SQL Server to S3. You can invoke the stored procedure msdb.dbo.rds_backup_database as shown in the following.

By using this stored procedure, RDS creates an encrypted backup file in S3 using the specified KMS CMK in the same manner as you did client-side. You can then retrieve and decrypt the backup in a fashion similar to the upload and import performed earlier.

Begin by downloading the exported file from S3 and retrieving the metadata, which contains the encrypted DEK and IV needed to decrypt the backup.

With the encrypted backup on your local disk and the metadata retrieved from S3, you next decrypt the DEK by using KMS. Using the AWS CLI, you can request KMS to decrypt the DEK and return the plaintext value of the DEK. You can then use that value to decrypt the Microsoft SQL Server backup.

Last, you decrypt the backup using the sample Python script as in the following.

You can now import the resulting output into an on-premises SQL Server database system.

Following you can find a full Python example of AES-256 CBC decryption.

Summary
This blog post highlights the cryptographic procedure required to migrate a Microsoft SQL Server database to Amazon RDS for SQL Server using secure client-side encryption and KMS. You should now understand the key steps to encrypt data on premises and import it into an Amazon RDS for SQL Server instance.

About the Author

Jason Barto is a solutions architect at Amazon Web Services. He works with our customers to provide guidance and technical assistance on database projects, helping them improving the value of their solutions when using AWS.