SQL Server Encryption – How to Restore Symmetric Key on New Database

encryptionsql serversql-server-2005sql-server-2008

I have an issue with symmetric key restoring on the another database.
I have table in the first(old) database with encrypted column.(The table contains encrypted data)
The key in the old database has been created with the next sql script:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TESTPSWD'
GO
CREATE CERTIFICATE [CertificateSecurity] WITH SUBJECT = 'Key Protection';
GO
CREATE SYMMETRIC KEY [EncryptKey] WITH
    ALGORITHM = AES_256 
    ENCRYPTION BY CERTIFICATE [CertificateSecurity]
GO

And now I have to move this table with all data to the new database.
I import table with data from the old database.
And there is a question: Is there any way to create the same encryption key in the new database without specifying key_source and the identity_value? (Because I don't have it)
I tried to create it with the same sql query as above, but decryption in this case returns NULL.

P.S. I know what I can decrypt data then recreate key(with all parammeters supplied) and encrypt data with a new key.But it would be great to find another solution.

Best Answer

There is no built-in solution in SQL Server. A symmetric key that was created without specifying the source and identity can never be scripted or copied.

That means, in your case you have to decrypt and re-encrypt on the fly while copying the data.

You could also create a new key in the current database (specifying source and identity) and decrypt and new-key-re-encrypt locally. Then you can move the encrypted data and recreate the key in the new location, as you have all the important seed data available for that new key.

Related Question