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
Best Answer
I was checking
K.encryption_state <> 3
(encrypted) to test whether I should enable encryption.i should have also checked for encrypted (
K.encryption_state <> 2
) when testing the status.