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
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);
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
Last activity: Aug 2, 2024, 06:01 AM