Unanswered: Sql Server Certificate and symmetric key
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.
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)