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
Anonymous User
11-Sep-2015