Sql-Server
在任何未加密的使用者數據庫上自動啟用 TDE 的腳本
我正在尋找一種自動加密使用者數據庫的方法。理想情況下,這將是一個可以安排在每晚執行的作業。該工作將:查找任何未加密的使用者數據庫,創建 DEK(如果它不存在),並啟用 TDE。我們為每台伺服器上的每個數據庫使用相同的證書/PK(無論好壞)。此證書已備份。
我通過創建以下儲存過程嘗試了這一點。但是,它偶爾會失敗並出現以下錯誤:
以使用者身份執行:TAC_NT********。消息 33107,級別 16,狀態 1,伺服器 *******,第 2 行
無法啟用數據庫加密,因為它已啟用。消息 5069,級別 16,狀態 1,伺服器 ,第 2 行 ALTER DATABASE 語句失敗。加密數據庫: 警告:用於加密數據庫加密密鑰的證書尚未備份。您應該立即備份證書和與證書關聯的私鑰。如果證書變得不可用,或者如果您必須在另一台伺服器上恢復或附加數據庫,則必須備份證書和私鑰,否則您將無法打開數據庫。
加密數據庫:*********。處理退出程式碼 1. 步驟失敗
數據庫確實被加密,但作業報告失敗。
這是SP:
CREATE PROC [dbo].[sp_EncryptDBs] AS BEGIN SET ANSI_WARNINGS OFF; SET XACT_ABORT, NOCOUNT ON; /* Get Databases that are online and have no DEK */ SELECT db_name(D.database_id) [Database] INTO #Databases FROM sys.databases D WHERE NOT EXISTS ( SELECT * FROM sys.dm_database_encryption_keys DEK WHERE DEK.database_id = D.database_id ) AND db_name(D.database_id) NOT IN ( 'Master', 'model', 'msdb' ) AND D.state_desc = 'Online' WHILE (SELECT COUNT(*) FROM #Databases) > 0 BEGIN DECLARE @Database NVARCHAR(50) = (SELECT TOP 1 [Database] FROM #Databases) DECLARE @Command1 NVARCHAR(MAX) = N' USE [' + @Database + '] CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE ******** ' DECLARE @Command2 NVARCHAR(MAX) = N' ALTER DATABASE [' + @Database + '] SET ENCRYPTION ON ' PRINT 'Encypting Database: ' + @Database; EXEC sp_executesql @Command1 ; EXEC sp_executesql @Command2 ; --PRINT @Command1 --PRINT @Command2 DELETE FROM #Databases WHERE [Database] = @Database; END DROP TABLE #Databases; /* Databases that have a DEK but are in an unencypted state */ SELECT db_name(database_id) [Database] INTO #Databases2 FROM sys.dm_database_encryption_keys K WHERE K.encryption_state <> 3 WHILE (SELECT COUNT(*) FROM #Databases2) > 0 BEGIN DECLARE @Database2 NVARCHAR(50) = (SELECT TOP 1 [Database] FROM #Databases2) DECLARE @Command3 NVARCHAR(MAX) = N' ALTER DATABASE [' + @Database2 + '] SET ENCRYPTION ON ' PRINT 'Encypting Database: ' + @Database2; --PRINT @Command3 EXEC sp_executesql @Command3; DELETE FROM #Databases2 WHERE [Database] = @Database2 END DROP TABLE #Databases2 END GO
我正在檢查
K.encryption_state <> 3
(加密)以測試是否應該啟用加密。
K.encryption_state <> 2
在測試狀態時,我還應該檢查加密 ( )。