How can I duplicate a SQL Server symmetric key?

Question

We have a server with a database that has a symmetric key (Database -> Security -> Symmetric Key). We have a backup duplicate databases that we are using as a test databases, but we don't have this key in there.

How can I duplicate this symmetric key (or make a new one exactly like the old) and put it in the existing databases? It has to have the same value and key-name as the other one.

This is on SQL Server 2008.

Answer 1

When you create your symmetric key in the first place, ensure you are using the KEY_SOURCE, IDENTITY_VALUE and ALGORITHM parameters.

If you haven't already, create the database master key and certificate to protect your symmetric key.

-- Create Database Master Key 
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'Your Database Master Key Password here' 
GO

-- Create Encryption Certificate 
CREATE CERTIFICATE MyCertificateName
WITH SUBJECT = 'Your Certificate Description Here' 
GO

-- Create Symmetric Key
CREATE SYMMETRIC KEY MyKeyName WITH
IDENTITY_VALUE = 'Enter a key description',
ALGORITHM = AES_256, 
KEY_SOURCE = 'Enter a key phrase here (keep very secret)'
ENCRYPTION BY CERTIFICATE MyCertificateName;

• The IDENTITY_VALUE parameter is used to create the guid in sys.symmetric_keys table, which needs to be the same in both databases to work.

• The KEY_SOURCE parameter is used to create the actual key itself, so make sure this is exactly the same and well protected.

• The ALGORITHM is of course which algorithm sql server uses to encrypt and decrypt the data, which must be the same to work.

You should be able to run the above script on both databases (replaced with your own values of course) and it will successfully decrypt data encrypted in the other database.

If your existing key wasn't created this way, you are going to have to decrypt everything with your old key and re-encrypt it back with the new one.

A couple of good sources on key creation can be found here:

• Introduction to Symmetric Key Encryption
• Laurentiu Cristofor's Blog