5

I disabled TDE on a database and once completed I dropped the DB certificate for it. Later I performed a manual failover to node2 and ran steps to enable TDE once again. However encryption state is stuck in state 2 with 0% of progress.

The error log shows:

Database Encryption scan for database was aborted. Reissue ALTER DB to resume the scan.

Every time I reissue the alter statement to turn encryption on it shows the same error. If I try setting encryption off I get an error that an encryption process is ongoing and can't get turned off.

I read this question but when I try to delete the certificate it gives me an error that the certificate is in use.

SQL Server 2016 TDE: Database encryption scan for 'DB_name' was aborted

0
2

Changing the account you use to run SQL Server means SQL Server is unable to unlock the service master key.

The service master key is protected (encrypted) using the Windows Data Protection API, which includes using the machine key and the account key for the GMSA.

When you change the service account, you prevent SQL Server from accessing the correct account key, and thereby prevent SQL Server from being able to decrypt the service master key, and in turn the TDE encryption key.

Changing the service back to the original account allows SQL Server to access the correct keys that allow it to decrypt the service master key, and thereby correctly decrypt all the other keys used in TDE.

I have a couple of related posts at SQLServerScience.com:

  1. Filesystem Security and TDE keys
  2. Cannot use Certificate, Private Key not Present
0
0

The solution was caused by the GMSA accounts running the SQL engine service. After switching back to normal AD accounts the issue in question was resolved. Please feel free to comment if you have any insight on why GMSA accounts cause this.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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