Is there a way to disable database encryption before initial encryption completes?



  • I am able to start, suspend, and resume encryption. Once started I can't find a way to disable encryption until initial encryption completes. Is there a way to accomplish that?

    For example...

    -- enable encryption
    ALTER DATABASE Test
    SET ENCRYPTION ON
    

    -- wait a few minutes and check progress, finding it is only 0.00001% complete :(
    SELECT DB_NAME(database_id), encryption_state, percent_complete
    FROM sys.dm_database_encryption_keys

    -- performance is terrible so suspend
    ALTER DATABASE Test
    SET ENCRYPTION SUSPEND

    -- consider options and decide to just forget about it, turn off encryption
    ALTER DATABASE Test
    SET ENCRYPTION OFF

    -- lol, nope
    Cannot disable database encryption while an encryption, decryption, or key change scan is in progress.
    ALTER DATABASE statement failed



  • The doc lists a few https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver15#restrictions regarding the encryption process:

    The following operations are disallowed during the CREATE DATABASE ENCRYPTION KEY, ALTER DATABASE ENCRYPTION KEY, DROP DATABASE ENCRYPTION KEY, and ALTER DATABASE...SET ENCRYPTION statements:

    • [...]
    • Using an ALTER DATABASE command

    By the doc you shouldn't be allowed even to use the SET ENCRYPTION SUSPEND since it is an ALTER DATABASE command. Possibly an outdated/misleading text from the doc, but since the command to turn encryption off is the one you used and the error message was clear, I'd say there's no way other than to wait until it finishes the encryption to turn it off (there might be a catch when https://www.sqlservercentral.com/articles/removing-tde ).




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2