SQL Server TDE卡入加密状态4

Question

我正在尝试创建一个运行备份、备份当前证书、创建新证书、备份新证书和用新证书重新生成数据库加密密钥的健壮脚本。显然，要做到这一点，您说的是一个相当复杂的脚本！我试图使它尽可能健壮，但是当运行脚本时，数据库已经陷入加密状态4。(这是我为什么要对其进行破坏的原因。)现在，在我删除和重新创建这些数据库之前，有什么方法可以迫使它们脱离状态4呢？它将不允许您关闭加密，您将得到以下错误:当加密、解密或密钥更改扫描正在进行时，无法禁用数据库加密。

我不知道发生了什么事才让他们进入这种状态，但我想不惜一切代价阻止它。

请看我的剧本。您应该能够轻松地通过创建几个db来测试这一点。

任何改进都将受到极大的赞赏，这对TDE环境中的任何人都是非常有用的。

-- *** 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

Answer 1

跟踪标志5004对于启动/停止TDE非常有用。可能值得一试，以防有帮助。

DBCC TRACEON(5004)

去

DBCC TRACEOFF(5004)

去