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.