[SQL Server] 利用透明資料加密(TDE)對整個資料庫加密

2018-03-02

8304
0
SQL Server
2018-03-06

using TDE encrypt SQL Database

前言

最近客戶有需求想避免資料庫(.mdf、.ldf)被偷走時，資料外洩流出

剛好SQL Server的TDE加密功能滿符合需求。

TDE透明資料加密會對資料庫整個加密，防範目標：有人已偷走資料庫，沒有憑證&私錀的話，在附加資料庫或還原備份資料庫時就會報錯，而沒辦法看到資料庫裡的資料。

一般程式存取資料，或用SSMS進入SQL Server裡查詢資料則是正常使用，無須額外設定

不過得留意Standard版的資料庫不支援此功能

支援TDE加密功能的資料庫為SQL Server 2008以上的Developer, Enterprise,Datacenter版。

※2018.3.6追記：發現IBM建議若是SQL Server Standard版的話，可使用Windows內建的EFS加密檔案系統來加密.mdf、.bak

在 SQL Server 資料庫上啟用透通資料加密

不過EFS加密防範目標和TDE防範目標有點不同

TDE是假設資料庫已被偷走，避免被存取資料；而EFS是阻止Windows登入使用者偷走資料庫。

採用EFS加密過的檔案，想複製偷走的話，有以下途徑：

1.取得加密檔案使用者的登入帳密登入Windows，用該身份拿走檔案。

2.使用其他身份使用者登入Windows，但要安裝加密檔案使用者的憑證。

3.使用其他身份使用者登入Windows，再用救援軟體還原檔案(嗯?還有這招)
如何存取用EFS加密的檔案？

針對資料庫如何實作EFS，已有其它網友撰寫文章：Windows 2012 R2 實作 EFS 檔案系統加密

==============================================================================================================

以下介紹如何對一個資料庫做TDE加密，並且移機到另一台機器上時，如何把資料庫附加回去。

實作

開啟資料庫加密功能實作流程，請見微軟文件說明的四大步驟(缺少一個備份憑證&私錀步驟XD)

我已經有名為「ContosoUniversity」資料庫

本文以它當範例

Step 1:

-- 在master DB 建立 master key  
USE master;  
GO  
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '1234';   --密碼隨便給，不影響本文作業
--查看建立的master key
select * from sys.symmetric_keys
Where name='##MS_DatabaseMasterKey##'

如果SQL Server中已經有前人建好的Master Key，就繼續沿用，因為再重複建立Master Key會報錯(或把前人的Master Key Drop掉?)

Step 2:

--在master DB建立SQL Server憑證
USE master;  
GO  
CREATE CERTIFICATE MySQLServerCert   
WITH SUBJECT = '用來展示TDE功能的憑證'  
--查看建立的憑證
Select * from sys.certificates
Where name='MySQLServerCert'

如果憑證名稱和前人建立的憑證名稱重複的話，就另外取個名字吧

Step 3:

--在master DB備份SQL Server憑證與私錀
USE master;  
GO  
BACKUP CERTIFICATE MySQLServerCert --要備份的憑證
TO FILE = 'MySQLServerCert.cert'   --給一個檔名
WITH PRIVATE KEY   --建立私錀
(  
    FILE = 'MySQLPrivateKeyFile.pk',  --預設備份位置為(C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA)
	--根據SQL Server版本不同存放的憑證&私錀 位置會不一樣 
    ENCRYPTION BY PASSWORD = 'demoPWD'  --這密碼很重要！移機時用得到
);  
GO

上面指令執行完畢會產生兩個檔案，請妥善保管，資料庫要附加到另一台機器上時派得上用場。

Step 4:


-- 切換到想加密的資料庫，本文為「ContosoUniversity」
USE ContosoUniversity;  
GO  
--在自己的資料庫上建立 ENCRYPTION KEY 
CREATE DATABASE ENCRYPTION KEY  
WITH ALGORITHM = AES_128  --加密演算法
ENCRYPTION BY SERVER CERTIFICATE MySQLServerCert;  --使用哪個憑證 
GO

Step 5: 最後一個步驟，啟用加密功能


USE ContosoUniversity;  
GO  
--利用TDE對整個資料庫加密
ALTER DATABASE ContosoUniversity  
SET ENCRYPTION ON;  
GO

接著示範，把資料庫(.mdf、.ldf)卸離，移到另一台機器上執行附加資料庫動作(記得憑證、私錀兩個檔案也要跟著搬移)

在另一台機器，檔案都放在以下位置

接著另一台機器也要匯入剛剛備份的憑證&金鑰，否則直接附加資料庫會報錯↓

另一台機器想要附加TDE加密過的資料庫前，得先匯入相關憑證&金鑰，SQL語法如下

Step1:

-- 在另一台 SQL Server 建立 master key .   
USE master;  
GO  
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'masterKey2PWD';  --密碼故意跟之前的master key不一樣，沒關係
Go

Step 2:

USE master;  
GO  
-- 以下是重點！
-- 利用之前備份的SQL Server憑證和私錀再次建立SQL Server certificate    

CREATE CERTIFICATE SQLServerCertAnother --這台SQL Server的憑證名字故意取不一樣，沒關係
FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS2014\MSSQL\DATA\MySQLServerCert.cert'  --剛剛備份的憑證
WITH PRIVATE KEY   
(  
    FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS2014\MSSQL\DATA\MySQLPrivateKeyFile.pk',  --剛剛備份的私錀
    DECRYPTION BY PASSWORD = 'demoPWD'  --密碼必須和備份時指定的密碼一樣
);  
GO

Step 3:

如此才能成功附加資料庫(.mdf檔)，這很簡單我懶得截圖了XD

結語

如果資料庫想關閉TDE加密功能的話，可以參考老外MSDN討論：

The certificate 'instance' cannot be dropped because it is bound to one or more database encryption key.

語法如下

--關閉TDE加密
Use ContosoUniversity
Go
--把資料庫加密功能關閉
ALTER DATABASE ContosoUniversity SET ENCRYPTION OFF
--移除資料庫的ENCRYPTION KEY              
DROP DATABASE ENCRYPTION KEY         


Use master
Go
/*  --查詢哪些資料庫使用SQL Server加密憑證
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')
*/
--移除憑證
DROP CERTIFICATE MySQLServerCert
--移除Master Key
DROP MASTER KEY

回首頁