SQLServer的TDE加密

標籤：

TDE的主要作用是防止Database Backup或資料檔案被偷了以後，偷Database Backup或檔案的人在沒有資料加密金鑰的情況下是無法恢複或附加資料庫的。

USE [master];

GO



--查看master資料庫是否被加密

SELECT name,is_master_key_encrypted_by_server FROM
sys.databases;



--建立master資料庫下的主要資料庫密鑰

CREATE MASTER KEY ENCRYPTION BY PASSWORD = N‘^&*()0A‘;



--查看master資料庫下的密鑰資訊

SELECT * FROM sys.symmetric_keys;



--建立認證用來保護 資料庫加密金鑰 (DEK)

CREATE CERTIFICATE master_server_cert WITH
SUBJECT = N‘Master Protect DEK Certificate‘;



IF DB_ID(‘db_encryption_test‘) IS NOT NULL

DROP DATABASE db_encryption_test



--建立測試資料庫

CREATE DATABASE db_encryption_test;

GO



USE db_encryption_test;



--建立由master_server_cert保護的DEK 資料庫加密金鑰 （對稱金鑰）

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE master_server_cert;

GO

USE master;
BACKUP CERTIFICATE master_server_cert TO FILE = ‘D:\MSSQL\Certificate\master_server_cert.cer‘
WITH PRIVATE KEY (
FILE = ‘D:\MSSQL\Certificate\master_server_cert.pvk‘ ,
ENCRYPTION BY PASSWORD = ‘^&*()0A‘;

--相應的，我們也備份一下資料庫主要金鑰(master)
USE master;
--如果沒有啟用主要金鑰的自動解密功能
--OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘^&*()0A‘;
BACKUP MASTER KEY TO FILE = ‘D:\MSSQL\MasterKey\master.cer‘
ENCRYPTION BY PASSWORD = ‘^&*()0A‘;
GO

--生產環境下，設定成單使用者在運行加密
ALTER DATABASE db_encryption_test SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

--備份成功以後，開啟TDE 加密
ALTER DATABASE db_encryption_test SET ENCRYPTION ON;
GO

--設定多使用者訪問
ALTER DATABASE db_encryption_test SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO

--查看db_encryption_test資料庫是否被加密 encryption_state:3 TDE加密了
SELECT DB_NAME(database_id),encryption_state FROM sys.dm_database_encryption_keys;
/*
發現tempdb也被加密了。MSDN解釋是：如果執行個體中有一個資料庫啟用了TDE加密，那麼tempdb也被加密
*/

--接下來,找另外一台機器或者執行個體來測試,如果資料檔案被盜走了,防止附加的測試.
USE master;
EXEC sp_detach_db N‘db_encryption_test‘;
GO


USE master;
--我先在他機器還原了MASTER KEY (他原機器master庫無master key)
RESTORE MASTER KEY
FROM FILE = ‘C:\Users\Administrator\Desktop\master.cer‘
DECRYPTION BY PASSWORD = ‘^&*()0A‘
ENCRYPTION BY PASSWORD = ‘^&*()0A‘;
GO

--如果沒有自動加密
OPEN MASTER KEY DECRYPTION BY PASSWORD=N‘^&*()0A‘;
--建立認證
CREATE CERTIFICATE master_server_cert
FROM FILE = ‘C:\Users\Administrator\Desktop\master_server_cert.cer‘
WITH PRIVATE KEY (FILE = ‘C:\Users\Administrator\Desktop\master_server_cert.pvk‘,
DECRYPTION BY PASSWORD = ‘^&*()0A‘;
GO
--附加資料庫
CREATE DATABASE db_encryption_test
ON PRIMARY
(
FILENAME=N‘C:\Users\Administrator\Desktop\db_encryption_test.mdf‘
)
LOG ON
(
FILENAME=N‘C:\Users\Administrator\Desktop\db_encryption_test_log.ldf‘
)
FOR ATTACH ;
GO

--測試成功

--關閉資料庫聯結
CLOSE MASTER KEY

SQLServer的TDE加密