實現SQL Server多層面加密機制

■ 河南 郭建偉

SQL Server作為常用的數據庫，在實際工作中應用的極為廣泛。如何提高SQL Server的安全性，防止其中的數據被盜取或者篡改，是管理員必須面對的重要問題。使用加密技術，可以有效保護SQL Server數據庫的安全。隨著SQL Server版本的演進，其中不斷添加了各種加密機制，能夠有力的保護數據的安全。這里就以SQL Server 2016為例，從加密傳輸、加密數據庫、加密字段等多個層面，來說明具體的實現方法。

創建所需的的證書

對于數字證書來說，可以從網上購買公網證書，也可以生成企業私有證書，對于后者來說，最好在客戶端安裝根證書，來信任證書鏈。證書準備好之后，接下來在SQL Server服務器上上啟用傳輸加密功能，在客戶端進行連接時，需要指定傳輸加密的連接，例如在相關的客戶端程序中添加諸如“Encrypt=True”，“TrustServerCertificate=Ture”等參數。

在SQL Server服務器上點擊“Windows+R”鍵，運行“mmc”程序，在管理控制臺上點擊菜單“文件”→“添加/刪除管理單元”項，在打開窗口左側選擇“證書”項，點擊“添加”按鈕按鈕將其添加進來。

點擊“確定”按鈕，選擇“計算機賬戶”項，點擊“完成”按鈕，在控制臺左側選擇“個人”→“證書”項，在右側可以看到在域環境中由企業CA頒發的證書。

如果沒有的話，可以在右鍵菜單上點擊“所有任務”→“申請新證書”項，在向導界面中選擇“Active Directory注冊策略”項，點擊“下一步”按鈕，選擇所需的證書。

這些證書是由CA服務器發布出來的。例如選擇“Web服務器”項，點擊“注冊此證書需要詳細信息，單擊這里以配置設置”鏈接，在證書屬性窗口中的“使用者”面板的“類型”列表中選擇“公用名”項，在“值”欄中輸入本機的FQDN計算機全名。

點擊“添加”按鈕，將其添加進來。在“常規”面板中輸入輸入其友好名稱，在“私鑰”面板中的“密鑰選項”欄中選擇“使私鑰可以導出”項，便于導出私鑰。保存配置信息后，點擊“注冊”按鈕，可以申請到所需的證書。

對于該證書來說，默認只能由管理員和系統來訪問其私鑰，并利用該證書加密數據，對于其他用戶是不允許的。

管理SQL Server服務賬戶

打開SQL Server 2016配置管理器，在左側選擇“SQL Server服務”項，在右側顯示所有的服務和實例，選擇所需的實例（例如，“SQL Server(MSSQLSERVER)”等），在“登錄身份為”列中查看其使用的服務賬戶信息。一般來說，該賬戶都不是特權賬戶。在上述控制臺上選擇證書，在其右鍵菜單中選擇“所有任務”→“管理私鑰”項，在打開窗口中點擊“添加”按鈕，輸入上述服務賬戶名稱，將其添加到“組或用戶名”列表中。選擇該賬戶，在權限列表中的“允許”列中選擇“讀取”項，即賦予其讀取私鑰的權限。

注意，還需要添加SQL Server默認的內置服務賬戶，但是該賬戶是無法直接顯示的。

點擊“添加”按鈕，在打開窗口中點擊“位置”按鈕，選擇本機名稱。之后輸入“nt servicemssqlserver”，將其該賬戶添加進來，按照同樣的方法，賦予其讀取私鑰的權限。

在SQL Server 2016配置管理器左側選擇“SQL Ser ver網絡配置”→“MSSQLSER VER的協議”項，在其右鍵菜單上點擊“屬性”項，在打開窗口中的“標志”面板中的“Force Envryption”欄中選擇“是”項，啟用強制加密功能。在“證書”面板中的“證書”列表中選擇上述證書，點擊“確定”按鈕保存配置信息。

在左側選擇“SQL Serve r”項，在右側選擇“SQL Ser ver(MSSQLSERVER)”服務，在其右鍵菜單上點擊“重新啟動”項，重啟該服務使上述配置生效。

激活加密傳輸功能

之后在本機上運行Micr osoft SQL Server Managem ent Studio（簡稱MSSMS）進行連接測試，在連接到服務器窗口中的“選項”按鈕，在“連接屬性”面板中選擇“加密連接”和“信任服務器證書”項。

在連接窗口中的“服務器名稱”欄中輸入上述證書中指定的計算機全名，點擊“連接”按鈕，才可以順利連接SQL Server服務器，否則系統會出現“目標主要名稱不正確”的錯誤提示。

對于加密的連接來說，可以打開“數據庫”→“master”→“視圖”→“系統視圖”項，在其中選擇“sys.dm_exec_connections”或者“sys.dm_exec_sessions”視圖，根據顯示信息，可以觀察到當前連接是否加密狀態。

為了便于客戶端訪問，需要在防火墻上放行TCP 1433，UDP1434端口，后者是被SQL Server Browser服務器使用，其主要用于管理多個SQL Server實例。

在MSSMS中選擇“實例名稱”→“安全性”→“登錄名”項，在其右鍵菜單上點擊“新建登錄名”項，創建新用戶（例如“user1”等），使用SQL Server身份驗證方式。

在左側選擇“用戶映射”項，在右側的“映射到此登錄名的用戶”欄選擇合適的數據庫名稱，在“數據庫角色成員身份”列表中選擇“db_datareader”項，使其針對該數據庫擁有讀取權限。

注意，對于沒有加入域的客戶端來說，只能使用SQL Server賬戶進行連接。

在客戶端上可以打開CMD窗口，執行“psping xxx:1433”命令，使用psping工具，對目標SQL Server服務器進行探測，其中的“xxx”為具體的域名。之后運行MSSMS等工具，使用上述SQL Server賬戶進行連接，輸入密碼后，可以順利連接到目標服務器上，并且只能對指定的數據庫有讀取權限。

SQL Server數據庫的加密架構

對數據庫加密來說，加密的對象是存儲在磁盤上的數據庫文件。出于性能和安全性的考慮，SQL Server使用的是對稱密鑰加密方式。為了保護密鑰的安全，還可以使用非對稱加密技術，對該對稱密鑰進行保護。對于非對稱密鑰（例如證書等）來說，則使用數據庫主密鑰進行保護。對于整個SQL Server加密體系來說，最外層的是Windows系統，其依靠Data Protection API組件來保護服務主密鑰。

可以看出，對于SQL Server加密體系來說，其并不是獨立存在的，其最終是依靠操作系統層面提供保護的。當SQL Server安裝完成后，會自動生成服務主密鑰，用來保護數據庫主密鑰。對于數據庫文件加密來說，主要使用SQL Server內置的透明數據加密TDE技術來實現。當然，也可以使用EFS和BitLocker來加密數據。對于TDE來說，最外層是依靠Windows的DPAPI組件。

在此基礎上，可以創建數據庫主密鑰，其存在于Master數據庫中。在該數據庫中創建一張證書，該證書用來保護最終加密數據使用到的Database Encryption Key對稱密鑰。注意，該密鑰是存放在需要加密的目標數據庫中的。這樣，就形成為完整的保護鏈。除了數據庫證書外，其余的密鑰都是對稱密鑰。從整體上來說，TDE需要使用到數據庫證書，其加密對象是存儲到磁盤的數據文件，可以防止數據文件的物理竊取，同時將加密日志和備份文件，采用的是后臺加密方式。

在實際使用SQL Server過程中，有時需要更改其服務賬戶，其方法是打開SQL Server配置管理器，在其中雙擊SQL Server服務，在其屬性窗口中打開“登錄”面板，在其中選擇“本賬戶”項，設置新的賬戶和密碼信息。在此過程中，服務主密鑰也需要對更換后賬戶進行保護，即服務主密鑰是通過該服務的密碼來實現實現保護的。如果直接在系統內置的服務器管理器打開SQL Server服務屬性窗口，在其中更改服務賬戶的話，就容易造成服務主密鑰異常，導致數據加密失敗的情況發生。

生成TDE加密證書

當然，也可以在MSSMS查詢窗口中執行“backup service master key to fil e='f:akfileServiceMas terKey.bak ENCRYPTION NY PASSWORD='xxxxxx'”，“Go”語句，來備份服務主密鑰。對應的執行“restore service master key from file='f:akfileServiceMasterKey.bak DECRYPTION NY PASSWORD='xxxxxx'”，“Go”語句，來恢復服務主密鑰，其中的“xxxxxx”為具體的密碼。執行“Select * From sys.symmetric_keys”命令，來查看數據庫主密鑰的信息和狀態。

執行“SELECT name,IS_MASTER_KEY_ENCRYPTED_BY_SERVER FROM sys.databases”命令，可以查看是否被服務主密鑰加密。

執行“backup master key to file='f:akfileD BMasterKey.bak ENCRYPTION BY PASSWORD='xxxxxx'”命令，備份數據庫數據庫主密鑰到指定的文件。

執行“restore master key from file='f:akfileDBMasterKey.bak DECRYPTION NY PASSWORD='x xxxxx' ENCRYPTION BY PASS WORD='xxxxxx'”命令，從指定的備份文件恢復數據庫主密鑰。”執行“USE master;”，“Go”，“CREATE MASTER KEY ENCRYPTION BY PASSWORD='xxxxxx'”，“Go”，“CREATE CERTIFICATE TDECert WITH SUBJECT='MY TDE CERT'”，“Go”語句，來創建TDE使用的加密證書。

使用TDE加密數據庫

其中的“xxxxxx”為具體的密鑰，即數據庫主密鑰即通過服務主密鑰進行保護，也通過該密碼進行保護，實現雙重保護效果。

打開“數據庫”→“系統數據庫”→“master”→“安全性”→“證書”項，在其下可以看到名為“TDECert”的證書。

在MSSMS左側的“對象資源管理器”列表中選擇目標數據庫（例如“TDE_DB”），在其右鍵菜單上點擊“任務”→“管理數據庫加密”項，在打開窗口（如圖1）中的“加密算法”列表中選擇合適的加密算法。

選擇“使用服務器證書”項，在列表中選擇“TDECert”證書，選擇“將數據庫加密設置為ON”項，點擊“確定”按鈕，激活加密功能。

注意，在執行加密設置時，該數據庫必須處于獨占訪問狀態。

圖1 配置字段加密功能

其方式是執行“ALTER DATABASE TPE_DB 4 SET SINGLE_USER WITH ROLLBACK IMMEDIATE”命令，即可將目標數據庫設置為獨占狀態。

這樣，即使別人將該數據庫文件竊取，當在其他的SQL Server數據庫實例中附加該數據庫文件時，會出現“無法為該請求檢索數據”的提示，使其無法訪問該加密的數據庫文件。因為證書實際上是保存在Master數據庫中的，因此只要備份了Master數據庫，就相當于備份了證書信息，當對其進行恢復后，就可以恢復TDE證書了。

由于服務主密鑰和數據庫主密鑰極為重要，所以一定要對其進行備份。

常用字段加密類型

對于SQL Server數據庫中的某些字段來說，可能包含了一些敏感的信息，為了防止別人隨意查看，需要對目標字段進行加密。利用加密方式，可以加密字段的所有內容。在字段數據存儲之前就進行了加密處理，當數據存儲之后，自然就處于加密狀態了。針對字段加密，在SQL Server中提供了兩種實現方法，其一是使用加解密函數和密鑰，其二是利用Always Encrypted總是加密技術來實現。

對于前者來說，是先生成數據庫主密鑰，并據此來生成證書，利用該證書創建對稱密鑰，最后利用該對稱密鑰對數據進行加解密，其特點是實現了存儲前加密，加密技術比較陳舊，具有很好的兼容性，但是缺點是管理者角色不分離，即不能防范管理人員查看加密數據。因為管理員可以讀取證書，這樣就可以得到密鑰，所以其可以很輕松的解密數據。對于SQL Server 2016來說，可以使用總是加密這一技術，來避開上述問題。

該技術的實現需要一些前提條件，例如需要證書的存儲（包括Windows或者云端的證書存儲），需要.Net Framework 4.6組件的支持，查詢語句需要實現參數化的設置等。其也可以實現存儲前加密，配置簡單技術新穎，可以實現管理角色分離，大大提高了數據的安全性。這對于云計算云存儲等場景來說，顯得極為重要，用戶可以將數據存放在任何位置，而不必擔心其安全性。

創建證書和對稱密鑰

在MSSMS中選擇目標數據庫，在查詢窗口中執行“CREATE MASTER KEY ENCRYPTION BY PASSWORD='p@ssw0rd!'”命令，創建所需的主密鑰，這里為“p@ssw0rd!”。

執行“OPEN MASTER KEY DECRYPTION BY PASSWORD='p@ssw0rd!'”命令，打開數據庫主密鑰。

注意，字段加密和TDE加密不同，TDE加密需要將主密鑰放置到目標數據庫之外的地方。

執行“CREATE CERTIFIC ATE cert_zs -ENCRYPTION BY PASSWORD=' p@ssw0rd!' WITH SUBJECT='New ce rt01',START_DATE='1/1/20 18',EXPIRY_DATE='1/1/20 80';”命令，創建名為“cert_zs”的證書，設置其有效期等參數。如果僅僅使用數據庫主密鑰創建證書，則可以不指定具體的密碼，當然，如果指定了密碼，則可以對證書進行備份。

為了保護證書的安全，可以執行“BACKUP CERTIFICATE cert_zs TO FILE='f:certbakcert_zs.bak' WITH PRIVATE KEY”命令，可以將證書備份到指定的文件。

如果指定了加密密碼，可以執行“BACKUP CERTIFICATE -DECRYPTION BY PASSWORD='p@ssw0rd!',F ILE='f:certbakcert_zs.bak',ENCRYPTION BY PAS SWORD='pASSword00!'”之類的語句，將證書備份到指定的文件中，當然，該備份文件是處于加密狀態的。

執行“SELECT * FROM sys.certificates”命令，可以查看證書信息。對應的，執行“CREATE CERTIFICATE cert_zs FROM FILE=f:c ertbakcert_zs1.bak'WITH PRIVATE KEY”命令，可以使用數據庫主密鑰來恢復證書。

如果為證書指定了密碼，則需要執行“CREATE CERTIFICATE cert_zs FROM FILE=f:certbakcert_zs1.bak' DECRYPTION BY PASSWORD='@ssw0rd!'”之類的語句，從備份文件恢復證書。

執行“CREATE SYMMETRI C KEY newkey1 WITH ALGOR ITHM=AES_256 ENCRYPTION BY CERTIFICATE cert_zs”命令，使用上述證書，利用指定的加密算法，來產生名為“newkey1”的對稱密鑰，該對稱密鑰是受到證書保護的。

執行“SELECT * FROM sys.symmetric_keys”，“SELECT * FROM sys.openkeys”命令，可以查看對稱密鑰信息。

使用函數加解密字段

準備好了對稱密鑰后，就可以對字段進行加密了。

例如，執行“CREATE TABLE Table1 (zduan1 INT PRIMARY KEY,zuduan2 NVARCHAR(20) NOT NULL,ziduan3 MONEY NOT NULL,ziduan9 VARBINARY(MAX) NULL)”語句，在當前的數據庫中創建名為“Table1”的表，執行“INSERT Table1 VALUES(1,'a100',9000,null)”，“INSERT Table1 VALUES(1,'a200',8 000,null)”等語句，向該表中插入一些數據。執行“OPEN SYMMETRIC KEY newkey1 DECRYPTION BY CERTIFICATE cert_zs”命令，打開上述名為“newkey1”的對稱密鑰。

執行“UPDATE Table1 S ET ziduan9=ENCRYPTBYKEY(K EYGUID('newkey1'),CAST(z iduan3 AS VARCHAR(20)))”命令，使用名為“ENCRYPTBYK EY”的函數，對“ziduan9”字段進行加密，并將加密后的內容存儲到“ziduan3”字段中，這樣便于查看加密效果。

執行“Select * from Table1”命令，可以看到實際的加密效果。

執行“CLOSE SYMMETRIC KEY newkey1”，“Go”語句，關閉對稱密鑰。

在執行解密操作時，先執行“OPEN SYMMETRIC KEY newkey1 DECRYPTION BY CERTIFICATE cert_zs”命令，打開上述對稱密鑰。

執行“SELECT ziduan 1,ziduan2,ziduan3,CONVE RT(VARCHAR(20),DECRYPTB YKEY(zuduan9)) AS PAY_DEC FROM Table1”命令，使用“DECRYPTBYKEY”函數對加密的“ziduan9”字段進行解密，顯示真實的數據內容。執行“CLOSE SYMMETRIC KEY newkey1”命令，關閉對稱密鑰。

使用Always Encrypted加密

同上述字段加密方法相比，使用總是加密技術可以更加有效提高安全性。但是，這要求使用的MSSMS的版本是17.6及其以上方可。總是加密技術將密鑰和數據庫進行了分離，在目標數據庫中選擇需要加密的表，在其右鍵菜單上選擇“加密列”項，在向導界面中選擇需要加密的列，在“加密類型”列中提供了“確定性密鑰”和“隨機密鑰”兩種類型，前者性能較好，后者加密強度較高。一般來說選擇確定性密碼即可。

點擊“下一步”按鈕，在打開窗口（如圖2）中的“選擇列主密鑰”列表中選擇“自動生成列主密鑰”項，在“選擇密鑰存儲提供程序”欄中可以選擇證書存儲類型，包括WIndows證書存儲和Azure Key Vault類型。在“選擇主密鑰源”列表中選擇“當前用戶”項，表示不管采用哪一種類型，必須針對當前用戶有效，即將權限和角色進行了分離，只有掌握主密鑰的用戶才可以擁有對加密數據的控制權。

注意，操作向導運行在哪臺主機上，主密鑰就保存在該機上。

圖2 配置字段加密功能

點擊“完成”按鈕，可以生成主密鑰，并據此生成列加密密鑰。對于列加密密鑰來說，是存儲在數據庫中的。但是，該密鑰必須必須通過主密鑰才能發揮作用。主密鑰是保存在指定的證書存儲中的。即其和數據庫服務器是分開存儲的。

當加密完成后，打開該數據表，可以看到指定的列已經處于加密狀態了。在MSSMS中選擇目標數據庫，選擇“安全性”→“Always Encrypted密鑰”項，在其下可以顯示列主密鑰和列加密密鑰信息。對于后者來說，需要使用前者進行加密。因此，只要掌控了列主密鑰，就可以對數據進行解密。當然，列主密鑰并不會存儲到數據庫中的，其保存在指定的證書存儲中。