Sample Header Ad - 728x90

Transparent Data Encryption doesn't appear to start encrypting the DB

0 votes
2 answers
763 views
I have enabled TDE on a big DB of mine (mdf file:9.6GB, log file:12.6GB). I have followed the steps from this video (amongst other sources): https://www.youtube.com/watch?v=6EhCeLbAS_o It has been more than 1.5 hour, and when running the query to check on the status , the progress appears to be zero! SELECT DB_NAME(database_id) AS DatabaseName, encryption_state, encryption_state_desc = 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' WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)' ELSE 'No Status' END, percent_complete, encryptor_thumbprint, encryptor_type FROM sys.dm_database_encryption_keys TDE Progress results I have checked the value of the 5004 trace flag (which is said to be able to pause the encryption and produce similar results ), and it is not set: DBCC TRACESTATUS (5004); State of 5004 trace flag This is the second time I try to enable encryption on the same restored backup, and on both times I got the same delay. So, I am beginning to wonder if I am missing something here. Does anyone have any ideas if this delay to see some progress is to be expected? If not, any ideas on what might be wrong? **UPDATE:** I still can't make this work. SQL Server aborts the encryption process without reporting the reason. I took a look here and didn't get much wiser. These are the steps I've used the last time, while trying to be extra careful :
-- I regenerate all keys, just in case:
		ALTER SERVICE MASTER KEY FORCE REGENERATE;
		GO
		OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mypass';
		GO
		ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'mypass';
		-- If it doesn't exist already: CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mypass';
		GO
		-- Restart SQL Server Service
		-- Restore Backup
		-- Change compatibility level for the newly created DB to 2017
		-- Restart SQL Server Service
		-- Create certificate:
		use master;
		GO
		OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mypass';
		GO
		CREATE CERTIFICATE SECURED_DB_EncryptionCertificate WITH SUBJECT = 'SECURED_DB_EncryptionCertificate';
		-- 
		USE SECURED_DB;
		GO
		CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256
		ENCRYPTION BY SERVER CERTIFICATE SECURED_DB_EncryptionCertificate;
		GO
		-- Backup keys and certificates.
		use master;
		go
		backup certificate SECURED_DB_EncryptionCertificate to file= 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\SECURED_DB_EncryptionCertificate'
		with private key (file = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\SECURED_DB_EncryptionCertificateKey.pvk', encryption by password = 'mypass');
		go
		-- Avoid having active connections to DB
		USE master;
		-- Encrypt DB
		ALTER DATABASE SECURED_DB SET ENCRYPTION ON;
At this point, the SECURED_DB encryption gets aborted, and restarting the SQL Server service makes master lose its encryption and SECURED_DB gets into permanent recovery mode.
Asked by user2173353 (303 rep)
Jun 21, 2023, 11:01 AM
Last activity: Aug 2, 2024, 06:01 AM