I'm looking for a way to automate the encryption of user databases. Ideally this would be a job that can be scheduled to run every night. The job would: find any user database that is not encrypted, create the DEK (if it doesn't exist), and enable TDE. We use the same Certificate/PK for each database on every server (for better or worse). This certificate has been backed up.
I attempted this by creating the following stored procedure. However, it occasionally fails with the following error:
Executed as user: TAC_NT********. Msg 33107, Level 16, State 1, Server *******, Line 2
Cannot enable database encryption because it is already enabled. Msg 5069, Level 16, State 1, Server *********, Line 2 ALTER DATABASE statement failed. Encypting Database: ********* Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.
Encypting Database: *********. Process Exit Code 1. The step failed
The databases do get encrypted, but the job reports failure.
Here's the 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