TDE 加密的结构:
如果需要启用数据库TDE加密,步骤如下:
1.创建master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'Test@1234'; GO -- for a new installed SQL server instance, we can check from sys.key_encryptions to see whethere master key was created SELECT * FROM master.sys.key_encryptions --drop master key drop master key
2. 创建证书
USE master GO CREATE CERTIFICATE TDECertificate WITH SUBJECT = 'Certificate to protect TDE key', EXPIRY_DATE = '2080-12-30' GO SELECT * FROM sys.certificates
3。备份
use master GO BACKUP CERTIFICATE TDECertificate TO FILE = N'C:\WorkSpace\TDECertificate.cer' WITH PRIVATE KEY ( FILE = N'C:\WorkSpace\TDECertificatekey.pvk' , ENCRYPTION BY PASSWORD = N'Test@123456' ); GO -- if we need to re-create the certificate, we can delete it first DROP CERTIFICATE TDECertificate
4.Create a database encryption key and protect it by the certificate. then Set the database to use encryption.
USE DBName; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE TDECertificate; GO ALTER DATABASE Advtools SET ENCRYPTION ON --system view SELECT * FROM sys.dm_database_encryption_keys
5.恢复DB的时候
use master GO -- step 1: create master key CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'Test@123456_Restore'; go -- step 2: restore certificate CREATE CERTIFICATE TDECertificate2 FROM FILE = N'C:\WorkSpace\TDECertificate.cer' WITH PRIVATE KEY ( FILE = N'C:\WorkSpace\TDECertificatekey.pvk' , DECRYPTION BY PASSWORD = N'Test@123456' ); GO -- restore DB RESTORE DATABASE DBName FROM DISK = 'C:\WorkSpace\DBName.bak'
当修改证书的时候,我们需要重新在数据disable TDE, 删除数据库证书。然后删除master中证书和master key。
use DBName GO ALTER DATABASE DBName SET ENCRYPTION OFF DROP DATABASE ENCRYPTION KEY drop CERTIFICATE TDECertificate DROP MASTER KEY --or : ALTER MASTER KEY WITH ENCRYPTION BY PASSWORD = 'newpwd' 再重新创建证书 --查看加密的DB: SELECT db_name(database_id), encryption_state, percent_complete, key_algorithm, key_length FROM sys.dm_database_encryption_keys WHERE db_name(database_id) not in('tempdb') --当ALTER DATABASE DBName SET ENCRYPTION OFF 运行时,需要把数据库解密 --以下语句可以看到进度 select db_name(database_id),percent_complete ,* from sys.dm_database_encryption_keys