SQL SERVER TDE加密建立與還原

第一個資料庫

建立加密

USE master; 

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password123'; 

GO

CREATE CERTIFICATE MyTDECert 

WITH SUBJECT = 'TDECertificate'

GO

匯出(備份)加密

--備份Master Key 

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Password123';

BACKUP MASTER KEY TO FILE = N'c:\expmasterkey' 

    ENCRYPTION BY PASSWORD = 'Password123';

GO

--備份憑證

BACKUP CERTIFICATE MyTDECert 

  TO FILE = N'c:\MyTDECert.cer'

        WITH PRIVATE KEY (

        FILE = 'c:\MyTDECert.pvk',

        ENCRYPTION BY 

        PASSWORD = 'Password123');

建立加密資料庫

USE master;

CREATE DATABASE DemoTDE

GO

USE DemoTDE;

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE MyTDECert

GO

啟用關閉加密

ALTER DATABASE DemoTDE

SET ENCRYPTION ON

GO

SELECT [name], is_encrypted FROM sys.databases

GO

還原加密(或第二台資料庫)

USE master  

GO  

CREATE MASTER KEY ENCRYPTION 

BY PASSWORD = 'Password123';  

GO

USE master;

GO

CREATE CERTIFICATE MyTDECert 

  FROM FILE = N'C:\MyTDECert.cer'

  WITH PRIVATE KEY ( 

    FILE =  N'C:\MyTDECert.pvk',

 DECRYPTION BY PASSWORD = 'Password123'

  );

GO

footer See also :