前幾天研究了一下sql數(shù)據(jù)庫(kù)的透明加密,記下來(lái)加深一下理解.

用腳本創(chuàng)建文件夾

 1 --查文件夾有沒(méi)有 2 EXEC master.dbo.xp_fileexist 'D:/DATA/storedcerts' 3 EXEC master.dbo.xp_fileexist 'D:/DATA/storedkeys' 4  5 --開(kāi)啟系統(tǒng)存儲(chǔ)過(guò)程高級(jí)選項(xiàng) 6 EXEC sp_configure 'show advanced options',1 7 GO 8 RECONFIGURE 9 GO10 EXEC sp_configure 'xp_cmdshell',111 RECONFIGURE12 GO13 --調(diào)用DOS命令創(chuàng)建storedcerts文件夾14 EXEC xp_cmdshell 'mkdir D:/DATA/storedcerts' 15 GO16 --調(diào)用DOS命令創(chuàng)建storedkeys文件夾17 EXEC xp_cmdshell 'mkdir D:/DATA/storedkeys' 

下面開(kāi)始加密

 1 USE MASTER; 2 GO 3 --刪除原有的證書(shū)和密鑰,保險(xiǎn)一點(diǎn),如果沒(méi)有會(huì)報(bào)錯(cuò)不用管 4 DROP CERTIFICATE MyServerCert 5 GO 6 DROP MASTER KEY  7 GO 8  9 --在MASTER數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)MASTER KEY,密碼是zz.www10 CREATE MASTER KEY ENCRYPTION BY PASSWord = 'zz.www';11 GO12 13 --使用MASTER KEY創(chuàng)建證書(shū)MyServerCert14 CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';15 GO16 17 USE ceshi;18 GO19 --在ceshi數(shù)據(jù)庫(kù)上使用MyServerCert這個(gè)證書(shū)創(chuàng)建數(shù)據(jù)庫(kù)私鑰20 CREATE DATABASE ENCRYPTION KEY21 WITH ALGORITHM = AES_12822 ENCRYPTION BY SERVER CERTIFICATE MyServerCert;23 GO

接下來(lái)是備份數(shù)據(jù)庫(kù)主密鑰,數(shù)據(jù)庫(kù)證書(shū)和證書(shū)私鑰.

在創(chuàng)建證書(shū)時(shí)數(shù)據(jù)庫(kù)會(huì)提示備份證書(shū)以及相關(guān)私鑰,在沒(méi)弄明白之前筆者只備份了證書(shū),沒(méi)有管私鑰,雖然私鑰是會(huì)在備份證書(shū)的時(shí)候帶到證書(shū)里面去,但是顯然筆者把證書(shū)想簡(jiǎn)單了,等還原證書(shū)解密的時(shí)候,才發(fā)現(xiàn)數(shù)據(jù)庫(kù)主密鑰和證書(shū)私鑰是兩碼事,后面解密的時(shí)候解釋這個(gè)問(wèn)題

 1 USE MASTER; 2 GO 3 --備份數(shù)據(jù)庫(kù)主密鑰MASTER KEY 4 BACKUP MASTER KEY TO FILE = 'D:/DATA/storedkeys/masterkey' ENCRYPTION BY PASSWORD = 'zz.www' 5 GO 6  7 --備份數(shù)據(jù)庫(kù)證書(shū)和私鑰 8 BACKUP CERTIFICATE MyServerCert TO FILE = 'D:/DATA/storedcerts/MyServerCert.cer' 9     WITH PRIVATE KEY ( FILE = 'D:/DATA/storedkeys/MyServerCert.pvk' , 10     ENCRYPTION BY PASSWORD = 'zz.www' );11 GO12 13 --將ceshi數(shù)據(jù)庫(kù)設(shè)置為加密14 ALTER DATABASE ceshi SET ENCRYPTION ON;15 GO

到這里ceshi數(shù)據(jù)庫(kù)就加密成功了.

下面是解密過(guò)程,筆者只是用到了把加密的數(shù)據(jù)庫(kù)的備份文件或者數(shù)據(jù)庫(kù)原文件還原或者附加到另外的實(shí)例數(shù)據(jù)庫(kù)中.

在這里就遇到了問(wèn)題,剛開(kāi)始筆者用備份好的密鑰和證書(shū)直接還原到新實(shí)例上面,提示都還原成功了,但是用備份文件進(jìn)行還原的時(shí)候,還是會(huì)提示密鑰不對(duì).

然后筆者嘗試刪除密鑰和證書(shū),雖然提示筆者刪掉了,而且在系統(tǒng)表里面查詢(xún)也確實(shí)沒(méi)有了,但是在還原的時(shí)候依舊報(bào)錯(cuò),提示需要證書(shū)

找了下原因,官方說(shuō)法:即使對(duì)于數(shù)據(jù)庫(kù)不再啟用加密，用于保護(hù)數(shù)據(jù)庫(kù)加密密鑰的證書(shū)備份也應(yīng)保留。 即使數(shù)據(jù)庫(kù)不再加密，事務(wù)日志的某些部分仍可能保持受到保護(hù)，但在執(zhí)行數(shù)據(jù)庫(kù)的完整備份前，對(duì)于某些操作可能需要證書(shū)。說(shuō)是刪除,實(shí)際上并沒(méi)有,也是,如果直接就刪了,那這透明加密就沒(méi)什么卵用了;也就是說(shuō)即使密鑰刪了,但是證書(shū)還在,私鑰還在,即使是用DROP CERTIFICATE MyServerCert 來(lái)刪除證書(shū),然后把當(dāng)前數(shù)據(jù)庫(kù)拿到其他數(shù)據(jù)庫(kù)實(shí)例上面還原,也是無(wú)法成功的.

然后在還原好了主密鑰和證書(shū),私鑰之后,數(shù)據(jù)庫(kù)卻提示當(dāng)前數(shù)據(jù)庫(kù)實(shí)例不支持透明加密功能,這才知道,雖然大部分的數(shù)據(jù)庫(kù)版本均可以進(jìn)行加密操作,但是在新實(shí)例上面做一些還原或者附加等操作的,必須要求新的數(shù)據(jù)庫(kù)實(shí)例是企業(yè)版的才可以完美操作.

1 --查詢(xún)數(shù)據(jù)庫(kù)實(shí)例版本等信息2 SELECT SERVERPROPERTY('productversion') as '產(chǎn)品版本', SERVERPROPERTY ('productlevel') as '產(chǎn)品級(jí)別'  , SERVERPROPERTY ('edition') as '版本'

下面是筆者總結(jié)的成功還原數(shù)據(jù)庫(kù)并且可以正常操作數(shù)據(jù)庫(kù)數(shù)據(jù)的解密方法

還原主密鑰,還原證書(shū)和證書(shū)私鑰,重點(diǎn)就是證書(shū)私鑰

 1 --還原MASTER KEY  2 USE MASTER 3 RESTORE MASTER KEY  4     FROM FILE = 'D:/DATA/storedkeys/masterkey'  5     DECRYPTION BY PASSWORD = 'zz.www'  6     ENCRYPTION BY PASSWORD = 'zz.www'; 7 GO 8  9 --還原證書(shū)和私鑰10 CREATE CERTIFICATE MyServerCert11     FROM FILE = 'D:/DATA/storedcerts/MyServerCert' 12     WITH PRIVATE KEY (FILE = 'D:/DATA/storedkeys/MyServerCert.pvk', 13     DECRYPTION BY PASSWORD = 'zz.www');14 GO 15 16 --還原數(shù)據(jù)庫(kù)17 RESTORE DATABASE ceshi18 FROM disk = 'F:/DATA/2015-07-06'  19 WITH MOVE 'Test' TO 'd:/DATA/ceshi.mdf',20 MOVE 'Test_log' TO 'd:/DATA/ceshi_log.ldf' 21 GO 

到這里數(shù)據(jù)庫(kù)還原成功就ok了

此文個(gè)人觀點(diǎn),有不對(duì)之處,歡迎指正.