TDE database Encryption_state stuck on 2

  • 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

  • What is the user_access_desc from:

    select * from sys.databases

  • 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.

  • 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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply