Script to automate enabling TDE on any unencypted user databases

Question

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

Answer 1

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.

Answer 2

Here is the updated script with parameters:

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_EncryptDBs')
    DROP PROCEDURE dbo.sp_EncryptDBs
GO

CREATE PROCEDURE [dbo].[sp_EncryptDBs]
    @CertName NVARCHAR(128) = NULL,
    @BackupLocation NVARCHAR(256) = NULL,
    @Password NVARCHAR(128) = NULL,
    @ExpirationDate DATE = NULL,
    @MasterKeyPassword NVARCHAR(128) = NULL,
    @ExcludedDatabases NVARCHAR(MAX) = NULL,
    @DropMasterKey NVARCHAR(3) = 'NO'
AS
BEGIN
    SET ANSI_WARNINGS OFF;
    SET XACT_ABORT, NOCOUNT ON;

    -- Check if the backup location is valid and accessible
    DECLARE @BackupLocationExists INT = 1
    IF @BackupLocation IS NOT NULL
    BEGIN
        BEGIN TRY
            EXEC master.dbo.xp_create_subdir @BackupLocation
        END TRY
        BEGIN CATCH
            SET @BackupLocationExists = 0
        END CATCH

        PRINT 'Checking backup location: ' + @BackupLocation;
        PRINT 'Backup location exists: ' + CASE WHEN @BackupLocationExists = 1 THEN 'Yes' ELSE 'No' END;

        IF @BackupLocationExists = 0
        BEGIN
            PRINT 'Backup location is not valid or accessible. Cannot proceed with the operation.';
            RETURN;
        END
    END

    -- Create a new certificate if parameters are specified
    IF @CertName IS NOT NULL
    BEGIN
        -- If @MasterKeyPassword is not provided, cannot proceed with creating a new certificate
        IF @MasterKeyPassword IS NULL
        BEGIN
            PRINT 'Cannot create a new certificate because the master key password is not provided.';
            RETURN;
        END

        -- Try to open the master key
        BEGIN TRY
            DECLARE @OpenMasterKeyCommand NVARCHAR(MAX) = N'
                OPEN MASTER KEY DECRYPTION BY PASSWORD = ''' + @MasterKeyPassword + '''';
            EXEC sp_executesql @OpenMasterKeyCommand;
            PRINT 'Opened master key';
        END TRY
        BEGIN CATCH
            PRINT 'Cannot open master key with the provided password.';
            RETURN;
        END CATCH

        -- If @DropMasterKey is 'YES', drop the encryption keys, certificate, and master key
        IF @DropMasterKey = 'YES'
        BEGIN
            -- Find databases with an encryption key and drop it
            DECLARE @DatabaseWithKey NVARCHAR(128)
            DECLARE cur CURSOR FOR
            SELECT db_name(database_id) FROM sys.dm_database_encryption_keys
            OPEN cur
            FETCH NEXT FROM cur INTO @DatabaseWithKey
            WHILE @@FETCH_STATUS = 0
            BEGIN
                DECLARE @DropKeyCommand NVARCHAR(MAX) = N'
                    USE [' + @DatabaseWithKey + ']
                    DROP DATABASE ENCRYPTION KEY';
                EXEC sp_executesql @DropKeyCommand;
                PRINT 'Dropped encryption key for database: ' + @DatabaseWithKey;
                FETCH NEXT FROM cur INTO @DatabaseWithKey
            END
            CLOSE cur
            DEALLOCATE cur

            -- Find the certificate used for TDE and drop it
            DECLARE @CertNameToDrop NVARCHAR(128) =
                (SELECT TOP 1 c.name FROM sys.certificates c WHERE c.pvt_key_encryption_type_desc = 'DATABASE')
            IF @CertNameToDrop IS NOT NULL
            BEGIN
                DECLARE @DropCertCommand NVARCHAR(MAX) = N'
                    USE master
                    DROP CERTIFICATE [' + @CertNameToDrop + ']';
                EXEC sp_executesql @DropCertCommand;
                PRINT 'Dropped certificate: ' + @CertNameToDrop;
            END

            -- Drop the master key
            EXEC sp_executesql N'DROP MASTER KEY';
            PRINT 'Dropped master key';
        END

        -- Switch to master database
        EXEC sp_executesql N'USE master';

        -- Create a new certificate
        DECLARE @CreateCertCommand NVARCHAR(MAX) = N'
            CREATE CERTIFICATE [' + @CertName + ']
            WITH SUBJECT = ''Database Encryption'',
            EXPIRY_DATE = ''' + CONVERT(NVARCHAR(10), @ExpirationDate, 120) + '''';
        EXEC sp_executesql @CreateCertCommand;

        -- Backup the certificate
        IF @BackupLocation IS NOT NULL AND @Password IS NOT NULL
        BEGIN
            DECLARE @BackupCertCommand NVARCHAR(MAX) = N'
                BACKUP CERTIFICATE [' + @CertName + ']
                TO FILE = ''' + @BackupLocation + '\' + @CertName + '.cer''
                WITH PRIVATE KEY (FILE = ''' + @BackupLocation + '\' + @CertName + '.pvk'',
                ENCRYPTION BY PASSWORD = ''' + @Password + ''')';
            EXEC sp_executesql @BackupCertCommand;

            PRINT 'Created and backed up new certificate: ' + @CertName;
        END
    END
    ELSE
    BEGIN
        -- Check if there is an existing, non-expired certificate that is used for encryption in a user database
        SET @CertName = (
            SELECT TOP 1 c.name
            FROM sys.certificates c
            INNER JOIN sys.dm_database_encryption_keys dek ON dek.encryptor_thumbprint = c.thumbprint
            WHERE c.expiry_date > GETDATE()
        )

        IF @CertName IS NULL
        BEGIN
            PRINT 'Cannot find an existing, non-expired certificate. Please provide a certificate name.';
            RETURN;
        END

        PRINT 'Using existing certificate: ' + @CertName;
    END

    -- Split the excluded databases into a table
    DECLARE @ExcludedDatabasesTable TABLE (DatabaseName NVARCHAR(128))
    DECLARE @DatabaseName NVARCHAR(128)
    WHILE CHARINDEX(',', @ExcludedDatabases) > 0
    BEGIN
        SELECT @DatabaseName = LTRIM(RTRIM(SUBSTRING(@ExcludedDatabases, 1, CHARINDEX(',', @ExcludedDatabases) - 1))),
            @ExcludedDatabases = LTRIM(RTRIM(SUBSTRING(@ExcludedDatabases, CHARINDEX(',', @ExcludedDatabases) + 1, LEN(@ExcludedDatabases))))
        INSERT INTO @ExcludedDatabasesTable (DatabaseName) VALUES (@DatabaseName)
    END
    INSERT INTO @ExcludedDatabasesTable (DatabaseName) VALUES (@ExcludedDatabases)

    -- Encrypt databases that are not yet encrypted and not in the excluded list
    IF OBJECT_ID('tempdb..#Databases') IS NOT NULL
        DROP TABLE #Databases

    SELECT
        db_name(D.database_id) [Database]
    INTO #Databases
    FROM
        sys.databases D
    WHERE
        D.is_encrypted = 0
        AND db_name(D.database_id) NOT IN
            (
                'Master',
                'model',
                'msdb'
            )
        AND db_name(D.database_id) NOT IN
            (
                SELECT DatabaseName FROM @ExcludedDatabasesTable
            )
        AND D.state_desc = 'Online'

    DECLARE @Count INT = (SELECT COUNT(*) FROM #Databases);
    PRINT 'Number of databases to encrypt: ' + CAST(@Count AS NVARCHAR(10));

    DECLARE @Database NVARCHAR(50);
    DECLARE db_cursor CURSOR FOR
        SELECT [Database] FROM #Databases;

    OPEN db_cursor
    FETCH NEXT FROM db_cursor INTO @Database

    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT 'Current Database: ' + @Database;
        DECLARE @Command1 NVARCHAR(MAX) = N'
            USE [' + @Database + ']
            CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256
            ENCRYPTION BY SERVER CERTIFICATE [' + @CertName + ']'
        DECLARE @Command2 NVARCHAR(MAX) = N'
            ALTER DATABASE [' + @Database + ']
            SET ENCRYPTION ON
        '
        PRINT 'Encrypting Database: ' + @Database;
        PRINT 'Executing command: ' + @Command1;
        BEGIN TRY
            EXEC sp_executesql @Command1;
        END TRY
        BEGIN CATCH
            PRINT 'Error executing command: ' + ERROR_MESSAGE();
        END CATCH
        PRINT 'Executing command: ' + @Command2;
        BEGIN TRY
            EXEC sp_executesql @Command2;
        END TRY
        BEGIN CATCH
            PRINT 'Error executing command: ' + ERROR_MESSAGE();
        END CATCH

        FETCH NEXT FROM db_cursor INTO @Database
    END

    CLOSE db_cursor
    DEALLOCATE db_cursor

    SET @Count = (SELECT COUNT(*) FROM #Databases);
    PRINT 'Number of databases after loop: ' + CAST(@Count AS NVARCHAR(10));

    DROP TABLE #Databases;

    -- Switch encryption to the new or existing certificate for databases that have an expired certificate
    SELECT db_name(database_id) [Database]
    INTO #Databases2
    FROM
        sys.dm_database_encryption_keys K
        INNER JOIN sys.certificates C ON K.encryptor_thumbprint = C.thumbprint
    WHERE
        C.expiry_date < GETDATE()

    WHILE (SELECT COUNT(*) FROM #Databases2) > 0
    BEGIN
        DECLARE @Database2 NVARCHAR(50) = (SELECT TOP 1 [Database] FROM #Databases2)
        DECLARE @Command3 NVARCHAR(MAX) = N'
            USE [' + @Database2 + ']
            ALTER DATABASE ENCRYPTION KEY
            ENCRYPTION BY SERVER CERTIFICATE [' + @CertName + ']'
        PRINT 'Switching encryption to certificate ' + @CertName + ' for Database: ' + @Database2;
        EXEC sp_executesql @Command3;
        DELETE FROM #Databases2 WHERE [Database] = @Database2
    END

    DROP TABLE #Databases2

    -- Close the master key, if it was opened
    IF @CertName IS NOT NULL AND @CertName = @CertName
    BEGIN
        EXEC sp_executesql N'CLOSE MASTER KEY';
        PRINT 'Closed master key';
    END

    PRINT 'Database encryption completed successfully.';
END
GO