none
SQL Server TDE stuck encryption state 4 RRS feed

  • Question

  • 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.

    *** UPDATED ***

    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) ,
        @Live AS NCHAR(3) = 'No'
     -- *** MAKE SURE YOU CHECK THIS BEFORE RUNNING ***
    
    
    --	 specify database backup directory
     SET @Path = 'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\'
    
    --	 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 + ''' 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  
    
    -- 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
     DECLARE @CurrentCertificateName AS NVARCHAR(100) ,
        @CertificateBackupFile AS NVARCHAR(256) ,
        @KeyBackup AS NVARCHAR(256) ,
        @KeyStore AS NVARCHAR(256) = 'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Key Backup\' ,
        @SecurePass AS NVARCHAR(50) = '*** Password ***'
    
    -- 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
    
    -- 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
    
    
    --  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
    
    --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  
    
     -- 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



    • Edited by jhowe1 Wednesday, January 8, 2014 6:16 PM
    Wednesday, January 8, 2014 2:20 PM

All replies

  • Hello,

    State 4 means (as you've noted in your script) that there is a key change in process. When a key change happens with TDE, all of the data must first be decrypted with the old keys and encrypted with the new keys which takes time. However long it takes to decrypt and encrypt your entire database (depending on how many key changes there are in the hierarchy) is how long it will take.

    There is also a very niche scenario where database corruption can cause issues with TDE while encrypting or decrypting. You could run a CHECKDB and validate this is not the case (you can also check suspect_pages at a quick glance).


    Sean Gallardy | Blog | Twitter

    Wednesday, January 8, 2014 2:32 PM
  • Hi Sean, thanks for your response.  THese are tiny databases which should take minutes to reencrypt.  One of them is blank.  I'll have a look at CHECKDB.
    Wednesday, January 8, 2014 3:19 PM
  • CHECKDB won't work.  I get error 

    Msg 945, Level 14, State 2, Line 1
    Database '' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

    Wednesday, January 8, 2014 3:21 PM
  • Hello,

    A few questions:

    1. Does the percentage complete move at all in the sys.dm_database_encryption_keys dmv?

    2. What does it say in the SQL Server Errorlog (in general and specific to the checkdb run)?


    Sean Gallardy | Blog | Twitter

    Wednesday, January 8, 2014 3:29 PM
  • hi i posted error above.  the percentage doesn't change it was properly stuck.  Although i'm not sure what was causing the error (i think it was something to do with regenerating the key.  I've put a couple extra checks in which seems to have resolved the issue.  (I'm going to try with large databases next).  I think this will do it.  Will update my code.
    Wednesday, January 8, 2014 6:08 PM
  • If the error you posted above is from the errorlog then you don't have enough disk space or memory... it's fairly straightforward. Is this the case, is that database set to a max size that can't grow and has no free space or the disk it is on does not have enough space?

    Sean Gallardy | Blog | Twitter

    Wednesday, January 8, 2014 6:57 PM