Transparent Data Encryption (TDE) feature of SQL Server 2008

• klnsuddu

  SSCertifiable

  Points: 5836

  More actions

  February 2, 2010 at 3:47 pm

  #210505

  Hi,

  I'm trying to explore the Transparent Data Encryption (TDE) feature of SQL Server 2008 and I ran the below query. Now from where & how I can backup the CERTIFICATE and PRIVATE KEY ASSOCIATED WITH THE CERTIFICATE

  USE master;

  GO

  CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'xxxxxxx';

  go

  CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate'

  go

  USE AdventureWorks

  GO

  CREATE DATABASE ENCRYPTION KEY

  WITH ALGORITHM = AES_128

  ENCRYPTION BY SERVER CERTIFICATE MyServerCert

  GO

  ALTER DATABASE AdventureWorks

  SET ENCRYPTION ON

  GO

  Result:

  Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.

  thanks

• matt stockham

  SSCrazy Eights

  Points: 9942

  More actions

  February 2, 2010 at 10:53 pm

  #1112649

  http://msdn.microsoft.com/en-us/library/ms178578.aspx

• Suresh B.

  SSC-Insane

  Points: 22986

  More actions

  February 3, 2010 at 12:28 am

  #1112672

  BACKUP CERTIFICATE MyServerCert

  TO FILE = 'C:\MyServerCert.backup'

  WITH PRIVATE KEY (FILE = 'C:\MyServerCert_PrivateKey.backup',

  ENCRYPTION BY PASSWORD = 'xxxxxxx');

  GO