Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2010
    Posts
    4

    Unanswered: Sql Server Certificate and symmetric key

    Folks,

    Is there a way to backup and restore the certificate and symmetric key from one sql server database to other.

    I tried using backing up the certificate to file and then creating the certificate on the other server from that file but when i look at the sys.certificates then i see in the other DB pvt_key_encrytpion_type is NA whie in the source DB it was MK ENCRYPTED_BY_MASTER_KEY

    And hence DECRYPTBYKEY is returning null in the destination database.

    I already backed up and copied master key from source and also used below on the destination DB


    open master key decryption by password = 'a$aDsdfsdffsdfdfddfs$$$na'
    alter master key add encryption by service master key
    close master key

    my issue is how to create certificate and symmetric key on destination so that they are similar to the one in the source database and hopefully DECRYPTBYKEY will return the correct value.

    Any help is much appreciated.

    Thank you.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Try this.

    Alter the certificate to add encryption by password
    backup the certificate to file (remember to use the decryption clause)
    create the certificate on the destination server
    reset the encryption on the original certificate to what it was before (presumably encrypted by master key)

  3. #3
    Join Date
    Jul 2010
    Posts
    4
    Could u pls describe the step with an example...esp the last step

  4. #4
    Join Date
    Jul 2010
    Posts
    4
    I was able to perform the three steps but not sure how to do the last step...also how to recreat the symmetric key from source to destintion

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Looks like in 2005 there is no way to "reset the encryption" or even drop a method of encryption. Just make the password very complex for safety, then.

  6. #6
    Join Date
    Jul 2010
    Posts
    4
    I am using 2008

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •