TDE database Encryption_state stuck on 2

• DKlein

  SSCrazy

  Points: 2351

  More actions

  August 27, 2013 at 9:01 am

  #395377

  Hello all,

  I am testing TDE in a lab environment for a proof of concept for roll out. I have 3 servers, a primary, a disaster recovery using AlwaysOn Availability Groups, and a report server subscriber using transaction replication from the primary. I set up the primary first using the scripts below:

  USE MASTER

  GO

  -- Create a Master Key

  CREATE MASTER KEY ENCRYPTION BY Password = 'Password1';

  -- Backup the Master Key

  BACKUP MASTER KEY

  TO FILE = 'Server_MasterKey'

  ENCRYPTION BY Password = 'Password2';

  -- Create Certificate Protected by Master Key

  CREATE Certificate SQLCertTDEMaster

  WITH Subject = 'Certificate to protect TDE key';

  -- Backup the Certificate

  BACKUP Certificate SQLCertTDEMaster

  TO FILE = 'SQLCertTDEMaster_cer'

  WITH Private KEY (

  FILE = 'SQLCertTDEMaster_key',

  ENCRYPTION BY Password = 'Password3'

  );

  Then I encrypted the Primary DB with the following scripts:

  USE TestDB

  GO

  -- Create a Database Encryption Key

  CREATE DATABASE ENCRYPTION KEY

  WITH Algorithm = AES_128

  ENCRYPTION BY Server Certificate SQLCertTDEMaster;

  -- Enable the Database for Encryption by TDE

  ALTER DATABASE TestDB

  SET ENCRYPTION ON;

  When I run the following select statement (select * from sys.dm_database_encryption_keys

  ). It lists the database in an encryption_state = 2, with 0 percent_complete. It has been that way for 24 hours and the database is only 2GB.

  Any suggestions?

  Thanks,

  DK

• e4d4

  SSCertifiable

  Points: 5777

  More actions

  August 27, 2013 at 2:10 pm

  #1645339

  What is the user_access_desc from:

  select * from sys.databases

• e4d4

  SSCertifiable

  Points: 5777

  More actions

  August 27, 2013 at 3:41 pm

  #1645360

  And what was when you executed:

  ALTER DATABASE TestDB SET ENCRYPTION ON;

  Because I can simulate this when during executing set encryption on the DB is in single_user. Then even after set the db to multi_user the state remain 2, but after execute again the ALTER DATABASE TestDB SET ENCRYPTION ON; the state go to 3.

• DKlein

  SSCrazy

  Points: 2351

  More actions

  August 28, 2013 at 2:52 pm

  #1645748

  Thanks for the replies. I did not have the database in single-user mode, as far as I am aware of. I ended up having to remove the master key and certificate. Then I added them again, this time without Availability Groups and transactional replication, and it worked. I will be trying to get the whole configuration up again tomorrow.

  Thanks again,

  DK