InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 02:18:48 GMT20http://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxThis is now the third time I've had to use this article as a reference for moving the same database. 4 different data centers for the same database. Fantastic article. Thank you again.Sat, 12 May 2012 10:19:37 GMTBuzz-523717http://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxThe solution for disabling TDE is pretty simple. After running the ALTER DATABASE command, and allowing time for the database to decrypt, you simply need to run the DROP DATABASE ENCRYPTION KEY command in the database in question. The database can then be restored onto another server without the certificate.Wed, 13 Apr 2011 15:51:38 GMTsierradream2001http://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxThank You for posting the issue and the way you solved it. It will help others if they encounter this problem.Fri, 19 Nov 2010 06:35:31 GMTRoy Ernesthttp://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxHello, I followed this article and everything went fine untill I tried to create the certificate on the second instance.(Both instances on the same server both with different windows users as service accounts)USE [master]GOCREATE CERTIFICATE TDECertificateFROM FILE = 'E:\MSSQL10.MSSQLSERVER\MSSQL\Backup\Certificate_EncryptionKey\TDECertificate.cert'WITH PRIVATE KEY (FILE = 'E:\MSSQL10.MSSQLSERVER\MSSQL\Backup\Certificate_EncryptionKey\TDE_Certificate_DYNAMICS.key',DECRYPTION BY PASSWORD = ‘xxxxxxxx’)I get the following:The certificate, asymmetric key, or private key file does not exist or has invalid format.My server OS is Windows 2008 R2.Sql version 2008 R2Eventually I found that the service account of the second SQL instance received a "access denied" error on the "certificate" and private key" files. Giving the service account of the second instance full control NTFS rights on this files fixed my problem. (Maybe read only rights would have been enough.)Cheers.Fri, 19 Nov 2010 06:15:01 GMTpietje gateshttp://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxThanks Tom & Roy,I am in (UTC-05:00) Eastern Time (US & Canada) time zone. The SQL Audit log is written to a text file by SQL Server.What do I need to do to get the correct date / time to log into the SQL Audit text file?There is no IIS installed on the server by the way.Thanks a lot,EricThu, 18 Nov 2010 08:41:32 GMTEric Minhttp://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxThanks for chiming in Tom. I do not have much clue about Auditing. This could explain the time difference.Thu, 18 Nov 2010 06:54:16 GMTRoy Ernesthttp://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspx[quote][b]Eric Min (11/17/2010)[/b][hr]Thanks Roy.We are in Eastern Time zone.I think the time zone is not a problem.The problem is that the SQL Audit event log time is different from the Server current date.It is applying to SQL Audit alone. Nothing to do with TDE.Thanks,Eric[/quote]Eastern Time Zone? As in UTC-5? And 3:48 server time is 8:48 SQL Audit Log time according to your earlier post. That looks as if the SQL audit event log time is UTC time while the Server current date is Easter Time.I used to get awfully bored explaining to people that the default IIS log format used UTC even in India, and even in the summer in the UK. Don't know if SQL Server Audit logging does the same.Thu, 18 Nov 2010 06:23:34 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxThanks Roy.We are in Eastern Time zone.I think the time zone is not a problem.The problem is that the SQL Audit event log time is different from the Server current date.It is applying to SQL Audit alone. Nothing to do with TDE.Thanks,EricWed, 17 Nov 2010 08:55:28 GMTEric Minhttp://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxDo you by any chance have any time zone difference? TDE should not have any issues with Auditing. But I have heard that Auditing can have issues with replication.Wed, 17 Nov 2010 05:02:06 GMTRoy Ernesthttp://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxHello, Roy,The TDE works fine on SQL Server 2008 R2 for me.Now I am trying to use SQL Audit in SQL Server 2008.Everything is working but the Audit log date somehow is different from the SQL Sever or Windows System date. For example, the current date is 11/16/2010 3:48 PM but the Audit log date displays as 11/16/2010 8:48 PM.Do you have any idea?Thanks a lot for your help in advance.EricTue, 16 Nov 2010 13:48:59 GMTEric Minhttp://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxThanks Tom. I tried to do that and I guess I succeeded.Mon, 18 Oct 2010 06:41:25 GMTRoy Ernesthttp://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxReally excellent article - nice and clear, easy to understand, and seems to cover all the issues. Thanks Roy.Sat, 16 Oct 2010 13:22:56 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxI have the same problem, the status is 2 but in my case percent complete is 100Wed, 30 Jun 2010 13:44:29 GMTPatrick-386510http://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxDid as what you said but the percent_complete is still shown 0.I also think it might be a permission issue but no idea what it is.Thanks a lot,EricTue, 18 May 2010 15:02:59 GMTEric Minhttp://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxIs there anything in the SQL error log? Till now no one has reported issues in setting up TDE on SP1 and Windows 2008. Keep in mind that Windows 2008 OS has this new security feature that it will not allow some file sttirbutes modification if you do not run the application as administrator. What I would like you to try is when you are opening SSMS, right click on the shortcut and choose "Run as administrator"Tue, 18 May 2010 14:52:30 GMTRoy Ernesthttp://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxUnder a domain user account. This user has Administrator's rights on the Windows server and is assigned sysadmin role on SQL server.Thanks,EricTue, 18 May 2010 14:41:27 GMTEric Minhttp://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxUnder what user is the service running? This looks more like permission issue. That is probably why encryption_state is 2.Tue, 18 May 2010 14:31:21 GMTRoy Ernesthttp://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxHi Roy,The percent_complete = 0 and never changed. It was working on a different server (VM) without SP1 before. The server was destroyed after the testing.Now I am working on a new server with SQL server 2008 + SP1 on Windows Server 2008.Thanks,EricTue, 18 May 2010 14:14:44 GMTEric Minhttp://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxHi Eric,Did you check the percent_complete value from the sys.dm_database_encryption_keys? Also are you trying to set up TDE on the same system where you tested without SP1?Tue, 18 May 2010 12:54:36 GMTRoy Ernesthttp://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxHello Roy,I successfully implemented TDE on SQL Server 2008 without SP1 before.Now I am testing the TDE on SQL Server 2008 with SP1 (version 10.0.2531.0) but it is not working any more (encryption_state is equal 2 forever).The files are not ready only and there is no filestream data type used.Do you know what the problem is?Thanks a lot.EricTue, 18 May 2010 12:30:36 GMTEric Minhttp://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxThank Eric for the compliments.. And Thanks Steve for clearing Erics doubt.Tue, 27 Apr 2010 07:10:17 GMTRoy Ernesthttp://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxYes, once the first db has been marked for TDE, tempdb will as well since it is used by queries into the encrypted database.Mon, 26 Apr 2010 16:05:19 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxYes... terrificly clear, and concise article. It let me do my own testing very quickly.Is it correct that 'tempdb' will always have encryption enabled once any dataabase has used TDE? Even after those databases are removed from the instance? Seems that-- in my experience. Yikes...eric johnsonann arbor, miMon, 26 Apr 2010 15:59:51 GMTeric.johnson 33562http://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxWhen one of my friend reported this error to Microsoft they said that they would fix it in the next release. Then they have taken a 360 degree turn saying that they wont be fixing it. They kept it like that by design and therefore it is termed as not a Bug. I did not test it with the latest release of SQL 2008 yet. When I do get a chance to do that, I will test it and see if it is still there.Thu, 04 Feb 2010 06:09:54 GMTRoy Ernesthttp://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxHi Roy,Thanks for the quick reply.I went through the URL, and Microsoft person says the resolution to aviod the error as below:[i]'To avoid this, you can take a log backup and a database backup after TDE is disabled to remove dependency upon the encryption key (and hence the certificate) from that point forward. Alternatively, you can switch to the simple recovery model.'[/i]But they did not confirm that it's a BUG in SQL Server 2008 SP1 and did not say that it's going to be fixed in next service pack release. And at the top, it's says that the issue is closed as by design.can you put some more light on thisthanksWed, 03 Feb 2010 15:36:40 GMTklnsudduhttp://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxI think this is the URL you are looking for.https://connect.microsoft.com/SQLServer/feedback/details/423249/disabling-encryption-on-tde-database-causes-restore-errorWed, 03 Feb 2010 13:30:15 GMTRoy Ernesthttp://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxHi Roy,This is one of the best articles. I followed the exact steps and got the same error after disabling the TDE.[quote]Just as an Update, the issue raised by Mohit and Grant will be fixed in the next release of SQL 2008What version of Service pack do you have. I know that this was an issue with Service pack 1. This was a known issue in Connect and Microsoft themselves made a statement that it will fixed with the next version of Service Pack[/quote]I just want the link where Microsoft declared that this as a bug and going to be fixed in Next service pack?I need to show my management and convence them to wait until next service pack to use TDE feature.I appreciate your helpBy the way, I have tested this in SQL Server 2008 x86 with SP1 on Windows 2003 x86 with SP2thanks againWed, 03 Feb 2010 12:57:19 GMTklnsudduhttp://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxNo problem Damon. Glad that it got sorted out.Thu, 10 Sep 2009 12:03:51 GMTRoy Ernesthttp://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxSorry I took so long to get back... other things popped up.Your advice put me on the right track. I restored the DMK, built the certificate, and restored the database successfully. :-)I was doing this restore on a separate instance, but on the same server where the original encrypted database was generated.I assume that if this were a different server I would first have to restore the backup of the Service Master Key before starting the process.In any event, thanks for your help. I appreciate it.DamonThu, 10 Sep 2009 11:49:16 GMTDamon Falconi-345992http://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxYou have to create the Master key first before you create the Certificate. Try it out please and let me know.Mon, 07 Sep 2009 07:21:18 GMTRoy Ernesthttp://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxSo, It’s the old good news bad news:The good news is that I was unable to restore the test DB backup on the new instance.The bad news is that when I attempt to recreate the certificate using the backups of the TDE Cert and key on the new instance via….USE [master]GOCREATE CERTIFICATE TDECertificateFROM FILE = 'E:\MSSQL10.MSSQLSERVER\MSSQL\Backup\Certificate_EncryptionKey\TDECertificate.cert'WITH PRIVATE KEY ( FILE = 'E:\MSSQL10.MSSQLSERVER\MSSQL\Backup\Certificate_EncryptionKey\TDE_Certificate_DYNAMICS.key', DECRYPTION BY PASSWORD = ‘xxxxxxxx’)I get the following:[b]The certificate, asymmetric key, or private key file does not exist or has invalid format.[/b]Which doesn’t look too good.Fri, 04 Sep 2009 12:57:37 GMTDamon Falconi-345992http://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxI did attempt to restore the backup of the encrypted database to a different server – which produced the following result:[b]Cannot find server certificate with thumbprint '0x8BF8CD0AC53329DE9283087552646C5499E2C0AB'.RESTORE FILELIST is terminating abnormally.[/b]I got the same result when attempting to attach the .mdb and .ldf files to that server.On the original server, I was able to restore the backup without issue.I’m going to create a separate instance and try the restore, on that without the certificate… then create the certificate on the new instance and try the restore again.Even if everything works properly, the encryption process does NOT give me a warm and fuzzy feeling.Transparent Data Encryption seems a little too transparent.Thanks for your help.Fri, 04 Sep 2009 10:33:45 GMTDamon Falconi-345992http://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxHi Damon,When the encryption is done, the percent_completed should be 0 itself. Did you by any chance take the back up and try to restore it without license and with license?Fri, 04 Sep 2009 06:31:41 GMTRoy Ernesthttp://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxHopefully someone can point me in the right direction.I Backed up the SMKCreated a DMK on the master DBCreated a Certificate on the master DBCreated an encryption key in my "test" database - based on the CertificateBacked up the Certificate and KeyThen ran the ALTER DATABASE to set the Encryption ONThe process - ran REALLY fast... I checked the sys.dm_database_encryption_keysIt had a status of 2... 0 percent complete; I check this repeatedly... percent never changed.Then both my test and the tempdb appeared complete - status = 3, but both still have percent complete of zero.Has something strange happened?Thu, 03 Sep 2009 17:07:26 GMTDamon Falconi-345992http://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxThanks for the response Roy. _UBTue, 18 Aug 2009 18:36:51 GMT_UBhttp://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxWhen encrypting a sql server 2008 database, an essential step is to backup the certificate(s) used to encrypt the Database Encryption keyWhat is the source of the private key that is backed up in this statement?BACKUP CERTIFICATE sales09 TO FILE = 'c:\storedcerts\sales09cert'[b]WITH PRIVATE KEY[/b] ( DECRYPTION BY PASSWORD = '9875t6#6rfid7vble7r' ,FILE = 'c:\storedkeys\sales09key' ,ENCRYPTION BY PASSWORD = '9n34khUbhk$w4ecJH5gh' );What is the signficance of this private key - when does it get used in encryption? Does it get created during the certificate creation step?I run into errors when restoring the encrypted backed up database on another server 'A key required by this operation appears to be corrupted. RESTORE FILELIST is terminating abnormally (Microsoft SQL Server, Error: 15507 )'Is info on the private key associated with the certificate stored in SQL Server 2008? Can a user determine if the private key has been backed up, [maybe by using some dmv] so that one can avoid the warning message when enabling TDE :'Warning: The certificate used for encrypting the database encryptionkey has not been backed up. You should immediately back up thecertificate and the private key associated with the certificate. Ifthe certificate ever becomes unavailable or if you must restore orattach the database on another server, you must have backups of boththe certificate and the private key or you will not be able to openthe database.'TIA :-)Mon, 17 Aug 2009 22:36:38 GMTtrey.jonnhttp://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxNo SP- Just RTM version,but I am using the developer edition. I am in the process of loading SP1 to see if I run into that issue you expressed in the article.BTW, great article Roy- well explained- Helped me thru understanding the setup, etc.Fri, 24 Jul 2009 06:44:32 GMTFresh Squeezehttp://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspx[quote][b]Fresh Squeeze (7/23/2009)[/b][hr]I have 2 responses that I did not see a reply yet to. so here we go:1) TDE is an available option on Enterprise and Developer editions only.2) Roy, this goes back to testing out the issues with TDE- I fortunately was able to turn the encryption off and and then try and restore from 2 different full database backups (using developer edition, RTM): 1) I tried restoring from an encrypted full backup- worked fine with replace option 2) I tried restoring from an non-encrypted full backup- worked fine with replace option.So, I am not sure if the limitations are only related to tlog restores after you switch an encrypted DB to non-encrypted DB. I also did try the worst case scenario-I blew away the master key and certificates and rebuilt from the files I backed up- it worked fine too. Stay tuned and I will let you know if the tlog restore option worked of failed like Roy expressed. Overall I like the TDE feature very much.[/quote]What version of Service pack do you have. I know that this was an issue with Service pack 1. This was a known issue in Connect and Microsoft themselves made a statement that it will fixed with the next version of Service Pack.Thu, 23 Jul 2009 15:20:48 GMTRoy Ernesthttp://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspx[quote][b]UB (7/16/2009)[/b][hr]Roy, Thanks for the great article. Steve pointed this article out to me as I was looking for some info on general database safety. [url]http://www.sqlservercentral.com/Forums/Topic754288-146-1.aspx#bm754352[/url] I have a question though.In the article, you said.....[quote]"That means that a small/medium sized database (Anywhere above 50 GIG) will probably have a backup file of that size itself. If someone is able to steal the backup file of that size, you have a very big issue."[/quote]Do you mean....[quote]If a TDE enabled backup is stolen, it cannot be restored. So its well protected. But the bigger problem is that if someone could steal that large file, then we are in big trouble, right? [/quote]Because, right now, I need to come up with ways to protect database files when Sql Server is installed on a laptop is stolen. BTW, its a Sql 2005. Or login info in to the laptop is compromised.Any help is appreciated.thanks,_Uday[/quote]let us say that your laptop that has the DB installed is stolen I do not think there is anything you can do other than actual encryption of data. (That is if they are able to Login to the Laptop.) If they are not able to login because of very strong password, then even if they are able to take out the HDD and copy the file somewhere else and try to attach the file, they wont be able to do that.But there are other things you can do. There is some tools that actually encrypts the HDD. You could try to use them.Thu, 23 Jul 2009 15:18:20 GMTRoy Ernesthttp://www.sqlservercentral.com/Forums/Topic709018-1188-1.aspxI have 2 responses that I did not see a reply yet to. so here we go:1) TDE is an available option on Enterprise and Developer editions only.2) Roy, this goes back to testing out the issues with TDE- I fortunately was able to turn the encryption off and and then try and restore from 2 different full database backups (using developer edition, RTM): 1) I tried restoring from an encrypted full backup- worked fine with replace option 2) I tried restoring from an non-encrypted full backup- worked fine with replace option.So, I am not sure if the limitations are only related to tlog restores after you switch an encrypted DB to non-encrypted DB. I also did try the worst case scenario-I blew away the master key and certificates and rebuilt from the files I backed up- it worked fine too. Stay tuned and I will let you know if the tlog restore option worked of failed like Roy expressed. Overall I like the TDE feature very much.Thu, 23 Jul 2009 14:16:26 GMTFresh Squeeze