locked
Steps to remove TDE on Alwayson RRS feed

  • Question

  • Hello All,

    i am trying to remove TDE on Alwayson however, i am getting below error on primary replica.

    Use dbname

    DROP DATABASE ENCRYPTION KEY

    Msg 33123, Level 16, State 1, Line 214

    Cannot drop or alter the database encryption key since it is currently in use on a mirror or secondary availability replica. Retry the command after all the previous reencryption scans have propagated to the mirror or secondary availability replicas or after availability relationship  has been disabled.

    is there any specific way to disable TDE on alwayson.


    Tuesday, July 5, 2016 12:00 PM

Answers

  • According to your description, the issue is caused by disabling TDE encryption directly on the primary replica in Always on availability group. You can disable TDE on Always on by using the following steps:

    1.You need to disable the availability relationship on primary replica and secondary replica by using the following steps:

    1)Remove the availability group from the server instance that hosts the primary replica, then remove the availability group from secondary replica.

    2)Disable Always On availability relationship following the instructions in this article: https://msdn.microsoft.com/en-sg/library/ff878259.aspx.

    2. Remove TDE by using the following steps:

    1)Remove encryption by using the following code:

    USE master;
    ALTER DATABASE [DatabaseName]
    SET ENCRYPTION OFF;
    GO

    2)Remove the encryption key by using the following code:

    Use [DatabaseName]
    DROP DATABASE ENCRYPTION KEY
    GO

    3)Remove the certificate and master key by using the following code:

    USE master
    DROP CERTIFICATE [Certificatename]
    DROP MASTER KEY
    GO

    3. Enable availability relationship and recreate an always on availability group.

    Regards,
    Teige



    Wednesday, July 6, 2016 8:33 AM

All replies

  • According to your description, the issue is caused by disabling TDE encryption directly on the primary replica in Always on availability group. You can disable TDE on Always on by using the following steps:

    1.You need to disable the availability relationship on primary replica and secondary replica by using the following steps:

    1)Remove the availability group from the server instance that hosts the primary replica, then remove the availability group from secondary replica.

    2)Disable Always On availability relationship following the instructions in this article: https://msdn.microsoft.com/en-sg/library/ff878259.aspx.

    2. Remove TDE by using the following steps:

    1)Remove encryption by using the following code:

    USE master;
    ALTER DATABASE [DatabaseName]
    SET ENCRYPTION OFF;
    GO

    2)Remove the encryption key by using the following code:

    Use [DatabaseName]
    DROP DATABASE ENCRYPTION KEY
    GO

    3)Remove the certificate and master key by using the following code:

    USE master
    DROP CERTIFICATE [Certificatename]
    DROP MASTER KEY
    GO

    3. Enable availability relationship and recreate an always on availability group.

    Regards,
    Teige



    Wednesday, July 6, 2016 8:33 AM
  • Hi,

    For now this is only the option to remove\disable TDE for database which are configured always on availability group but Microsoft should give better solution to disable TDE without removing always on, we can follow this approach for small databases but we have few databases more than 10 TB in that case to remove always on and reconfigure always on taking long time and it risk to business as well since database will be available only on Primary. also disable TDE also takes longer time for this kind of databases.

    Please suggest if we have any better solution for this issue.

    Thanks,

    Srinivas Boddu.

    Friday, February 8, 2019 3:47 PM