The encrypted operation of data stored in the database instance of Microsoft SQL Server via TDE is supported by RDS of JD. TDE will automatically encrypt such data before writing them into a memory and automatically decrypt such data when reading them from the memory. The database file is encrypted in the page level. The page in the encrypted database will be encrypted before being wrote into a disk, but will be decrypted when being read into a memory. TDE will not increase the size of an encrypted database.
For detailed introduction of transparent data encryption (TDE), please refer to the Microsoft document TDE
RDS of JD Cloud supports the use of TDE in the following SQL Server version:
USE master
GO
SELECT name FROM sys.certificates WHERE name LIKE 'TDE%'
GO
-- TDECertificateName refers to the TDE certificate name searched in the last step.
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM=AES_128
ENCRYPTION BY SERVER CERTIFICATE [TDECertificateName]
GO
ALTER DATABASE db1
SET ENCRYPTION ON
GO
-- TDE Verification Enabled
USE master
GO
SELECT name FROM sys.databases WHERE is_encrypted = 1
GO
SELECT db_name(database_id) as DatabaseName, * FROM sys.dm_database_encryption_keys
GO
After TDE is enabled in the Database, the backup downloaded to the local cannot be restored directly, and it only can be restored in the local SQL Server Instance in combination with the TDE certificate and Key
USE master
GO
create master key encryption by password = N'xxx'; --xxx is the Key obtained previously
GO
CREATE CERTIFICATE Mycertificate
FROM FILE = N'D:\Database\mycertificate.cer'
with private key
(
file = N'D:\Database\mycertificate.pvk' ,
decryption by password = N'xxx'
); -- mycertificate.cer and mycertificate.pvk are TDE certificates previously downloaded, and the specific file names are slightly different