SQLServerCentral.com / SQL Server 2012 / SQL 2012 - General / TDE database Encryption_state stuck on 2 / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 02 Aug 2015 16:56:39 GMT20RE: TDE database Encryption_state stuck on 2http://www.sqlservercentral.com/Forums/Topic1488815-2799-1.aspxThanks 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,DKWed, 28 Aug 2013 14:52:43 GMTDKleinRE: TDE database Encryption_state stuck on 2http://www.sqlservercentral.com/Forums/Topic1488815-2799-1.aspxAnd what was when you executed:[code="sql"]ALTER DATABASE TestDB SET ENCRYPTION ON; [/code]Because I can simulate this when during executing [i][b]set encryption on[/b][/i] the DB is in single_user. Then even after set the db to multi_user the state remain 2, but after execute again the [i][b]ALTER DATABASE TestDB SET ENCRYPTION ON;[/b][/i] the state go to 3.Tue, 27 Aug 2013 15:41:52 GMTe4d4RE: TDE database Encryption_state stuck on 2http://www.sqlservercentral.com/Forums/Topic1488815-2799-1.aspxWhat is the user_access_desc from:[code="sql"]select * from sys.databases[/code]Tue, 27 Aug 2013 14:10:43 GMTe4d4TDE database Encryption_state stuck on 2http://www.sqlservercentral.com/Forums/Topic1488815-2799-1.aspxHello 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 MASTERGO-- Create a Master KeyCREATE MASTER KEY ENCRYPTION BY Password = 'Password1';-- Backup the Master KeyBACKUP MASTER KEY TO FILE = 'Server_MasterKey' ENCRYPTION BY Password = 'Password2';-- Create Certificate Protected by Master KeyCREATE Certificate SQLCertTDEMaster WITH Subject = 'Certificate to protect TDE key';-- Backup the CertificateBACKUP 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 TestDBGO-- Create a Database Encryption KeyCREATE DATABASE ENCRYPTION KEY WITH Algorithm = AES_128 ENCRYPTION BY Server Certificate SQLCertTDEMaster; -- Enable the Database for Encryption by TDEALTER 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,DKTue, 27 Aug 2013 09:01:19 GMTDKlein