0

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.

19
  • What is encryption_scan_state and/or encryption_scan_state_desc column? Commented Jun 21, 2023 at 11:48
  • What does sp_WhoIsActive show it's waiting on? A 10 GB database is tiny, 1.5 hours sounds like something is wrong.
    – J.D.
    Commented Jun 21, 2023 at 11:57
  • @J.D. It returns no results when doing EXEC sp_WhoIsActive on the master DB (using version 12 of sp_WhoIsActive). But I had this issue while "installing" sp_WhoIsActive: stackoverflow.com/questions/71720321/… . Probably it is not related, since the first time I tried I didn't have Always Encrypted turned on. Commented Jun 21, 2023 at 12:12
  • What about sp_BlitzWho?...does it return it any results and what are the session wait stats for those results?
    – J.D.
    Commented Jun 21, 2023 at 12:15
  • @Charlieface I think those columns were added on a later version of SQL Server. I use SQL Server 2017. Commented Jun 21, 2023 at 12:15

2 Answers 2

0

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.

0

I just added this and worked for me

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

https://stackoverflow.com/questions/46373723/please-create-a-master-key-in-the-database-or-open-the-master-key-in-the-session

New contributor
ozziem is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.