ApsaraDB RDS:Best practices for TDE

How TDE works

TDE protects data at the storage layer without requiring changes to application code or connection logic. Authenticated applications and users read plaintext data as usual; encryption and decryption happen transparently in the SQL Server I/O path.

TDE uses a layered key hierarchy. Each layer encrypts the layer below it:

OS layer
└── Data Protection API (DPAPI)          — Windows built-in API; root of the hierarchy
    └── Service Master Key (SMK)         — root key for the SQL Server engine; generated at instance creation
        └── Database Master Key (DMK)    — symmetric key stored in the master database
            └── Certificate              — stored in the master database; created from the DMK
                └── Database Encryption Key (DEK)  — stored in the user database; encrypts data files

The master database stores the DMK and the certificate. User databases store the DEK, encrypted by the certificate. When SQL Server reads or writes data, it uses the certificate's private key to decrypt the DEK in memory, then uses the DEK to encrypt or decrypt data files in real time.

If the certificate is missing from the master database, the DEK cannot be decrypted. Backup files of the affected database become inaccessible.

Performance considerations

TDE reduces overall database performance by approximately 3%–5% under typical workloads, based on official Microsoft benchmarks. Under high CPU utilization, the impact can reach approximately 28%.

Prerequisites

Before enabling TDE, make sure the following conditions are met:

• The RDS instance belongs to the general-purpose or dedicated instance family. Shared instances are not supported. See Instance families.

• The billing method is subscription or pay-as-you-go. Serverless ApsaraDB RDS instances are not supported. See Serverless ApsaraDB RDS for SQL Server instances.

• The instance runs one of the following SQL Server versions: SQL Server 2019 SE, SQL Server 2022 SE, or SQL Server EE.

• Your Alibaba Cloud account has authorized the RDS instance to access Key Management Service (KMS). See Authorize ApsaraDB RDS to access KMS.

• (BYOK only) Obtain the certificate file, private key file, and password before you begin.

Read-only instances do not support TDE.

Limitations

Enable or disable TDE

Enable TDE

1. Go to the Instances page. In the top navigation bar, select the region where the RDS instance resides. Find the instance and click its ID.

2. In the left-side navigation pane, click Data Security.

3. On the TDE tab, turn on the switch next to Disabled.

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

Use an Alibaba Cloud-managed key

Select databases from the Unselected Databases section, click the icon to move them to the Selected Databases section, and then click Confirm.

Use your own SQL Server key (BYOK)

1. Upload the certificate file and private key file to an OSS bucket. See Upload objects.

   

2. Click Next step and configure the following parameters.

   Parameter	Description
   OSS Bucket	The OSS bucket where the certificate and private key files are stored.
   Certificate	The certificate file uploaded to the OSS bucket.
   Private key	The private key file uploaded to the OSS bucket.
   Password	The password of your SQL Server key.

   

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

   

Disable TDE

Disabling TDE decrypts all data in the selected databases. Decryption is resource-intensive — it consumes significant CPU, memory, and I/O — and the time required is proportional to data volume. For a database with approximately 200 GB of data, disabling TDE can take more than 40 minutes.

To disable TDE for one or more databases, remove those databases from the Selected Databases section. To disable TDE for the entire RDS instance, remove all databases from the Selected Databases section.

1. Go to the Instances page. In the top navigation bar, select the region where the RDS instance resides. Find the instance and click its ID.

2. In the left-side navigation pane, click Data Security.

3. On the TDE tab, click TDE Settings.

4. Select databases from the Selected Databases section, click the icon to move them to the Unselected Databases section, and then click OK.

   

Monitor decryption progress

After disabling TDE, run the following query to check decryption progress:

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

Restore to on-premises: Alibaba Cloud key

Use this procedure if TDE was enabled with an Alibaba Cloud-managed service key. Because the service key is managed by Alibaba Cloud and is not exportable, encrypted backup files cannot be used directly. The workaround is to disable TDE first, then perform a fresh full backup.

Restore to on-premises: BYOK

If TDE was enabled with your own SQL Server key (BYOK), you can restore the encrypted backup to an on-premises SQL Server using your own certificate and private key.

You can also use the Alibaba Cloud key procedure instead, using the disable-then-backup approach.

Connect to your RDS instance using a SQL Server client before running the following statements. See Connect to an ApsaraDB RDS for SQL Server instance.

USE master;
GO
IF NOT EXISTS (
    SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101
)
BEGIN
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<your-mek-password>'; -- Replace with your custom password.
END;

USE master;
GO
CREATE CERTIFICATE TDE_Certificate
FROM FILE = 'C:\cert\tde_cert.cer'         -- Replace with the actual path to your certificate file.
WITH PRIVATE KEY (
    FILE = 'C:\cert\tde_privatekey.pvk',   -- Replace with the actual path to your private key file.
    DECRYPTION BY PASSWORD = '<your-tde-password>'  -- Replace with the password used when enabling TDE.
);

What's next

For other data encryption options available in ApsaraDB RDS for SQL Server, see Data security and encryption.