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

  • After disabling TDE on the original node, did you take a full or log backup to clear the encrypted portions of the transaction log? – HandyD Mar 17 at 22:07
  • Log backup. I will try to set it to simple then full recovery mode and see if that clears it. – Manuel Mar 19 at 6:09
1

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
  • Hi Max, thanks for the useful information. However, the odd thing about this case is that the DB was encrypted while using the GMSA accounts that were causing the issues and not with the accounts that replaced them. I tried to regenerate the SMK as well, ALTER SERVICE MASTER KEY REGENERATE, and it did not solved the problem either. My last alternative was to revert to normal service accounts, after replacing the GMSA for the normal accounts the issue was fixed. – Manuel Mar 25 at 17:51
  • If changing the service account used by SQL Server fixed the problem, and nothing else changed, then it is nearly 100% certain that the problem was related to the service account key. – Max Vernon Mar 25 at 17:55
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.

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.