How to backup Symmetric Key in SQL Server?

Question

I use the next code to create SQL Encryption keys

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Pass>'
CREATE CERTIFICATE MyEncryptCert WITH SUBJECT = 'Descryption', EXPIRY_DATE = '2115-1-1'
CREATE SYMMETRIC KEY MySymmetricKey WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE MyEncryptCert

How I encrypt data

OPEN SYMMETRIC KEY MySymmetricKey DECRYPTION BY CERTIFICATE MyEncryptCert
SET @Result = ENCRYPTBYKEY(KEY_GUID('MySymmetricKey'), '<String to encrypt>')
CLOSE SYMMETRIC KEY MySymmetricKey

I am able to backup Database Master Key and Certificate.

BACKUP MASTER KEY TO FILE = 'c:\temp\key' ENCRYPTION BY PASSWORD = '<Pass>';
BACKUP CERTIFICATE MyEncryptCert TO FILE = 'c:\temp\cert' WITH PRIVATE KEY(ENCRYPTION BY PASSWORD='<Pass>', FILE='C:\temp\cert.pvk')

But I can not backup Symmetric Key. Without it I can not decrypt the encrypted data if I move the encrypted table to another Database.

Is there any solutions?

P.S. I tried the next code, but seems it is not safe to me, because if you know KEY_SOURCE and IDENTITY_VALUE you actually do not need original Database Master Key and Certificate to decrypt the data

CREATE SYMMETRIC KEY MySymmetricKey WITH KEY_SOURCE = '<Pass1>', ALGORITHM = AES_256, IDENTITY_VALUE = '<Pass2>' ENCRYPTION BY CERTIFICATE MyEncryptCert

Selfconfessed

Answer 1

If you need to have the ability to duplicate a symmetric key, you should provide KEY_SOURCE and IDENTITY_VALUE. Your assessment is correct in that by knowing those two values, you can re-create the key. Observe the following code that shows that I can create the same key twice as is evidence by my encrypting a value with the "first" key, dropping the key, re-generating it with the same KEY_SOURCE and IDENTITY_VALUE, and then decrypting the encrypted value.

CREATE SYMMETRIC KEY MySymmetricKey WITH 
    KEY_SOURCE = '<Pass1>', 
    ALGORITHM = AES_256, 
    IDENTITY_VALUE = '<Pass2>' 
    ENCRYPTION BY Password = 'foobar!23'

open symmetric key MySymmetricKey
    decryption by password = 'foobar!23';
declare @encrypted varbinary(max);
select @encrypted = ENCRYPTBYKEY(KEY_GUID('MySymmetricKey'), 'my secrets!');

close symmetric key MySymmetricKey;
drop symmetric key MySymmetricKey;

CREATE SYMMETRIC KEY MySymmetricKey WITH 
    KEY_SOURCE = '<Pass1>', 
    ALGORITHM = AES_256, 
    IDENTITY_VALUE = '<Pass2>' 
    ENCRYPTION BY Password = 'foobar!23'

open symmetric key MySymmetricKey
    decryption by password = 'foobar!23';

select cast(DECRYPTBYKEY(@encrypted) as varchar(max))
close symmetric key MySymmetricKey;
drop symmetric key MySymmetricKey;

Claustrophobia

Thank you for the answer. The conclusion is I can not backup Symmetric Key. – Selfconfessed

Not directly. But you can recreate the same key by providing the same key material at creation time. Which as good as a backup. – Claustrophobia

Warning to people copying code: ENCRYPTION BY Password in the CREATE SYMMETRIC KEY statement is not recommended because the key will be protected with 3DES, which is a weaker algorithm than what it's actually protecting (AES256). Use a certificate instead. learn.microsoft.com/en-us/sql/t-sql/statements/… – Subtrahend

Answer 2

You can't.

As noted in another answer, it's possible to recreate the same symmetric key using the same parameters, but this does not work between different versions of SQL Server.

Subtrahend