Transparent Data Encryption (TDE) SQL Server 2008

  • Thanks for chiming in Tom. I do not have much clue about Auditing. This could explain the time difference.


  • Thanks 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,


  • Hello,

    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]



    FROM FILE = 'E:\MSSQL10.MSSQLSERVER\MSSQL\Backup\Certificate_EncryptionKey\TDECertificate.cert'


    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 R2

    Eventually 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.)


  • Thank You for posting the issue and the way you solved it. It will help others if they encounter this problem.


  • The 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.

  • This 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.

  • Hi Roy,

    Great work!Well articulated and precise with both pros ans cons but i have a little challenge while trying to implement this on my testDB.

    I got "an error occurred during decryption " error message while trying to cretae a masterkey .What could be the reason?


Viewing 7 posts - 76 through 81 (of 81 total)

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