InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 16:32:13 GMT20http://www.sqlservercentral.com/Forums/Topic673845-391-1.aspxDid you use this query to turn off the TDE.USE MASTERGOALTER DATABASE DBNAMESET ENCRYPTION OFFGOUSE DBNAMEGODROP DATABASE ENCRYPTION KEYGOWed, 09 Jan 2013 22:58:14 GMTnoor.mohammadhttp://www.sqlservercentral.com/Forums/Topic673845-391-1.aspxCan you make sure you backup database to a different .bak file after you turn off the encryption?Wed, 06 Jul 2011 07:24:52 GMTlu zhanghttp://www.sqlservercentral.com/Forums/Topic673845-391-1.aspxI know this thread is over a year old but I'll post in case someone else runs into this problem.I've run into the need to know how to remove TDE. In doing my research I came across this thread and some other links with similar issues. The blogs say that setting encryption to off isn't enough. You also need to drop the database encryption key. Only after performing these two steps can you create a fresh backup of the database and restore it to a new server without encryption.[code="other"]USE MASTERGOALTER DATABASE DBNAMESET ENCRYPTION OFFGOUSE DBNAMEGODROP DATABASE ENCRYPTION KEYGO[/code]reference this link:[url]http://social.msdn.microsoft.com/Forums/en-IE/sqldatabasemirroring/thread/1df9bb6e-6cf7-4569-b91a-5d39c320f7bb[/url]Tue, 07 Dec 2010 12:20:51 GMTcalvohttp://www.sqlservercentral.com/Forums/Topic673845-391-1.aspxIf we analyze the error from backup command. It is complaining about log file. So I thought how about trying to attach just MDF file but it failed.sp_attach_single_file_db @dbname = '[TDEDisabling]', @physname = 'J:\Backups\TDEDisabling.mdf'Msg 33111, Level 16, State 3, Line 1Cannot find server certificate with thumbprint '0xBB1B0816079C10A6342683443A8E576196EFBE73'.Msg 1813, Level 16, State 2, Line 1Could not open new database '[TDEDisabling]'. CREATE DATABASE is aborted.Fri, 13 Mar 2009 13:01:41 GMTAmit Lohiahttp://www.sqlservercentral.com/Forums/Topic673845-391-1.aspx[quote][b]Amit Lohia (3/13/2009)[/b][hr]I do not think it will make much difference but my testing is on platform 64[/quote]I think that's worth noting. Now we know it happens on two platforms. If there's a way to fix it, we should find out, I hope. I also [url=http://scarydba.wordpress.com/2009/03/13/disabling-database-encryption/]blogged about[/url] it and sent out a tweet. Hopefully someone with an answer will notice.Fri, 13 Mar 2009 11:00:01 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic673845-391-1.aspx[quote][b]Grant Fritchey (3/13/2009)[/b][hr]I posted it to Connect:[url=https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=423249]https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=423249[/url]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.[/quote]I do not think it will make much difference but my testing is on platform 64Fri, 13 Mar 2009 10:55:03 GMTAmit Lohiahttp://www.sqlservercentral.com/Forums/Topic673845-391-1.aspx[quote][b]Grant Fritchey (3/13/2009)[/b][hr]Nuts. I might have been premature in posting to Connect. I finally found another reference:[url=https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=351548&wa=wsignin1.0]https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=351548&wa=wsignin1.0[/url]I'm testing removing the certificate now.[/quote]Yep, I check that too. It was only post I find related to this issues.Fri, 13 Mar 2009 10:46:32 GMTAmit Lohiahttp://www.sqlservercentral.com/Forums/Topic673845-391-1.aspxOr it's removal is coming in SQL 11 :w00t::hehe:Fri, 13 Mar 2009 09:07:12 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic673845-391-1.aspxNope 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.Fri, 13 Mar 2009 08:27:28 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic673845-391-1.aspxNuts. I might have been premature in posting to Connect. I finally found another reference:[url=https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=351548&wa=wsignin1.0]https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=351548&wa=wsignin1.0[/url]I'm testing removing the certificate now.Fri, 13 Mar 2009 08:20:24 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic673845-391-1.aspxI posted it to Connect:[url=https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=423249]https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=423249[/url]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.Fri, 13 Mar 2009 08:14:45 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic673845-391-1.aspxI 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.Fri, 13 Mar 2009 07:59:27 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic673845-391-1.aspxNo TORN page error either. Same issue with new database also. Did you try using the command I post.Thu, 12 Mar 2009 17:43:26 GMTAmit Lohiahttp://www.sqlservercentral.com/Forums/Topic673845-391-1.aspxThere's definitely something wrong. Can you backup and restore the source without issues to itself?Thu, 12 Mar 2009 17:41:32 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic673845-391-1.aspxI think that's a torn page error. Try running a consistency check on the source db.Thu, 12 Mar 2009 17:26:59 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic673845-391-1.aspx[quote][b]Steve Jones - Editor (3/12/2009)[/b][hr]How large is the db? might take time to undo encryption.[/quote]I confirmed the status and it is not even working on a new database.Thu, 12 Mar 2009 15:27:12 GMTAmit Lohiahttp://www.sqlservercentral.com/Forums/Topic673845-391-1.aspxCan 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 KeyUSE 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'goCREATE DATABASE TDEDisablingGOUSE TDEDisablingGOCREATE DATABASE ENCRYPTION KEYWITH ALGORITHM = AES_128ENCRYPTION BY SERVER CERTIFICATE NewCertificateGOALTER DATABASE TDEDisablingSET ENCRYPTION ONGO-- We have Encription ON-- Verify EncriptionWAITFOR DELAY '00:00:30'SELECT * FROM sys.dm_database_encryption_keysGOBACKUP DATABASE [TDEDisabling] TO DISK = N'J:\Backups\TDEDisabling.bak' WITH NOFORMAT, NOINIT, NAME = N'TDEDisabling-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10GO-- Trying restoring the backup on a DIFFERENT server/instance it will fail (due to encryption)-- Delete the backup fileALTER DATABASE TDEDisablingSET ENCRYPTION OFFGOWAITFOR DELAY '00:00:30'SELECT * FROM sys.dm_database_encryption_keys--Confirm no encryption is going onGOBACKUP DATABASE [TDEDisabling] TO DISK = N'J:\Backups\TDEDisabling.bak' WITH NOFORMAT, NOINIT, NAME = N'TDEDisabling-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10GO-- 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 = 10GO-- I am still getting the following error. Though I see the database but it is not accesable14 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 1The file "TDEDisabling_log" failed to initialize correctly. Examine the error logs for more details.Msg 3013, Level 16, State 1, Line 1RESTORE DATABASE is terminating abnormally.Thu, 12 Mar 2009 15:13:37 GMTAmit Lohiahttp://www.sqlservercentral.com/Forums/Topic673845-391-1.aspxHow large is the db? might take time to undo encryption.Thu, 12 Mar 2009 11:11:25 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic673845-391-1.aspxYep. 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.Thu, 12 Mar 2009 11:03:41 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic673845-391-1.aspxSame issue. Even restart of instances do not work. Did anyone try this?Thu, 12 Mar 2009 10:44:06 GMTAmit Lohiahttp://www.sqlservercentral.com/Forums/Topic673845-391-1.aspxThis 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.[code]ALTER DATABASE dbnameSET ENCRYPTION OFF[/code]Thu, 12 Mar 2009 05:42:08 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic673845-391-1.aspxGuys,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 serverMsg 3283, Level 16, State 1, Line 1The file "AdventureWorks_Log" failed to initialize correctly. Examine the error logs for more details.Msg 3013, Level 16, State 1, Line 1RESTORE DATABASE is terminating abnormally.and thumb print error while doing attach.Wed, 11 Mar 2009 17:44:03 GMTAmit Lohia