Certificate I created could not be restored!!!

• Laura_SqlNovice

  SSCrazy

  Points: 2751

  More actions

  April 9, 2012 at 12:57 pm

  #259728

  Hi Guys,

  I created a certificate to be used for Encryption in SQL 2005 and backed up the files; however, when I tried to recreate the certificate after I dropped it, I was not able to restore using the backups. Please suggest what might be the issue? I was using Query Analyzer to do all this....

  This is the error I get...

  --ERROR

  Msg 15208, Level 16, State 1, Line 1

  The certificate, asymmetric key, or private key file does not exist or has invalid format.

  Thanks for taking time.

  Laura

  IF NOT EXISTS (

  SELECT * FROM sys.certificates WHERE name = N'TEST_CERTIFICATE'

  )

  CREATE CERTIFICATE TEST_CERTIFICATE

  WITH SUBJECT = 'TEST CERTIFICATE';

  SELECT * FROM sys.certificates

  --=======================================================

  --=======================================================

  --BACKUP THE CERTIFICATE

  --=======================================================

  BACKUP CERTIFICATE TEST_CERTIFICATE

  TO FILE = 'D:\Encryption\TEST_CERTIFICATE_BACKUP.cer'

  WITH PRIVATE KEY

  (

  FILE=N'D:\Encryption\TEST_CERTIFICATE_ALT.pvk',

  ENCRYPTION BY PASSWORD = N'ABC123' --Change password!!!

  );

  I was able to see the backup files!!!

  --=============================================================

  --Restore Certificate using the backup created above

  --=============================================================

  SELECT * FROM sys.certificates

  --Dropping the certificate (if only needed)

  DROP CERTIFICATE TEST_CERTIFICATE

  SELECT * FROM sys.certificates

  --Now restore using the backup (if only needed)

  CREATE CERTIFICATE TEST_CERTIFICATE

  FROM FILE = 'D:\Encryption\TEST_CERTIFICATE_BACKUP.cer'

  WITH PRIVATE KEY

  (

  FILE=N'D:\Encryption\TEST_CERTIFICATE_ALT.pvk',

  ENCRYPTION BY PASSWORD = N'ABC123'

  )

  --ERROR

  Msg 15208, Level 16, State 1, Line 1

  The certificate, asymmetric key, or private key file does not exist or has invalid format.

• calvo

  SSChampion

  Points: 12930

  More actions

  April 9, 2012 at 1:24 pm

  #1470889

  Laura_SqlNovice (4/9/2012)

  ---

  --Now restore using the backup (if only needed)

  CREATE CERTIFICATE TEST_CERTIFICATE

  FROM FILE = 'D:\Encryption\TEST_CERTIFICATE_BACKUP.cer'

  WITH PRIVATE KEY

  (

  FILE=N'D:\Encryption\TEST_CERTIFICATE_ALT.pvk',

  ENCRYPTION BY PASSWORD = N'ABC123'

  )

  [/code]

  "DECRYPTION BY PASSWORD" is the correct syntax

  ______________________________________________________________________________________________
  Forum posting etiquette.[/url] Get your answers faster.

• Laura_SqlNovice

  SSCrazy

  Points: 2751

  More actions

  April 9, 2012 at 1:28 pm

  #1470891

  Calvo I just solved this and yes exactly like you said. Thanks a lot calvo.