Disable TDE

  • Amit Lohia

    SSCertifiable

    Points: 6104

    Guys,

    What is the best way to disable TDE. I uncheck the "Set Database Encryption On property" but I am still not able to restore or attach the database on a different server.

    The value of is_encrypted in sys.databases is 0 (after the uncheck).

    The error message while restoring the database on a different server

    Msg 3283, Level 16, State 1, Line 1

    The file "AdventureWorks_Log" failed to initialize correctly. Examine the error logs for more details.

    Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

    and thumb print error while doing attach.


    Kindest Regards,

    Amit Lohia

  • Grant Fritchey

    SSC Guru

    Points: 395510

    This seems to work. Set it to off, backup the database with a new backup and you should be able to restore to another server without the need for certificates.

    ALTER DATABASE dbname

    SET ENCRYPTION OFF

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Amit Lohia

    SSCertifiable

    Points: 6104

    Same issue. Even restart of instances do not work. Did anyone try this?


    Kindest Regards,

    Amit Lohia

  • Grant Fritchey

    SSC Guru

    Points: 395510

    Yep. Just did. Works great. Turn on encryption on one machine, including setting a certificate and enabling it on the database. I ran a backup from there that was encrypted. I then disabled encryption, ran a backup and restored that backup to another server. All done. No issues.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715968

    How large is the db? might take time to undo encryption.

  • Amit Lohia

    SSCertifiable

    Points: 6104

    Can someone run the following commands and confirm if this is working for them.

    You will need two server or instances and I am assuming server already have Master Key

    USE master;

    GO

    -- Please make sure to create a newcertificate because your existing certificate may be on both

    --server.

    CREATE CERTIFICATE NewCertificate WITH SUBJECT = 'To Test TDE'

    go

    CREATE DATABASE TDEDisabling

    GO

    USE TDEDisabling

    GO

    CREATE DATABASE ENCRYPTION KEY

    WITH ALGORITHM = AES_128

    ENCRYPTION BY SERVER CERTIFICATE NewCertificate

    GO

    ALTER DATABASE TDEDisabling

    SET ENCRYPTION ON

    GO

    -- We have Encription ON

    -- Verify Encription

    WAITFOR DELAY '00:00:30'

    SELECT * FROM sys.dm_database_encryption_keys

    GO

    BACKUP DATABASE [TDEDisabling] TO DISK = N'J:\Backups\TDEDisabling.bak' WITH NOFORMAT, NOINIT, NAME = N'TDEDisabling-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

    GO

    -- Trying restoring the backup on a DIFFERENT server/instance it will fail (due to encryption)

    -- Delete the backup file

    ALTER DATABASE TDEDisabling

    SET ENCRYPTION OFF

    GO

    WAITFOR DELAY '00:00:30'

    SELECT * FROM sys.dm_database_encryption_keys

    --Confirm no encryption is going on

    GO

    BACKUP DATABASE [TDEDisabling] TO DISK = N'J:\Backups\TDEDisabling.bak' WITH NOFORMAT, NOINIT, NAME = N'TDEDisabling-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

    GO

    -- Restoring on a different server/INSTANCE.

    RESTORE DATABASE [TDEDisabling] FROM DISK = N'J:\Backups\TDEDisabling.bak' WITH FILE = 1, MOVE N'TDEDisabling_log' TO N'I:\Logs\TDEDisabling_1.LDF', NOUNLOAD, STATS = 10

    GO

    -- I am still getting the following error. Though I see the database but it is not accesable

    14 percent processed.

    24 percent processed.

    34 percent processed.

    44 percent processed.

    54 percent processed.

    64 percent processed.

    74 percent processed.

    84 percent processed.

    94 percent processed.

    100 percent processed.

    Processed 160 pages for database 'TDEDisabling', file 'TDEDisabling' on file 1.

    Processed 1 pages for database 'TDEDisabling', file 'TDEDisabling_log' on file 1.

    Msg 3283, Level 16, State 1, Line 1

    The file "TDEDisabling_log" failed to initialize correctly. Examine the error logs for more details.

    Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.


    Kindest Regards,

    Amit Lohia

  • Amit Lohia

    SSCertifiable

    Points: 6104

    Steve Jones - Editor (3/12/2009)


    How large is the db? might take time to undo encryption.

    I confirmed the status and it is not even working on a new database.


    Kindest Regards,

    Amit Lohia

  • Grant Fritchey

    SSC Guru

    Points: 395510

    I think that's a torn page error. Try running a consistency check on the source db.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715968

    There's definitely something wrong. Can you backup and restore the source without issues to itself?

  • Amit Lohia

    SSCertifiable

    Points: 6104

    No TORN page error either. Same issue with new database also. Did you try using the command I post.


    Kindest Regards,

    Amit Lohia

  • Grant Fritchey

    SSC Guru

    Points: 395510

    I hit the same issue. I went back to retest and now I'm hitting the issue on the test database, where I didn't hit it before...

    I'm trying to track down if anyone else knows about this. I can't find anything so far. I think we're going to have to open an issue on Connect. I just did a few searches there. There doesn't seem to be anything. This is either a bug, or we're missing something. I'm just confused by how I got it to work before.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Grant Fritchey

    SSC Guru

    Points: 395510

    I posted it to Connect:

    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=423249

    You can vote on it, set the priority, add your comments, especially since this is your issue.

    I'm going to keep hacking at it as time allows.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Grant Fritchey

    SSC Guru

    Points: 395510

    Nuts. I might have been premature in posting to Connect. I finally found another reference:

    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=351548&wa=wsignin1.0

    I'm testing removing the certificate now.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Grant Fritchey

    SSC Guru

    Points: 395510

    Nope removing the certificate didn't fix the issue. That makes me feel better. There must be an alter database statement that removes the reference to encryption.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715968

    Or it's removal is coming in SQL 11 :w00t::hehe:

Viewing 15 posts - 1 through 15 (of 26 total)

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