SQL Server 加密案例解析

pursuer.chen發表於2018-01-22
SQLServer加密

一、概述

加密是一種安全措施，有時候甚至是法律要求。作為攻破Windows系統的最後一道防線，通過加密可以保證在沒有金鑰的情況下獲取備份或者物理介質變得毫無意義。

 

二、概念

加密層次結構

加密層次結構的每一層是如何對它下面的一層進行加密的，並且顯示了最常用的加密配置。對層次結構的開始進行的訪問通常受密碼保護。SQL Server 用分層加密和金鑰管理基礎結構來加密資料。每一層都使用證照、非對稱金鑰和對稱金鑰的組合對它下面的一層進行加密。非對稱金鑰和對稱金鑰可以儲存在 SQL Server 之外的可擴充套件金鑰管理 (EKM) 模組中。

注意：

  • 為了獲得最佳效能，使用對稱金鑰（而不是證照或非對稱金鑰）加密資料。

  • 資料庫主金鑰受服務主金鑰保護。 服務主金鑰由 SQL Server 安裝程式建立，並且使用 Windows 資料保護 API (DPAPI) 進行加密。

  • 堆疊其他層的其他加密層次結構是可能的。

  • 可擴充套件金鑰管理 (EKM) 模組將對稱金鑰或非對稱金鑰儲存在 SQL Server 的外部。

  • 透明資料加密 (TDE) 必須使用稱為資料庫加密金鑰的對稱金鑰，該金鑰受由 master 資料庫的資料庫主金鑰保護的證照保護，或者受儲存在 EKM 中的非對稱金鑰保護。

  • 服務主金鑰和所有資料庫主金鑰是對稱金鑰。

1.服務主金鑰(Service Master Key)

每一個例項只有一個服務主金鑰，服務主金鑰用於加密資料庫主金鑰，服務主金鑰為 SQL Server 加密層次結構的根。服務主金鑰是首次需要它來加密其他金鑰時自動生成的。預設情況下，服務主金鑰使用 Windows 資料保護 API 和本地計算機金鑰進行加密。只有建立服務主金鑰的 Windows 服務帳戶或有權訪問服務帳戶名稱和密碼的主體能夠開啟服務主金鑰。

---備份服務主金鑰
BACKUP SERVICE MASTER KEY TO FILE = 'D:\DECRYPTION\ServerMasterKey' 
    ENCRYPTION BY PASSWORD = 'password'

----還原服務主金鑰
RESTORE SERVICE MASTER KEY FROM FILE = 'D:\DECRYPTION\ServerMasterKey'
    DECRYPTION BY PASSWORD = 'password' 
    [FORCE];   ----即使存在資料丟失的風險，也要強制替換服務主金鑰。

注意：
1.服務主金鑰直接或間接地保護樹中的所有其他金鑰。如果在強制的還原過程中不能對某個相關金鑰進行解密，則由該金鑰所保護的資料便會丟失。
2.重新生成加密層次結構是一種消耗大量資源的操作。您應當將該操作安排在資源需求較低的時段進行。
3.當還原服務主金鑰時，SQL Server 將對所有已使用當前服務主金鑰加密的金鑰和機密內容進行解密，然後使用從備份檔案中載入的服務主金鑰對這些金鑰和機密內容進行加密。

2.資料庫主金鑰

資料庫主金鑰建立於對應資料庫下，具體的保護物件可以參考下面的資料庫範圍的安全物件。如果要對資料庫備份或者透明資料庫加密那麼需要將服務主金鑰建立於Master資料庫下。

----1.建立資料庫主金鑰
USE [master]
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKey'
---2.刪除資料庫主金鑰
DROP MASTER KEY

---3.備份資料庫主金鑰
/*
主金鑰必須為開啟狀態，因此在備份主金鑰之前應對其進行解密。如果主金鑰使用服務主金鑰進行加密，則不必顯式開啟。但如果主金鑰僅使用密碼進行加密，則必須顯式開啟。
建議在建立主金鑰之後立即對其進行備份，並儲存於另外一個安全的位置中。
*/

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'MasterKey'; ---開啟資料庫主金鑰,這裡的密碼為建立主金鑰時設的密碼
BACKUP MASTER KEY TO FILE = 'D:\DECRYPTION\MasterKey' -----主金鑰私鑰檔案
    ENCRYPTION BY PASSWORD = 'MasterKey';             -----加密主金鑰私鑰檔案
GO 
---4.還原資料庫主金鑰
/*
還原主金鑰之後，SQL Server 會對使用當前活動的主金鑰加密的所有金鑰進行解密，然後使用還原後的主金鑰對這些金鑰進行加密。這種大量消耗資源的操作應當安排在資源需求較低的時段執行。如果當前的資料庫主金鑰未開啟或無法開啟，或者無法對任何使用該主金鑰加密的金鑰進行解密，則還原操作將失敗。
如果當前資料庫中沒有主金鑰，則 RESTORE MASTER KEY 將建立一個主金鑰。新的主金鑰不會自動使用服務主金鑰進行加密。
請僅在主金鑰無法恢復或解密失敗時，才使用 FORCE 選項。僅由不可恢復金鑰加密的資訊將會丟失。
如果主金鑰通過服務主金鑰進行加密，則還原後的主金鑰也通過該服務主金鑰進行加密(當前伺服器)。
*/
RESTORE MASTER KEY  
FROM FILE = 'C:\DECRYPTION\MasterKey' 
DECRYPTION BY PASSWORD = N'MasterKey'  
ENCRYPTION BY PASSWORD = 'MasterKey123'  ---加密匯出的主金鑰
--force;        ----指定即使當前資料庫主金鑰未開啟，或者 SQL Server 無法對使用該主金鑰加密的某些私鑰進行解密，RESTORE 過程也應繼續執行。
GO 

---5.開啟資料庫主金鑰
OPEN MASTER KEY DECRYPTION BY PASSWORD = N'MasterKey123'  

GO

注意：

1.資料庫主金鑰是指用於保護證照私鑰的對稱金鑰以及資料庫中存在的非對稱金鑰。當建立主金鑰時，會使用 Triple DES 演算法以及使用者提供的密碼對其進行加密。
2.請使用服務主金鑰對該主金鑰的副本進行加密，並將副本儲存在資料庫和 master 中。通常，每當主金鑰更改時，便會在不進行提示的情況下更新儲存在 master 中的副本。
3.在當前伺服器下建立的資料庫主金鑰預設就使用了服務主金鑰加密和自動解密，不必使用 OPEN MASTER KEY 語句。如果還原到了新的伺服器那麼服務主金鑰則不存在.必須使用 OPEN MASTER KEY 語句解密資料庫主金鑰。一旦資料庫主金鑰解密後，通過使用 ALTER MASTER KEY 語句向伺服器提供資料庫主金鑰（使用服務主金鑰加密）的副本，即可擁有將來啟用自動解密的選項。
4.通過使用帶 DROP ENCRYPTION BY SERVICE MASTER KEY 選項的 ALTER MASTER KEY 語句，可從自動金鑰管理中排除特定資料庫的資料庫主金鑰。然後，必須顯式開啟帶密碼的資料庫主金鑰。
5.資料庫主金鑰使用公鑰對證照、非對稱金鑰進行加密，使用私鑰進行解密，如果在當前建立的伺服器上預設自動解密，如果還原到一臺新的服務上時可能需要使用OPEN MASTER KEY進行解密。

3.證照

 證照使用公鑰對安全物件進行加密,使用私鑰進行解密,預設證照存在就自動解密。

----1.建立自我簽名的證照,使用資料庫主金鑰進行加密證照
USE MASTER;
GO
CREATE CERTIFICATE MyCerts 
   WITH SUBJECT = 'BackDB Records', 
   EXPIRY_DATE = '10/31/2099';            ----證照過期時間，不指定開始時間預設開始時間為當前時間
GO

---使用密碼進行加密證照
USE MASTER;
GO
CREATE CERTIFICATE CertsByPW 
   ENCRYPTION BY PASSWORD = 'CertsByPW111'
   WITH SUBJECT = 'BackDB Records', 
   EXPIRY_DATE = '10/31/2099';            ----證照過期時間，不指定開始時間預設開始時間為當前時間
GO

----2.備份證照
----警告: 用於對資料庫加密金鑰進行加密的證照尚未備份。應當立即備份該證照以及與該證照關聯的私鑰。如果該證照不可用，或者您必須在另一臺伺服器上還原或附加資料庫，則必須對該證照和私鑰均進行備份，否則將無法開啟該資料庫。
BACKUP CERTIFICATE MyCerts TO FILE = 'D:\DECRYPTION\MyCerts' ----證照檔案
    WITH PRIVATE KEY ( FILE = 'D:\DECRYPTION\MyCertsKey' ,   ----證照私鑰檔案
                       ENCRYPTION BY PASSWORD = 'MyCerts123' ); ----對私鑰檔案加密
GO

---備份使用私鑰進行加密的證照,必須先對私鑰進行解密
BACKUP CERTIFICATE CertsByPW TO FILE = 'D:\DECRYPTION\MyCerts' ----證照檔案
    WITH PRIVATE KEY ( DECRYPTION BY PASSWORD = 'CertsByPW111',----解密證照
                       FILE = 'D:\DECRYPTION\MyCertsKey' ,   ----證照私鑰檔案
                       ENCRYPTION BY PASSWORD = 'MyCerts123' ); ----對私鑰檔案加密
GO

----3.通過備份檔案建立證照,還原證照,
CREATE CERTIFICATE MyCerts FROM FILE = 'C:\DECRYPTION\MyCerts' ----證照檔案
    WITH PRIVATE KEY ( FILE = 'C:\DECRYPTION\MyCertsKey' ,    ----證照私鑰檔案
    DECRYPTION BY PASSWORD = 'MyCerts123' );                  ----解密私鑰檔案


---4.刪除證照
DROP CERTIFICATE MyCerts

注意：

當使用資料庫主金鑰對私鑰進行加密時，不需要 ENCRYPTION BY PASSWORD 選項。

只有在使用密碼對私鑰進行加密時，才使用該選項。

如果未指定密碼，則使用資料庫主金鑰對證照的私鑰進行加密。 如果資料庫主金鑰無法開啟，則省略該子句會導致錯誤。

4.非對稱金鑰

“非對稱金鑰”是資料庫級的安全物件實體。該實體的預設格式包含公鑰和私鑰。當未使用 FROM 子句執行時，CREATE ASYMMETRIC KEY 會生成新的金鑰對。當使用 FROM 子句執行時，CREATE ASYMMETRIC KEY 會從檔案中匯入金鑰對，或從程式集中匯入公鑰。
預設情況下，私鑰受資料庫主金鑰保護。如果尚未建立任何資料庫主金鑰，則需要使用密碼保護私鑰。如果不存在資料庫主金鑰，則可以選擇性地使用密碼。

通常使用RSA加密演算法，RSA_512、RSA_1024、RSA_2048。

---1.建立非對稱金鑰；非對稱金鑰可以由密碼、資料庫主金鑰、EKM模組加密
--使用密碼加密
CREATE ASYMMETRIC KEY AsymmetricByPW
    WITH ALGORITHM = RSA_2048   ---使用RSA_2048加密演算法
    ENCRYPTION BY PASSWORD = 'AsymmetricByPW111'; 
GO
--2.通過檔案建立非對稱金鑰
CREATE ASYMMETRIC KEY AsymmetricByFile 
    AUTHORIZATION Christina ----授予Christina使用者使用該非對稱金鑰
    FROM FILE = 'c:\PacSales\Managers\ChristinaCerts.tmp'  
    ENCRYPTION BY PASSWORD = 'AsymmetricByFile111';
GO
---3.使用資料庫主金鑰加密
CREATE ASYMMETRIC KEY AsymmetricByMasterKey
    WITH ALGORITHM = RSA_2048;   ---使用RSA_2048加密演算法

---4.刪除非對稱金鑰
DROP ASYMMETRIC KEY AsymmetricByPW
GO

5.對稱金鑰

建立對稱金鑰時，必須至少使用以下項之一來對該對稱金鑰進行加密：證照、密碼、對稱金鑰、非對稱金鑰或 PROVIDER。可使用上述每種型別中的多項對金鑰進行加密。換言之，可以同時使用多個證照、密碼、對稱金鑰以及非對稱金鑰對單個對稱金鑰進行加密

通常使用AES演算法，有AES_128、AES_192、AES_256

 

--1.建立對稱金鑰，對稱金鑰可以由密碼、非對稱金鑰、對稱金鑰、EKM模組加密
---使用密碼加密
CREATE SYMMETRIC KEY SymmetricByPW
    WITH ALGORITHM = AES_256
    ENCRYPTION BY PASSWORD = 'SymmetricByPW111';
GO
--注意:當使用密碼（而不是資料庫主金鑰的公鑰）對對稱金鑰進行加密時，便會使用 TRIPLE DES 加密演算法。因此，用強加密演算法（如 AES）建立的金鑰本身受較弱演算法的保護。
---2.使用證照加密
CREATE SYMMETRIC KEY SymmetricByCert
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE MyCerts;

---3.刪除非對稱金鑰
DROP SYMMETRIC KEY TestSymmetric
GO

三、安全物件

安全物件是 SQL Server 資料庫引擎授權系統控制對其進行訪問的資源。通過建立可以為自己設定安全性的名為“範圍”的巢狀層次結構，可以將某些安全物件包含在其他安全物件中。安全物件範圍有伺服器、資料庫和架構。

1.安全物件範圍：伺服器

包含以下安全物件：

  • 端點
  • 登入帳戶
  • 資料庫

2.安全物件範圍：資料庫

包含以下安全物件：

  • 使用者
  • 角色
  • 應用程式角色
  • 程式集
  • 訊息型別
  • 路由
  • 服務
  • 遠端服務繫結
  • 全文目錄
  • 證照
  • 非對稱金鑰
  • 對稱金鑰
  • 約定
  • 架構

3.安全物件範圍：架構

包含以下安全物件：

  • 型別
  • XML 架構集合
  • 物件

物件

下面是物件類的成員：

    • 聚合
    • 約束
    • 函式
    • 過程
    • 佇列
    • 統計資訊
    • 同義詞
    • 表
    • 檢視  

四、案例 

案例1.備份加密

 通過使用證照加密備份，如果需要在新的伺服器上還原備份，先還原資料庫主金鑰和證照，然後就可以自動解密還原備份。

--1.備份資料庫主金鑰
BACKUP MASTER KEY TO FILE = 'D:\DECRYPTION\MasterKey' -----主金鑰私鑰檔案
    ENCRYPTION BY PASSWORD = 'MasterKey';             -----加密主金鑰私鑰檔案
GO 
--2.備份證照
BACKUP CERTIFICATE MyCerts TO FILE = 'D:\DECRYPTION\MyCerts' ----證照檔案
    WITH PRIVATE KEY ( FILE = 'D:\DECRYPTION\MyCertsKey' ,   ----證照私鑰檔案
                       ENCRYPTION BY PASSWORD = 'MyCerts123' ); ----對私鑰檔案加密
GO

--3.備份資料庫
USE MASTER
GO
BACKUP DATABASE [EncryDb]    
TO DISK = N'D:\BackDB\EncryDb.bak'    
WITH  COMPRESSION, stats = 10,   
    ENCRYPTION     
    (    
    ALGORITHM = AES_256,  
    SERVER CERTIFICATE = MyCerts
    )  
GO 
---4.在新伺服器上還原資料庫主金鑰
USE MASTER
GO
RESTORE MASTER KEY  
FROM FILE = 'C:\DECRYPTION\MasterKey' 
DECRYPTION BY PASSWORD = N'MasterKey'    ---解密主金鑰檔案
ENCRYPTION BY PASSWORD = N'MasterKey123'  ---加密匯出的主金鑰
--force;        ----指定即使當前資料庫主金鑰未開啟，或者 SQL Server 無法對使用該主金鑰加密的某些私鑰進行解密，RESTORE 過程也應繼續執行。
GO 
---5.在新伺服器上還原證照
USE MASTER
GO
---需要先開啟資料庫主金鑰
OPEN MASTER KEY DECRYPTION BY PASSWORD = N'MasterKey123'  
GO 
CREATE CERTIFICATE MyCerts FROM FILE = 'C:\DECRYPTION\MyCerts' ----證照檔案
    WITH PRIVATE KEY ( FILE = 'C:\DECRYPTION\MyCertsKey' ,    ----證照私鑰檔案
    DECRYPTION BY PASSWORD = 'MyCerts123' );                  ----解密私鑰檔案

---6.在新伺服器上還原資料庫
USE [master]  
GO  
OPEN MASTER KEY DECRYPTION BY PASSWORD = N'MasterKey123'  
GO 
RESTORE DATABASE [EncryDb]  
FROM  DISK = N'C:\DECRYPTION\EncryDb.bak'   
WITH  FILE = 1,  
MOVE N'EncryDb' TO N'C:\DECRYPTION\EncryDb.mdf',     
MOVE N'EncryDb_log' TO N'C:\DECRYPTION\EncryDb_log.ldf',    
NOUNLOAD,  STATS = 5  
GO

注意：在master資料庫中建立資料庫主金鑰和證照。

如果沒有還原資料庫主金鑰和證照直接還原資料庫報錯如下

案例2.TDE透明資料庫加密

 通過使用證照加密資料庫

 

步驟操作如下：

    • 建立主金鑰
    • 建立或獲取由主金鑰保護的證照
    • 建立資料庫加密金鑰並通過此證照保護該金鑰
    • 將資料庫設定為使用加密
USE EncryDb;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyCerts;
GO
USE EncryDb;
GO
---啟用資料庫加密
ALTER DATABASE EncryDb
SET ENCRYPTION ON;
GO
USE EncryDb;
GO
---禁用資料庫加密
ALTER DATABASE EncryDb
SET ENCRYPTION OFF;
GO

1.“透明資料加密”(TDE) 可對資料和日誌檔案執行實時 I/O 加密和解密。這種加密使用資料庫加密金鑰 (DEK)，該金鑰儲存在資料庫引導記錄中以供恢復時使用。DEK 是使用儲存在伺服器的 master 資料庫中的證照保護的對稱金鑰，或者是由 EKM 模組保護的非對稱金鑰。TDE 保護“處於休眠狀態”的資料，即資料和日誌檔案。它提供了遵從許多法律、法規和各個行業建立的準則的能力。軟體開發人員籍此可以使用 AES 和 3DES 加密演算法來加密資料，且無需更改現有的應用程式。
2.啟用 TDE 時，應該立即備份證照和與證照相關聯的私鑰。如果證照變為不可用，或者如果必須在另一臺伺服器上還原或附加資料庫，則必須同時具有證照和私鑰的備份，否則將無法開啟該資料庫。即使不再對資料庫啟用 TDE，也應該保留加密證照或非對稱金鑰。即使資料庫沒有加密，資料庫加密金鑰可能也保留在資料庫中，執行某些操作時可能需要訪問這些加密金鑰。
3.資料庫檔案的加密在頁級執行。已加密資料庫中的頁在寫入磁碟之前會進行加密，在讀入記憶體時會進行解密。TDE 不會增加已加密資料庫的大小。

注意：在master資料庫中建立資料庫主金鑰和證照。

測試在新的資料庫中還原TDE透明加密資料庫

---備份資料庫
USE MASTER
GO
BACKUP DATABASE [EncryDb]    
TO DISK = N'C:\DECRYPTION\EncryDb0122.bak'    
WITH  COMPRESSION, stats = 10  

---在新伺服器中還原資料庫主金鑰
USE MASTER
GO
RESTORE MASTER KEY  
FROM FILE = 'C:\DECRYPTION\MasterKey' 
DECRYPTION BY PASSWORD = N'MasterKey'    ---解密主金鑰檔案
ENCRYPTION BY PASSWORD = N'MasterKey123'  ---加密匯出的主金鑰
--force;        ----指定即使當前資料庫主金鑰未開啟，或者 SQL Server 無法對使用該主金鑰加密的某些私鑰進行解密，RESTORE 過程也應繼續執行。
GO 
---在新伺服器上還原證照
USE MASTER
GO
---需要先開啟資料庫主金鑰
OPEN MASTER KEY DECRYPTION BY PASSWORD = N'MasterKey123'  
GO 
CREATE CERTIFICATE MyCerts FROM FILE = 'C:\DECRYPTION\MyCerts' ----證照檔案
    WITH PRIVATE KEY ( FILE = 'C:\DECRYPTION\MyCertsKey' ,    ----證照私鑰檔案
    DECRYPTION BY PASSWORD = 'MyCerts123' );                  ----解密私鑰檔案


---還原備份
USE [master]  
GO  
OPEN MASTER KEY DECRYPTION BY PASSWORD = N'MasterKey123'  
GO 
RESTORE DATABASE [EncryDb_20180122]  
FROM  DISK = N'C:\DECRYPTION\EncryDb0122.bak'   
WITH  FILE = 1,  
MOVE N'EncryDb' TO N'C:\DECRYPTION\EncryDb0122.mdf',     
MOVE N'EncryDb_log' TO N'C:\DECRYPTION\EncryDb0122_log.ldf',    
NOUNLOAD,  STATS = 5  
GO

注意：經測試發現只有同資料庫版本可以還原成功，在搞版本中還原提示會提示錯誤頁，比如2014版本加密的資料庫在2016版本中還原保持如下：

案例3.儲存過程加密

在AS前增加WITH ENCRYPTION加密選項即可

USE EncryDb;
GO
CREATE PROCEDURE Sptest
WITH ENCRYPTION ---加密選項
AS
BEGIN


END

注意:
1.加密前先保留儲存副本，否則加密完再需要解密就很麻煩
2.加密過的儲存過程不影響修改、刪除，但是無法檢視儲存過程的定義比如:sp_helptext、生成create語句、生成alter語句等。

案例4.資料列加密

CREATE DATABASE TestDb
GO
USE [TestDb]
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKey'
GO
---建立證照
CREATE CERTIFICATE MyCerts 
   WITH SUBJECT = 'BackDB Records', 
   EXPIRY_DATE = '10/31/2099';            ----證照過期時間，不指定開始時間預設開始時間為當前時間
GO
----建立對稱金鑰
CREATE SYMMETRIC KEY SymmetricByCert
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE MyCerts;

----建立測試表
USE TestDb;
GO
DROP TABLE Test
GO
CREATE TABLE Test
(Id INT NOT NULL,
Name NVARCHAR(30) NOT NULL,
EncryptionName varbinary(500) null
);
GO
INSERT INTO Test(Id,Name) VALUES(1,'aa'),(2,'bb');
GO

----加密列
OPEN SYMMETRIC KEY SymmetricByCert 
     DECRYPTION BY CERTIFICATE MyCerts;  
UPDATE Test
SET EncryptionName= EncryptByKey(Key_GUID('SymmetricByCert'), Name);  
GO

SELECT * FROM TEST

GO
---解密查詢
OPEN SYMMETRIC KEY SymmetricByCert 
     DECRYPTION BY CERTIFICATE MyCerts;  
select Id,
Name,
EncryptionName,
convert(nvarchar(30), DecryptByKey(EncryptionName)) ConvertEncryptionName  ----nvarchar(30)值和明文欄位型別長度保持一致 
from test;

注意：

1.加密列的資料型別必須是nvarchar資料型別，否則解密後的結果不會和明文一致。

2.解密過程定義的資料型別需要和明文的資料型別保持一致，包括長度也必須一致。

 

 

 

公鑰和私鑰的解釋參考：http://blog.csdn.net/tanyujing/article/details/17348321

加密參考：https://docs.microsoft.com/zh-cn/sql/relational-databases/security/encryption/encryption-hierarchy

使用對稱金鑰加密資料：https://docs.microsoft.com/zh-cn/sql/t-sql/functions/encryptbykey-transact-sql

 

 

 

備註：

    作者：pursuer.chen

    部落格：http://www.cnblogs.com/chenmh

本站點所有隨筆都是原創，歡迎大家轉載；但轉載時必須註明文章來源，且在文章開頭明顯處給明連結，否則保留追究責任的權利。

《歡迎交流討論》

 

相關文章