主页 / dba / 问题 / 56356
Accepted
jhowe
Asked: 2014-01-09 06:07:17 +0800 CST

SQL Server TDE 卡住加密状态 4

  • 772

我正在尝试创建一个强大的脚本来运行备份、备份当前证书、创建新证书、备份新证书并使用新证书重新生成数据库加密密钥。显然，要完成这一切，您所谈论的是一个相当复杂的脚本！我试图让它尽可能健壮，但是在运行脚本时，数据库卡在加密状态 4 中。（这之前发生过，这就是为什么我要测试它以破坏它。）现在在我删除和重新创建之前这些数据库有什么办法可以强制它们退出状态 4？它不允许您关闭加密，您会收到以下错误：加密、解密或密钥更改扫描正在进行时无法禁用数据库加密。

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

请看我的脚本。您应该能够通过创建几个数据库来轻松测试这一点。

任何改进都将不胜感激，这对 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
sql-server t-sql

1 个回答

  1. Best Answer

    跟踪标志 5004 对于启动/停止 TDE 很有用。如果它有帮助，可能值得一试。

    DBCC TRACEON(5004)

    去

    DBCC TRACEOFF(5004)

    去

    • 1

相关问题