SQL Server Transparent Data Encryption

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 

 

　　

 

转载于:https://www.cnblogs.com/sky1225/p/9626054.html