recreate symmetric key on different server database

_{Friday, October 19, 2012 4:06 PM}

I have an existing database on one machine for which

the database master key for which I know the password, certificate encrypted by master key (pvt_key_encryption_type = MK), symmetric key preexistent encrypted by the certificate.

I need to recreate the symmetric key on the new server :

steps: restore the db master key, then recreate same certificate(since no private key), then recreate the symmetric key based on the previously created certificate. However in the symmetric key does not have the  Key_SOURCE and IDENTITY_VALUE since were created before. can I alter the  Key_SOURCE and IDENTITY_VALUE on the source database without issue on the already encrypted data?

What are the recommended steps if no Key_SOURCE and IDENTITY_VALUE known from the source since the encryption on the target does not match without those 2 matching?

_{Thursday, October 25, 2012 8:33 AM ✅Answered}

Hi ServerH,

Because keys cannot be individually backed up and restored, there is no direct way of moving a key from one database to another. However, by specifying the same values for the ALGORITHM, KEY_SOURCE, and IDENTITY_VALUE clauses of CREATE SYMMETRIC KEY, the same key can be generated on different databases.

The KEY_SOURCE is the most important clause: the passphrase specified here is used to determine the key bits, so the phrase should be protected as carefully as the key itself or the data protected by it are protected. By specifying the same KEY_SOURCE, you are guaranteed to obtain the same key, assuming of course that you specified the same encryption algorithm. However, this is not sufficient to allow us to decrypt data encrypted by the key in another database - we also need for this key to be identified by the system as the same key - that is, the key needs to have the same identifier, because this identifier is appended to the encrypted data and is used to determine the key that should be used for decryption.

If we want regenerate the same symmetric key in two different databases, KEY_SOURCE, and IDENTITY_VALUE is necessary.

How to regenerate the same symmetric key in two different databases: http://blogs.msdn.com/b/lcris/archive/2006/07/06/658364.aspx.

Thanks.

Maggie Luo

TechNet Community Support

_{Monday, October 22, 2012 10:50 AM}

Hi Server H,

You can do this by backing up and restoring the symmetric key from one server to another. I suggest you to through following link to understand this process:

http://basitaalishan.com/2012/07/19/using-cell-level-encryption-in-sql-server/

http://www.sqlservercentral.com/blogs/steve_jones/2009/05/04/backing-up-asymmetric-keys/

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com

Please remember to click

"Mark as Answer" on the post that helps you, and to click

"Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

_{Tuesday, October 23, 2012 3:24 PM}

Hi Basit,

the first link shows generic info regarding column level encryption. and the second refers to asymmetric key backup

As I know there is no backup and restore of symmetric key

Steps that I followed;
1. source server:

backup master key to file = 'path\filename.bak' encryption by password 'password'

check:

select key_id, crypt_type,crypt-type_desc from sys.sys_encryption

101 ESKM ENCRYPTION BY MASTER KEY

1O1 ESKP ENCRYPTION BY PASSWORD

2. target server where I need to see the data

a.  restore master key from file =  'path\filename.bak' encryption by password 'password' --to add the password for the master key

b. alter master key add encryption by service master key -- to add the service master key encryption for database  master key

c. create certificate from file

d. open master key and create symmetric key encryption by certificate fails since the certificate is does not have a private key after restore

(select pvt_key_encryption_desc from sys.certificates  

NO_PRIVATE_KEY)

how to alter the certificate to use the database master key for the private key. I thought if no password specified the private key gets automatically encryptied with the db master key