Home > DeveloperSection > Forums > SQL Server TDE stuck encryption state 4
Alex Leblois
Alex Leblois

Total Post:67

Points:471
Posted on    September-11-2015 4:22 AM

 MSSQL Server Database  MSSQL Server 
Ratings:


 1 Reply(s)
 1162  View(s)
Rate this:
I'm trying to create a robust script that runs backups, backs up current certificate, creates a new certificate, backs up new certificate and regenerates database encryption keys with the new certificate. Obviously to do all this you're talking about a pretty complicated script! i've tried to make it as robust as possible, however when running the script the databases have gotten stuck in encryption state 4. (this has happened before which is why i'm testing this to destruction.) now before i delete and recreate these databases is there any way to force them out of state 4? It will not allow you to turn encryption off you get the following error : Cannot disable database encryption while an encryption, decryption, or key change scan is in progress.

I'm not sure what happened to get them into this state but want to prevent it at all costs.

Please see my script. You should be able to test this easily by creating a couple db's.

Any improvements would be greatly appreciated, and this will be extremely useful to anyone in a TDE environment.

-- *** CREATE CERTIFICATE FROM CERTIFICATE BACKUPS ON LOCAL MACHINE IF REQUIRED ***
------------------------------------------------------------------------------------------------------------------------------------------------------
--USE master;
--  CREATE CERTIFICATE PCI_Compliance_Certificate_201301032240 
--      FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\PCI_Compliance_Certificate_201301032240.cer' 
--      WITH PRIVATE KEY (FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\PCI_Compliance_Certificate_201301032240.pvk', 
--      DECRYPTION BY PASSWORD = '*** SERVER MASTER KEY ***');
--  GO
------------------------------------------------------------------------------------------------------------------------------------------------------
 --This script will create new certificates, back them up, back up your old certificates and rotate 
 --the encryption using your new key. This is ideal in PCI DSS and other regulated environments 
 --where regular key rotation is neccessary. The script will determine which databases are encrypted
 --on your server instance and only re-generate the encryption for them. 
--Backup databases on server
 USE master
 DECLARE @Name NVARCHAR(50) , -- Database Name
    @Path NVARCHAR(100) , -- Path for backup files
    @FileName NVARCHAR(256) , -- Filename for backup
    @FileDate NVARCHAR(20) , -- Used for file name
    @BackupSetName NVARCHAR(50) ,
    @SQLScript NVARCHAR(MAX) ,
    @CurrentCertificateName AS NVARCHAR(100) ,
    @CertificateBackupFile AS NVARCHAR(256) ,
    @KeyBackup AS NVARCHAR(256) ,
    @KeyStore AS NVARCHAR(256) = 'E:\SQL Data\Local Backups\' ,
    @SecurePass AS NVARCHAR(MAX) = '*** PASSWORD ***' ,
    @Live AS NCHAR(3) = 'No'
 -- *** MAKE SURE YOU CHECK THIS BEFORE RUNNING ***

--   specify database backup directory
 SET @Path = 'E:\SQL Data\Local Backups\'
--   specify filename format
 SET @FileDate = REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(20), GETDATE(), 120),
                                         ':', ''), '-', ''), ' ', '')
 IF CURSOR_STATUS('global', 'db_cursor') >= -1 
    DEALLOCATE db_cursor
 DECLARE db_cursor CURSOR
 FOR
    SELECT  Name
    FROM    sys.databases
    WHERE   Name NOT IN ( 'master', 'model', 'msdb', 'tempdb' )
            AND is_encrypted = 1
 OPEN db_cursor   
 FETCH NEXT FROM db_cursor INTO @Name   
 WHILE @@FETCH_STATUS = 0 
    BEGIN TRY
        SET @FileName = @Path + @Name + '_' + @FileDate + '.bak'  
        SET @SQLScript = 'BACKUP DATABASE ' + @Name + ' TO DISK = '''
            + @FileName + ''' WITH NOFORMAT, INIT, SKIP, STATS = 10
        RESTORE VERIFYONLY FROM  DISK = ''' + @FileName
            + ''' IF (SELECT recovery_model_desc FROM sys.databases WHERE Name = '
            + CHAR(39) + @Name + CHAR(39) + ') = ''FULL'' BACKUP LOG ' + @Name
            + ' TO DISK = ''' + @Path + @Name + '_log.ldf'''
        PRINT '*** STEP ONE Backing up Databases ***'
        PRINT @SQLScript
        IF @Live = 'Yes' 
            EXEC (@SQLScript)
        FETCH NEXT FROM db_cursor INTO @Name
    END TRY
    BEGIN CATCH 
        PRINT 'Error Completing Backups' 
        SELECT  ERROR_NUMBER() AS ErrorNumber ,
                ERROR_SEVERITY() AS ErrorSeverity ,
                ERROR_STATE() AS ErrorState ,
                ERROR_PROCEDURE() AS ErrorProcedure ,
                ERROR_LINE() AS ErrorLine ,
                ERROR_MESSAGE() AS ErrorMessage;
        RETURN
    END CATCH
 CLOSE db_cursor
 DEALLOCATE db_cursor  
 PRINT '*** STEP ONE Complete ***'
-- Get current certificate statuses
 SELECT DB_NAME(database_id) AS DatabaseName ,
        Name AS CertificateName ,
        CASE encryption_state
          WHEN 0 THEN 'No database encryption key present, no encryption'
          WHEN 1 THEN 'Unencrypted'
          WHEN 2 THEN 'Encryption in progress'
          WHEN 3 THEN 'Encrypted'
          WHEN 4 THEN 'Key change in progress'
          WHEN 5 THEN 'Decryption in progress'
        END AS encryption_state_desc ,
        create_date ,
        regenerate_date ,
        modify_date ,
        set_date ,
        opened_date ,
        key_algorithm ,
        key_length ,
        encryptor_thumbprint ,
        percent_complete ,
        certificate_id ,
        principal_id ,
        pvt_key_encryption_type ,
        pvt_key_encryption_type_desc ,
        issuer_name ,
        cert_serial_number ,
        subject ,
        expiry_date ,
        start_date ,
        thumbprint ,
        pvt_key_last_backup_date
 FROM   sys.dm_database_encryption_keys AS e
        LEFT JOIN master.sys.certificates AS c ON e.encryptor_thumbprint = c.thumbprint
         -- TDE cannot be started while backup is running
 WHILE EXISTS ( SELECT  *
                FROM    master.dbo.sysprocesses
                WHERE   dbid IN ( DB_ID('*** DATABASE ***'))
                        AND cmd LIKE 'BACKUP%' )  
    BEGIN
        PRINT 'Waiting for backups to complete'
        WAITFOR DELAY '00:01:00'
    END
 --Code for backing up certificate and generating new certificate
-- Get current certificate name
 SELECT @CurrentCertificateName = c.name
 FROM   sys.dm_database_encryption_keys AS e
        LEFT JOIN master.sys.certificates AS c ON e.encryptor_thumbprint = c.thumbprint
 WHERE  DB_NAME(e.database_id) = @Name
-- backup the current certificate
 SET @CertificateBackupFile = @KeyStore + @CurrentCertificateName + '.cer'
 SET @KeyBackup = @KeyStore + @CurrentCertificateName + '.pvk'
 SET @SQLScript = 'BACKUP CERTIFICATE ' + @CurrentCertificateName
    + +' TO FILE = ''' + @CertificateBackupFile + ''' WITH PRIVATE KEY'
    + ' (FILE = ''' + @KeyBackup + ''',' + ' ENCRYPTION BY PASSWORD = '''
    + @SecurePass + ''')'
 PRINT '*** STEP TWO Backing up current certificate: ' + @SQLScript + ' ***'
 IF @Live = 'Yes' 
    BEGIN TRY
        EXEC ( @SQLScript )
    END TRY
    BEGIN CATCH
        PRINT 'Could not back up existing Certificate. Job Cancelled'
        SELECT  ERROR_NUMBER() AS ErrorNumber ,
                ERROR_SEVERITY() AS ErrorSeverity ,
                ERROR_STATE() AS ErrorState ,
                ERROR_PROCEDURE() AS ErrorProcedure ,
                ERROR_LINE() AS ErrorLine ,
                ERROR_MESSAGE() AS ErrorMessage;
        RETURN
    END CATCH
 PRINT '*** STEP TWO Complete ***'
-- Generate the new certificate.
 DECLARE @Now AS NVARCHAR(12) = REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(20), GETDATE(), 120),
                                                        ':', ''), '-', ''),
                                        ' ', '')
 DECLARE @NewCertificateName AS NVARCHAR(50) = 'PCI_Compliance_Certificate_'
    + @Now
 -- Manually set certificate name
 --SELECT @NewCertificateName = 'PCI_Compliance_Certificate_201312231546'
-- Generate a new certificate
 DECLARE @NewCertificateDescription AS NVARCHAR(100) = 'PCI DSS Compliance Certificate for 2014'
 SET @SQLScript = 'CREATE CERTIFICATE ' + @NewCertificateName
    + ' WITH SUBJECT = ''' + @NewCertificateDescription + ''''
 PRINT '*** STEP THREE Creating New Certificate: ' + @SQLScript + ' ***'
 IF @Live = 'Yes' 
    BEGIN TRY
        EXEC ( @SQLScript
        )
    END TRY 
    BEGIN CATCH
        PRINT 'Could not create the new Certificate. Job Cancelled'
        SELECT  ERROR_NUMBER() AS ErrorNumber ,
                ERROR_SEVERITY() AS ErrorSeverity ,
                ERROR_STATE() AS ErrorState ,
                ERROR_PROCEDURE() AS ErrorProcedure ,
                ERROR_LINE() AS ErrorLine ,
                ERROR_MESSAGE() AS ErrorMessage;
        RETURN      
    END CATCH
 PRINT '*** STEP THREE Complete ***'
--  Back up the new certificate
 SET @CertificateBackupFile = @KeyStore + @NewCertificateName + '.cer'
 SET @KeyBackup = @KeyStore + @NewCertificateName + '.pvk'
 SET @SQLScript = 'BACKUP CERTIFICATE ' + @NewCertificateName
    + +' TO FILE = ''' + @CertificateBackupFile + '''' + ' WITH PRIVATE KEY'
    + ' (FILE = ''' + @KeyBackup + ''',' + ' ENCRYPTION BY PASSWORD = '''
    + @SecurePass + ''')'
 PRINT '*** STEP FOUR Backing up New Certificate: ' + @SQLScript + ' ***'
 IF @Live = 'Yes' 
    BEGIN TRY
        EXEC ( @SQLScript
        )
    END TRY
    BEGIN CATCH  
        PRINT 'Error: Could not back up New Certificate.'
        SELECT  ERROR_NUMBER() AS ErrorNumber ,
                ERROR_SEVERITY() AS ErrorSeverity ,
                ERROR_STATE() AS ErrorState ,
                ERROR_PROCEDURE() AS ErrorProcedure ,
                ERROR_LINE() AS ErrorLine ,
                ERROR_MESSAGE() AS ErrorMessage;
        RETURN
    END CATCH
 PRINT '*** STEP FOUR Complete ***'
--Encrypt database with new certificate
 WHILE EXISTS ( SELECT  *
                FROM    master.dbo.sysprocesses
                WHERE   dbid IN ( DB_ID('*** DATABASE ***'))
                        AND cmd LIKE 'BACKUP%' ) 
    BEGIN
        PRINT 'Waiting for backups to complete'
        WAITFOR DELAY '00:01:00'
    END
 DECLARE db_cursor CURSOR
 FOR
    SELECT  Name
    FROM    sys.databases
    WHERE   Name NOT IN ( 'master', 'model', 'msdb', 'tempdb' )
            AND is_encrypted = 1
 OPEN db_cursor   
 FETCH NEXT FROM db_cursor INTO @Name   
 WHILE @@FETCH_STATUS = 0 
    BEGIN TRY
        SET @SQLScript = 'USE ' + @Name
            + ' ALTER DATABASE ENCRYPTION KEY REGENERATE WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE '
            + 'PCI_Compliance_Certificate_' + @Now
        PRINT '*** STEP FIVE Encrypting Databases ***'
        PRINT @SQLScript
        IF @Live = 'Yes' 
            EXEC (@SQLScript)
        FETCH NEXT FROM db_cursor INTO @Name
    END TRY
    BEGIN CATCH 
        PRINT 'Error Encrypting Databases' 
        SELECT  ERROR_NUMBER() AS ErrorNumber ,
                ERROR_SEVERITY() AS ErrorSeverity ,
                ERROR_STATE() AS ErrorState ,
                ERROR_PROCEDURE() AS ErrorProcedure ,
                ERROR_LINE() AS ErrorLine ,
                ERROR_MESSAGE() AS ErrorMessage;
        RETURN
    END CATCH
 CLOSE db_cursor
 DEALLOCATE db_cursor  
 PRINT '*** STEP FIVE Complete ***'
 -- Inspect the new state of the databases
 SELECT DB_NAME(e.database_id) AS DatabaseName ,
        e.database_id ,
        e.encryption_state ,
        CASE e.encryption_state
          WHEN 0 THEN 'No database encryption key present, no encryption'
          WHEN 1 THEN 'Unencrypted'
          WHEN 2 THEN 'Encryption in progress'
          WHEN 3 THEN 'Encrypted'
          WHEN 4 THEN 'Key change in progress'
          WHEN 5 THEN 'Decryption in progress'
        END AS encryption_state_desc ,
        c.name ,
        e.percent_complete
 FROM   sys.dm_database_encryption_keys AS e
        LEFT JOIN master.sys.certificates AS c ON e.encryptor_thumbprint = c.thumbprint


Mayank Tripathi
Mayank Tripathi

Total Post:397

Points:3117
Posted on    September-11-2015 4:48 AM

Trace flag 5004 is useful for starting/stopping TDE. Might be worth giving it a go in case it helps.

DBCC TRACEON(5004)

GO

DBCC TRACEOFF(5004)

GO

Don't want to miss updates? Please click the below button!

Follow MindStick