Transparent Data Encryption (TDE) SQL Server 2008

Question

Post reply

Transparent Data Encryption (TDE) SQL Server 2008

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

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

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

-Roy

Eric Min Valued Member Points: 53 More actions · Answer 2

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,

Eric

pietje gates Grasshopper Points: 18 More actions · Answer 3

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]

GO

CREATE CERTIFICATE TDECertificate

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

Cheers.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 4

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

-Roy

sierradream2001 Newbie Points: 7 More actions · Answer 5

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.

Buzz-523717 SSC-Addicted Points: 494 More actions · Answer 6

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.

habay4lord Grasshopper Points: 19 More actions · Answer 7

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?

Habay