Best practices for TDE of ApsaraDB RDS for SQL Server - ApsaraDB RDS

ApsaraDB RDS for SQL Server supports Transparent Data Encryption (TDE) to enhance data security. TDE allows you to use the keys that are automatically generated by Alibaba Cloud or Bring Your Own Keys (BYOKs) for data encryption. If you back up your ApsaraDB RDS for SQL Server instance after you enable TDE, the backup files are automatically encrypted and cannot be directly used to restore data. This topic describes how to enable TDE for your RDS instance and how to restore the data of an RDS instance for which TDE is enabled to your on-premises device in different encryption scenarios. If you enable TDE and use a BYOK for data encryption, you can use your own certificate and password to restore data based on the method for Scenario 2.

TDE encryption

TDE performs data-at-rest encryption on a database. This prevents attackers from bypassing the database to read sensitive information from storage. TDE allows authenticated applications and users to access plaintext application data without the need to modify the application code or configurations for decryption. However, TDE prevents operating system (OS) users who attempt to read sensitive information within tablespaces and unauthorized users who attempt to read backup data and on-disk data from accessing the plaintext data. For more information about how to enable TDE, see Enable TDE.

This section describes how TDE works at different levels in a computer system.

Operating system layer

Microsoft Data Protection API (DPAPI) is a built-in API in a Windows operating system and is used to encrypt or decrypt data. DPAPI frees you from focusing on encryption keys and algorithms.

DPAPI is the root of the TDE hierarchy. DPAPI protects the key hierarchy at the operating system layer and the Service Master Key (SMK) of a database instance.

SQL Server instance layer

In SQL Server, an SMK is an advanced encryption key used by the database engine. An SMK is a root key for the SQL Server engine and is generated when an SQL Server instance is created. An SMK is used to encrypt low-level keys and data.

An SMK is protected by DPAPI and is used to encrypt the Database Master Key (DMK).

Database layer

TDE is performed on the Master database and User database at the database layer.

Master database

After you enable TDE for your RDS instance, a DMK is created in the Master database and is used to create a certificate. The DMK and certificate are stored in the Master database.

A DMK is a symmetric key that is used to encrypt sensitive information in a database. A DMK is encrypted by an SMK and is used to encrypt certificates. The certificate in the Master database is used to encrypt the Database Encryption Key (DEK) in the User database.

User database

A DEK is an encryption key that is created and stored in the User database and only works together with TDE.

The DEK is encrypted by the certificate in the Master database. Data in the User database in which the DEK is stored is encrypted by the DEK. The DEK is encrypted by a certificate and stored in the User database. If the DEK is not encrypted, the DEK is stored in the memory. When data is written to or read from a database, SQL Server uses the private key of the certificate to decrypt the encrypted DEK and loads the decrypted DEK into the memory to encrypt or decrypt data files in real time. This process is transparent to applications and users.

If the certificate for TDE is not generated for the Master database, the DEK cannot be decrypted. As a result, you cannot restore backup files of a database for which TDE is enabled or add the database to a database instance.

Enable or disable TDE

Enable TDE

Prerequisites

The RDS instance runs one of the following database engine versions:
- SQL Server 2019 SE, SQL Server 2022 SE, and SQL Server EE
- The RDS instance belongs to the general-purpose or dedicated instance family. The shared instance family is not supported.
Note
- Read-only RDS instances do not support this feature.
- If you want to use Bring Your Own Keys (BYOKs), obtain the certificate, private key, and password that are used for encryption and decryption in advance.
Your Alibaba Cloud account is used to authorize the RDS instance to access Key Management Service (KMS). For more information, see Authorize an ApsaraDB RDS for MySQL instance to access KMS.

Usage notes

If you use a service key that is provided by Alibaba Cloud for an RDS instance and enable TDE for the RDS instance, you cannot use the backup files generated after the TDE feature is enabled to restore the data of the RDS instance to an on-premises device.
After TDE is enabled, the performance of your database is affected.
- Impact on performance: The overall performance is reduced by approximately 3% to 5% based on official Microsoft documentation.
- Impact on in-memory data: If most of the accessed data is stored in memory, the performance is slightly affected.
- Impact on CPU utilization and I/O performance: Performing TDE is a CPU-intensive process and involves I/O operations.
  If the I/O load on a server or an application is low and the CPU utilization is low, the performance is slightly affected. If the CPU utilization on an application is high, a performance loss of approximately 28% is caused.
  If the I/O load on a server or an application is high but the CPU utilization is low enough, the performance is slightly affected.

Limits

If TDE is enabled for your RDS instance, you cannot upgrade the major engine version, upgrade the RDS instance with local disks from SQL Server 2008 R2 to SQL Server 2012 or SQL Server 2016, update the minor engine version, or migrate the RDS instance across zones. For more information, see Upgrade the major engine version, Upgrade the RDS instance with local disks from SQL Server 2008 R2 to SQL Server 2012 or SQL Server 2016, Update the minor engine version, and Migrate an ApsaraDB RDS for SQL Server instance across zones.
If TDE is enabled for your RDS instance, you cannot use the backup files of the RDS instance to rebuild the RDS instance. For more information, see Rebuild an RDS instance and

Procedure

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the left-side navigation pane, click Data Security.
On the TDE tab, turn on the switch next to Disabled.
Note
You can enable TDE only when your RDS instance meets all of the conditions specified in the "Prerequisites" section.

In the dialog box that appears, select a key type and click OK.

Use the key automatically generated by Alibaba Cloud

Select databases from the Unselected Databases section, click the icon to move the selected databases to the Selected Databases section, and then click Confirm. TDE设置

Encrypt with own SQL Server key

Upload the certificate file and the private key file to your OSS bucket. For more information, see Upload objects.

Click Next step and configure the following parameters. 选择自带密钥

Parameter	Description
OSS Bucket	The OSS bucket in which the certificate file and the private key file are stored.
Certificate	The certificate file that you uploaded to the OSS bucket.
Private key	The private key file that you uploaded to the OSS bucket.
Password	The password of your own SQL Server key.

Click Next step to go to the Authorization database step.
Select databases from the Unselected Databases section, click the icon to move the selected databases to the Selected Databases section, and then click Confirm.

Disable TDE

Note

If you want to disable TDE for one or more databases, you can remove the databases from the Selected Databases section.
If you want to disable TDE for an RDS instance, you must remove all databases on the RDS instance from the Selected Databases section. Then, the system disables TDE for the RDS instance.

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the left-side navigation pane, click Data Security.
Click the TDE tab. Then, click TDE Settings.
Select databases from the Selected Databases section, click the icon to move the selected databases to the Unselected Databases section, and then click OK.

Note

When you disable TDE, multiple database operations, such as waiting for the last backup to complete, decrypting data, recording logs, and updating metadata, are involved. Data decryption is a resource-intensive operation and consumes a large number of CPU, memory, and I/O resources. The time consumed by data decryption is proportional to the data volume of the database. If you want to disable TDE for a database that contains a large amount of data, a long period of time is required. For example, if the database contains approximately 200 GB of data, it may require more than 40 minutes to disable TDE.

If you want to view the progress of data decryption after TDE is disabled, execute the following SQL statement:

SELECT 
    db_name(database_id) AS DatabaseName, 
    encryption_state, 
    percent_complete 
FROM 
    sys.dm_database_encryption_keys;

Scenario 1: Using the backup files generated after you enable TDE for which a key that is automatically generated by Alibaba Cloud is used to restore data of your RDS instance to your on-premises device

Step 1: Disable TDE

On the Data Security page of your RDS instance, remove the databases that you want to back up from the Protected Databases section.

Note

If you want to disable TDE for one or more databases, you can remove the databases from the Selected Databases section.
If you want to disable TDE for an RDS instance, you must remove all databases on the RDS instance from the Selected Databases section. Then, the system disables TDE for the RDS instance.

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the left-side navigation pane, click Data Security.
Click the TDE tab. Then, click TDE Settings.
Select databases from the Selected Databases section, click the icon to move the selected databases to the Unselected Databases section, and then click OK.

Note

If you want to view the progress of data decryption after TDE is disabled, execute the following SQL statement:

SELECT 
    db_name(database_id) AS DatabaseName, 
    encryption_state, 
    percent_complete 
FROM 
    sys.dm_database_encryption_keys;

Step 2: Perform a full backup

After you disable TDE, some encrypted transaction logs exist. If you download the backup file, the backup file is encrypted and cannot be used to restore data. After you disable TDE, you must perform a full backup. For more information, see Database Encryption in SQL Server 2008 Enterprise Edition.

You can manually perform a full backup. For more information, see Configure manual backups.

Note

If you manually create a backup, you can back up data of the entire RDS instance or back up specific databases on the RDS instance based on your business requirements. You can back up specific databases only when you use the physical backup method.

Step 3: Download the backup file and use the backup file to restore data to your on-premises device

On the Backup and Restoration page of the RDS instance, download the most recent full backup file to your on-premises device. Then, use the .bak file in the downloaded package to restore data. For more information, see Download data backup files and log backup files.

Scenario 2: Using the backup files generated after you enable TDE for which a BYOK is used to restore data of your RDS instance to your on-premises device

You can disable TDE for the required databases based on the method for Scenario 1. Then, perform a full backup and download the full backup file to your on-premises device. This section describes another data restoration method. You can use the method based on your business requirements.

Step 1: Create a MEK

Use a custom password to create a master encryption key (MEK) in the server on which the master database on your RDS instance is created. The custom password can be different from the password that is used to create the MEK in the server on which the self-managed SQL Server database is created.

Note

Before you start, you must use a client to connect to the RDS instance. For more information, see Connect to an ApsaraDB RDS for SQL Server instance.

Execute the following SQL statement to check whether an MEK is created in the server on which your RDS instance resides. If an MEK exists, go to Step 2. If no MEKs exist, an MEK is created.

USE master; 
GO 
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
BEGIN
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourPassword'; --Specify the custom password.
END;

Step 2: Create a certificate

Use the certificate and private key that are provided when you enable TDE to create a certificate and apply the certificate to the server on which your RDS instance resides.

USE MASTER;
GO
CREATE CERTIFICATE TDE_Certificate
FROM FILE = 'C:\cert\tde_cert.cer' -- Change the value to the actual path to the created certificate.
WITH PRIVATE KEY (FILE = 'C:\cert\tde_privatekey.pvk', -- Change the value to the actual path to the private key.
DECRYPTION BY PASSWORD='YourTDEPassword ' ); -- Change the value to the actual password.

Step 3: Download the backup file and use the backup file to restore data to your on-premises device

On the Backup and Restoration page of the RDS instance, find the required backup set, download the backup set to your on-premises device, and then use the .bak file in the downloaded package to restore data. For more information, see Download data backup files and log backup files.

References

For more information about other data encryption methods supported by ApsaraDB RDS for SQL Server, see Data security and encryption.