Transparent data encryption Aurora MySQL - SQL Server to Aurora MySQL Migration Playbook
SQL Server UsageMySQL Usage

Transparent data encryption Aurora MySQL

This topic provides reference information about data encryption capabilities in Microsoft SQL Server and Amazon Aurora MySQL. You can understand how Transparent Data Encryption (TDE) works in SQL Server to protect data at rest without requiring application changes.

Feature compatibility AWS SCT / AWS DMS automation level AWS SCT action code index Key differences

Four star feature compatibility

N/A

N/A

Enable encryption when creating the database instance.

SQL Server Usage

Transparent data encryption (TDE) is an SQL Server feature designed to protect data at-rest in the event an attacker obtains the physical media containing database files.

TDE doesn’t require application changes and is completely transparent to users. The storage engine encrypts and decrypts data on-the-fly. Data isn’t encrypted while in memory or on the network. TDE can be turned on or off individually for each database.

TDE encryption uses a Database Encryption Key (DEK) stored in the database boot record, making it available during database recovery. The DEK is a symmetric key signed with a server certificate from the primary system database.

In many instances, security compliance laws require TDE for data at rest.

Examples

The following example demonstrates how to enable TDE for a database.

Create a master key and certificate.

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyPassword';
CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate';

Create a database encryption key.

USE MyDatabase;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDECert;

Enable TDE.

ALTER DATABASE MyDatabase SET ENCRYPTION ON;

For more information, see Transparent data encryption (TDE) in the SQL Server documentation.

MySQL Usage

Amazon Aurora MySQL-Compatible Edition (Aurora MySQL) provides the ability to encrypt data at rest (data stored in persistent storage) for new database instances. When data encryption is enabled, Amazon Relational Database Service (RDS) automatically encrypts the database server storage, automated backups, read replicas, and snapshots using the AES-256 encryption algorithm.

You can manage the keys used for Amazon Relational Database Service (Amazon RDS) encrypted instances from the Identity and Access Management (IAM) console using the AWS Key Management Service (AWS KMS). If you require full control of a key, you must manage it yourself. You can’t delete, revoke, or rotate default keys provisioned by AWS KMS.

The following limitations exist for Amazon RDS encrypted instances:

  • You can only enable encryption for an Amazon RDS database instance when you create it, not afterward. It is possible to encrypt an existing database by creating a snapshot of the database instance and then creating an encrypted copy of the snapshot. You can restore the database from the encrypted snapshot. For more information, see Copying a snapshot.

  • Encrypted database instances can’t be modified to turn off encryption.

  • Encrypted Read Replicas must be encrypted with the same key as the source database instance.

  • An unencrypted backup or snapshot can’t be restored to an encrypted database instance.

  • KMS encryption keys are specific to the region where they are created. Copying an encrypted snapshot from one region to another requires the KMS key identifier of the destination region.

Note

Disabling the key for an encrypted database instance prevents reading from, or writing to, that instance. When Amazon RDS encounters a database instance encrypted by a key to which Amazon RDS doesn’t have access, it puts the database instance into a terminal state. In this state, the database instance is no longer available and the current state of the database can’t be recovered. To restore the database instance, you must re-enable access to the encryption key for Amazon RDS and then restore the database instance from a backup.

Table encryption can now be managed globally by defining and enforcing encryption defaults. The default_table_encryption variable defines an encryption default for newly created schemas and general tablespace. The encryption default for a schema can also be defined using the DEFAULT ENCRYPTION clause when creating a schema. By default a table inherits the encryption of the schema or general tablespace it is created in. Encryption defaults are enforced by enabling the table_encryption_privilege_check variable. The privilege check occurs when creating or altering a schema or general tablespace with an encryption setting that differs from the default_table_encryption setting or when creating or altering a table with an encryption setting that differs from the default schema encryption. The TABLE_ENCRYPTION_ADMIN privilege permits overriding default encryption settings when table_encryption_privilege_check is enabled. For more information, see Defining an Encryption Default for Schemas and General Tablespaces.

Creating an Encryption Key

To create your own key, browse to the Key Management Service (KMS) and choose Customer managed keys and create a new key.

  1. Choose relevant options and choose Next.

  2. Define alias as the name of the key and choose Next.

  3. You can skip Define Key Administrative Permissions and choose Next.

  4. On the next step make sure to assign the key to the relevant users who will need to interact with Amazon Aurora.

  5. On the last step you will be able to see the ARN of the key and its account.

  6. Choose Finish and now this key will be listed in under customer managed keys.

Now you will be able to set Master encryption key by using the ARN of the key that you have created or picking it from the list.

Proceed to finish and launch the instance.

As part of the database settings, you will be prompted to enable encryption and select a master key.

Encryption for an Amazon RDS DB instance can be enabled only during the instance creation.

You can select the default key provided for the account or define a specific key based on an IAM KMS ARN from your account or a different account.