原文地址: https://www.mssqltips.com/sqlservertip/3572/recovering-a-sql-server-tde-encrypted-database-successfully/ 問題: 我的任務是在具有敏感信息的SQL Server資料庫上設置透明數據加密 ...
問題:
我的任務是在具有敏感信息的SQL Server資料庫上設置透明數據加密(TDE)。在我看到的示例中,我知道我需要主資料庫中的主密鑰,然後需要用該主密鑰加密的證書。 一個浮現在腦海中的問題是,如果我選擇將資料庫恢復到不同的伺服器上我的資料庫主密鑰是否必須具有相同的密碼? 我見過的每個例子都包含相同的密碼。為了成功還原,我還需要計劃些什麼?解決方案:
為SQL Server資料庫啟用TDE加密是一個簡單的過程。它包括: 1,在主資料庫中創建資料庫主密鑰master key。 2,創建由該密鑰master key加密的證書。 3,備份證書和證書的私鑰。雖然加密資料庫不需要這樣做,但您希望立即這樣做。(譯者註:為了在別的機器上還原加密後的資料庫,必須要有證書以及證書的私鑰) 4,在(用戶自定義)資料庫中創建由證書加密的密鑰。 5,修改資料庫以開啟加密。 如果您正在閱讀本文並且不熟悉版本要求,那麼TDE僅適用於Microsoft SQL Server的企業版(譯者註:自SQLServer 2017開始,企業版,標準版 web版均支持TDE )。使用TDE創建新的SQL Server資料庫
讓我們用下麵的代碼設置一個示例資料庫:USE [master]; GO -- Create the database master key -- to encrypt the certificate CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'FirstServerPassw0rd!'; GO -- Create the certificate we're going to use for TDE CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Cert for Test'; GO -- Back up the certificate and its private key -- Remember the password! BACKUP CERTIFICATE TDECert TO FILE = N'C:\SQLBackups\TDECert.cer' WITH PRIVATE KEY ( FILE = N'C:\SQLBackups\TDECert_key.pvk', ENCRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!' ); GO -- Create our test database CREATE DATABASE [RecoveryWithTDE]; GO -- Create the DEK so we can turn on encryption USE [RecoveryWithTDE]; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TDECert; GO -- Exit out of the database. If we have an active -- connection, encryption won't complete. USE [master]; GO -- Turn on TDE ALTER DATABASE [RecoveryWithTDE] SET ENCRYPTION ON; GO
這將啟動資料庫上的加密過程。請註意我為資料庫主密鑰指定的密碼。正如所暗示的那樣,當我們在第二台伺服器上執行恢復時我將使用不同的密碼,不需要擁有相同的密碼,但需要擁有相同的證書,我們將在查看恢復過程中的“陷阱”時瞭解這一點。即使在基本為空的資料庫上,加密資料庫也需要幾秒鐘的時間。可以通過以下查詢查詢加密的狀態:
-- We're looking for encryption_state = 3 -- Query periodically until you see that state -- It shouldn't take long SELECT DB_Name(database_id) AS 'Database', encryption_state FROM sys.dm_database_encryption_keys;正如註釋所示,我們希望資料庫顯示狀態為3,這意味著加密已經完成。下麵是你應該看到的一個例子:
當encryption_state顯示為3時,您應該對資料庫進行備份,因為我們將需要它來恢復到第二台伺服器(您的路徑可能不同):
-- Now backup the database so we can restore it -- Onto a second server BACKUP DATABASE [RecoveryWithTDE] TO DISK = N'C:\SQLBackups\RecoveryWithTDE_Full.bak'; GO現在我們有了備份,讓我們將該備份恢復到SQL Server的另一個實例。
Failed Restore - No Key, No Certificate
恢復受TDE保護的資料庫的第一種情況是,我們嘗試進行恢復,但沒有任何加密塊被恢復。我們沒有資料庫主密鑰,當然也沒有證書。 這就是為什麼TDE是偉大的。如果沒有這些部分,恢復就無法工作。讓我們嘗試恢復(註意:您的路徑可能不同):-- Attempt the restore without the certificate installed RESTORE DATABASE [RecoveryWithTDE] FROM DISK = N'C:\SQLBackups\RecoveryWithTDE_Full.bak' WITH MOVE 'RecoveryWithTDE' TO N'C:\SQLData\RecoveryWithTDE_2ndServer.mdf', MOVE 'RecoveryWithTDE_log' TO N'C:\SQLData\RecoveryWithTDE_2ndServer_log.mdf'; GO這將會失敗。下麵是嘗試恢復時應該看到的內容: 當SQL Server嘗試恢復時,它會意識到它需要一個證書,一個特定的證書。由於證書不存在,因此恢復失敗。
Failed Restore - The Same Certificate Name, But Not the Same Certificate
第二種情況是存在資料庫主密鑰,並且存在與第一個伺服器(甚至是相同的主題)名稱相同的證書,但它不是來自第一個伺服器的證書。讓我們設置並嘗試恢復:-- Let's create the database master key and a certificate with the same name -- But not from the files. Note the difference in passwords CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SecondServerPassw0rd!'; GO -- Though this certificate has the same name, the restore won't work CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Cert for Test'; GO -- Since we don't have the corrected certificate, this will fail, too. RESTORE DATABASE [RecoveryWithTDE] FROM DISK = N'C:\SQLBackups\RecoveryWithTDE_Full.bak' WITH MOVE 'RecoveryWithTDE' TO N'C:\SQLData\RecoveryWithTDE_2ndServer.mdf', MOVE 'RecoveryWithTDE_log' TO N'C:\SQLData\RecoveryWithTDE_2ndServer_log.mdf'; GO
請註意資料庫主密鑰密碼(與第一臺機器上的主密鑰密碼)是的不同,這兩個主密鑰的密碼這是不同的,但這不是我們在恢復中失敗的原因。和之前的情況一樣,我們沒有正確的證書。結果,您將得到與前一種情況相同的錯誤。
Failed Restore - The Right Certificate, but Without the Private Key
下一個場景是完成證書恢復,但沒有私鑰。這也將失敗。但是,在嘗試T-SQL代碼之前,可能必須修複證書文件和私鑰文件上的文件許可權。如果您試圖在與第一個實例位於同一系統上的SQL Server實例上執行恢復,則可能會出現這種情況。 為了確保第二個實例可以訪問這些文件,請轉到文件的位置並調出文件屬性(右鍵單擊文件,然後從彈出菜單中選擇properties)。單擊“安全”選項卡,然後單擊“高級”按鈕。當SQL Server寫這些文件時,它可能破壞了許可權的繼承,我們將修複這個問題。這將出現一個不同的界面,您應該查看許可權選項卡。如果您看到它,請單擊Continue按鈕。如果UAC打開,你會看到它。
現在您將打繼承選項。註意我圈出來的東西。如果框未選中,如下圖所示,這意味著取消繼承。如果您的文件就是這種情況,請單擊覆選框,併在每個介面單擊OK以重新打開繼承。
現在讓我們嘗試恢復證書,但故意忘記使用私鑰進行恢復。在從文件創建證書之前,必須先刪除剛剛創建的證書。
-- Let's drop the certificate and do the restore of it... -- But without the private key DROP CERTIFICATE TDECert; GO -- Restoring the certificate, but without the private key. CREATE CERTIFICATE TDECert FROM FILE = 'C:\SQLBackups\TDECert.cer' GO -- We have the correct certificate, but not the private key. -- This should fail as well. RESTORE DATABASE [RecoveryWithTDE] FROM DISK = N'C:\SQLBackups\RecoveryWithTDE_Full.bak' WITH MOVE 'RecoveryWithTDE' TO N'C:\SQLData\RecoveryWithTDE_2ndServer.mdf', MOVE 'RecoveryWithTDE_log' TO N'C:\SQLData\RecoveryWithTDE_2ndServer_log.mdf'; GO
我們有正確的證書,但是沒有私鑰,SQL Server不能用它來解密資料庫。結果,我們得到一個不同的錯誤,告訴我們這個鍵有問題。錯誤提示密鑰似乎已損壞,但我們知道真正的問題:我們沒有恢復密鑰。
The Successful Restore
為了執行成功的恢復,我們需要主資料庫中的資料庫主密鑰,我們需要恢復用於加密資料庫的證書,但我們需要確保使用私鑰恢復它。以核對錶形式: 1,在主資料庫中有一個資料庫主密鑰master key。 2,用於加密資料庫的證書將與其私鑰一起恢復(譯者註:還原證書時需要與創建證書時的私鑰的密碼一致,也即 DECRYPTION BY PASSWORD必須與創建證書時一致,否則無法還原證書)。 3,完成資料庫恢復。 既然有了資料庫主密鑰master key,那麼讓我們執行最後兩個步驟。當然,由於我們必須清除以前的證書,我們將在我們發出的命令中有一個刪除證書:-- Let's do this one more time. This time, with everything, -- Including the private key. DROP CERTIFICATE TDECert; GO -- Restoring the certificate, but without the private key. CREATE CERTIFICATE TDECert FROM FILE = 'C:\SQLBackups\TDECert.cer' WITH PRIVATE KEY ( FILE = N'C:\SQLBackups\TDECert_key.pvk', DECRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!' ); GO -- We have the correct certificate and we've also restored the -- private key. Now everything should work. Finally! RESTORE DATABASE [RecoveryWithTDE] FROM DISK = N'C:\SQLBackups\RecoveryWithTDE_Full.bak' WITH MOVE 'RecoveryWithTDE' TO N'C:\SQLData\RecoveryWithTDE_2ndServer.mdf', MOVE 'RecoveryWithTDE_log' TO N'C:\SQLData\RecoveryWithTDE_2ndServer_log.mdf'; GO
一切就緒,我們終於成功了!
如果在執行恢復時沒有看到升級步驟消息,請不要驚慌。 對於這個例子,我在SQL Server 2008 R2實例上創建了初始資料庫,並將其恢復到SQL Server 2014實例上。 因此,如果您有關於是否可以將受TDE保護的資料庫恢復到不同版本的SQL Server的問題,那麼答案是肯定的,只要新實例運行的是企業版,並且新實例具有相同或更高版本的SQL Server(包括服務包、累積更新和任何熱修複程式/補丁)。第二個要求不應該令人驚訝,因為這是將資料庫恢復到不同伺服器的標準要求。
Next Steps
- Read up on some more points you should know about implementing TDE in SQL Server 2008.
- Learn how to configure TDE in SQL Server 2012 when using Availability Groups.
- Understand how to set up encrypted backups in SQL Server 2014 for cases where you can't use TDE.