TDE加密证书被删除后的处理方法

作者：[51ak ]

在SQL2008以后的TDE加密中，用到的一个加密证书

此加密证书跟MASTERKEY有关，当MATERKEY被删除和重置以后，需要进行以下处理

SELECT name,is_master_key_encrypted_by_server FROM sys.databases;

SELECT * FROM sys.symmetric_keys;

CREATE CERTIFICATE db_sql_error_cert WITH SUBJECT = N'db_sql_error_cert';

use Db_sql_error

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE db_sql_error_cert;

drop CERTIFICATE db_sql_error_cert

drop MASTER KEY

drop CERTIFICATE HOST_B_cert

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Wokofo2';--此处的密码是生成密钥的字典,可以跟A机的不一样

CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate',

START_DATE = '06/01/2010',

EXPIRY_DATE= '01/01/2099';

RESTORE MASTER KEY

FROM FILE = 'E:\master.cer'

DECRYPTION BY PASSWORD = 'Wokofo2'

ENCRYPTION BY PASSWORD = 'Wokofo2';

BACKUP CERTIFICATE db_sql_error_cert TO FILE = 'D:\db_sql_error_cert.cer'

WITH PRIVATE KEY (

FILE = 'D:\db_sql_error_cert.pvk' ,

DECRYPTION BY PASSWORD = 'Wokofo2' ,

ENCRYPTION BY PASSWORD = 'Wokofo2' );

drop CERTIFICATE db_sql_error_cert

CREATE CERTIFICATE db_sql_error_cert

FROM FILE = 'D:\254\db_sql_error_cert.cer'

WITH PRIVATE KEY (

FILE = 'D:\254\db_sql_error_cert.pvk'

, DECRYPTION BY PASSWORD = 'Wokofo2'

)

BACKUP MASTER KEY TO FILE = 'D:\master.cer'

ENCRYPTION BY PASSWORD = 'Wokofo2';

ALTER DATABASE db_sql_error SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE db_sql_error SET ENCRYPTION off;

ALTER DATABASE db_sql_error SET MULTI_USER WITH ROLLBACK IMMEDIATE;