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

Question

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.

Answer 1

OK, I found out what was wrong, although I am not sure I understand it...

All I had to do was drop my pre-existing master key and not re-use it.

The funny thing is that regenerating the master key (which was something I tried from early-on) didn't help either.

This feels so weird to me, but not weirder than the fact that MS does not log the exact reason for aborting the encryption in the log file.