Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
Could you give the output of SELECT @@VERSION please? –  Edward Dortland Jan 8 at 20:37
    
hi i'm using sql 2012, my production machine is 2008. –  jhowe Jan 10 at 9:30

1 Answer 1

up vote 1 down vote accepted

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

share|improve this answer
    
what does that trace flag do? –  jhowe Jan 10 at 9:50
    
It stops the encryption scan. Handy when there is a problem and the scan can't finish and stalls (for example if it encounters corruption). –  John Alan Jan 10 at 14:00
    
Thanks John! Although after i finished my script i was unable to reproduce my issue, this will come in handy in case it crops up again... i will post my finished code. –  jhowe Jan 14 at 9:06
1  
@jhowe, what do you mean by " after i finished my script, I was unable to reproduce?" What did you change? I read on your post at the ms forum: social.msdn.microsoft.com/Forums/sqlserver/en-US/… that you had trouble running checkdb? I'm currious on what the root cause was. –  Edward Dortland Jan 14 at 10:32
    
It must have been a bug in my code around regenerating the key. Maybe it tried to regenerate the DEK and while it was doing that it created a new certificate or something so the original was lost, i really couldn't tell you. I implemented more error handling and rewrote step 5 and then couldn't reproduce the original issue. I also put another check in to make sure there were no backups running before it started regenerating the DEK. –  jhowe Jan 16 at 10:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.