Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

TDE database Encryption_state stuck on 2 Expand / Collapse
Author
Message
Posted Tuesday, August 27, 2013 9:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:23 PM
Points: 114, Visits: 904
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
Post #1488815
Posted Tuesday, August 27, 2013 2:10 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:43 AM
Points: 223, Visits: 1,720
What is the user_access_desc from:
select * from sys.databases

Post #1488938
Posted Tuesday, August 27, 2013 3:41 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:43 AM
Points: 223, Visits: 1,720
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.
Post #1488966
Posted Wednesday, August 28, 2013 2:52 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:23 PM
Points: 114, Visits: 904
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
Post #1489424
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse