Sql-server – Recreate symmetric key of unknown CREATE SYMMETRIC KEY

I found that for each encryption of a symmetric key an entry in sys.key_encryptions is present and at least for ENCRYPTION BY CERTIFICATE-type entries I can use the certificate of the specified thumbprint to decrypt the crypt_property:

SELECT
           SK.name,
           SK.key_length,
           SK.algorithm_desc,
           [DECRYPTBYCERT(C.certificate_id, KE.crypt_property)] = DECRYPTBYCERT(C.certificate_id, KE.crypt_property),
           SK.key_guid
FROM       sys.symmetric_keys SK
INNER JOIN sys.key_encryptions KE ON KE.key_id = SK.symmetric_key_id
INNER JOIN sys.certificates C ON C.thumbprint = KE.thumbprint
WHERE      KE.crypt_type = 'EPUC'

Now I wonder if I can recreate the symmetric key from this information inside another database. For CREATE CERTIFICATE you can use FROM BINARY = 0x... WITH PRIVATE KEY ( BINARY = 0x...), but it seems the closest I get is

CREATE SYMMETRIC KEY TestKey
WITH
    ALGORITHM = AES_256,
    KEY_SOURCE = 'password to derive key',
    IDENTITY_VALUE = 'password to derive guid'
ENCRYPTION BY CERTIFICATE TestCert;

Can anybody help me write a working statement to recreate the symmetric key?

I guess I will either need a way to generate a password that derives the same key (and ideally the same guid) again or find another option to specify the 'plaintext' symmetric key (EKM?). The original KEY_SOURCE– and IDENTITY_VALUE-properties are unknown to me.

Appendix: Demonstration, that key is valid

Prerequisites on SQL Server (I used SQL Server 2016):

CREATE CERTIFICATE TestCert
WITH SUBJECT = 'TestCert';

CREATE SYMMETRIC KEY TestKey
WITH
    ALGORITHM = AES_256,
    KEY_SOURCE = 'password to derive key',
    IDENTITY_VALUE = 'password to derive guid'
ENCRYPTION BY CERTIFICATE TestCert;

Use query from above to retrieve AES-Key:

|| name || key_length || algorithm_desc || DECRYPTBYCERT(C.certificate_id, KE.crypt_property) || key_guid ||
| TestKey | 256 | AES_256 | 0xAF8C9D22CE9B6BBE5E4207608A2D4D9494B8532AD2F358C382F02BB3303A36E6 | 01DE2200-EF6C-3C1A-0F3A-889881EF77E7 |

Encrypt something

DECLARE @KeyGuid uniqueidentifier = '01DE2200-EF6C-3C1A-0F3A-889881EF77E7';

OPEN SYMMETRIC KEY TestKey
DECRYPTION BY CERTIFICATE [TestCert];

SELECT ENCRYPTBYKEY(@KeyGuid, N'blubb'); -- yields 0x0022DE016CEF1A3C0F3A889881EF77E70100000091B6D30FAB2FFBC7FFEAE32D3A52A6F8AEA2750CEBEBE6BCE33782AEC52A4E31B04FFBCCD06470242E585AA4261BC3B1

CLOSE SYMMETRIC KEY TestKey;

Then this LINQPad-query (C# Program) can be used to decrypt the encrypted values again (Strictly proof-of-concept quality!):

void Main()
{
    AesManaged myAes = new AesManaged();

    // Override the cipher mode and padding. Key and IV are passed in below
    myAes.Mode = CipherMode.CBC;
    myAes.Padding = PaddingMode.None;

    var Key = StringToByteArray("0xAF8C9D22CE9B6BBE5E4207608A2D4D9494B8532AD2F358C382F02BB3303A36E6");  // Byte array representing the key

    var encrypted = StringToByteArray("0x0022DE016CEF1A3C0F3A889881EF77E70100000091B6D30FAB2FFBC7FFEAE32D3A52A6F8AEA2750CEBEBE6BCE33782AEC52A4E31B04FFBCCD06470242E585AA4261BC3B1");

    // Following fields are according to https://blogs.msdn.microsoft.com/sqlsecurity/2009/03/30/sql-server-encryptbykey-cryptographic-message-description/
    var KeyGUID = new Guid(encrypted.Slice(0, 16)).Dump("Guid");
    var EncryptionHeader = encrypted.Slice(16, 4);
    //var Headerversion = EncryptionHeader.Slice(0, 1); // Could check for 0x01
    //var ReservedBytes = EncryptionHeader.Slice(1, 3); // currently all 0x00
    var EncryptedMessage = encrypted.Slice(20);
    var InitializationVector = EncryptedMessage.Slice(0, myAes.BlockSize / 8);
    var EncryptedData = EncryptedMessage.Slice(myAes.BlockSize / 8);

    // We have everything to perform the decryption
    var decryptor = myAes.CreateDecryptor(Key, InitializationVector);
    var InnerMessage = decryptor.TransformFinalBlock(EncryptedData, 0, EncryptedData.Length);

    // Encrypted result contaisn more fields
    var InnerMessageHeader = InnerMessage.Slice(0, 8);
    //var MagicNumber = InnerMessageHeader.Slice(0, 4); // Could check for 3131961357
    var IntegrityBytesLength = BitConverter.ToUInt16(InnerMessageHeader.Slice(4, 2), 0).Dump("IntegrityBytesLength");
    var PlaintextLength = BitConverter.ToUInt16(InnerMessageHeader.Slice(6, 2), 0).Dump("PlaintextLength");
    //var IntegrityBytes = InnerMessage.Slice(8, IntegrityBytesLength).Dump("IntegrityBytes"); // Not used without "authenticator" in ENCRYPTBYKEY

    // Our Plaintext (finally!) - Encoding needs to be changed if input to ENCRYPTBYKEY is not nvarchar
    var Plaintext = new string(Encoding.Unicode.GetChars(InnerMessage.Slice(8 + IntegrityBytesLength, PlaintextLength))).Dump("Plaintext");

}

// Define other methods and classes here
public static byte[] StringToByteArray(string hex)
{
    return Enumerable.Range(2, hex.Length - 2)
                     .Where(x => x % 2 == 0)
                     .Select(x => Convert.ToByte(hex.Substring(x, 2), 16))
                     .ToArray();
}

static class Extensions
{
    public static byte[] Slice(this byte[] input, long startIndex, long? length = null)
    {
        var result = new byte[length ?? (input.LongLength - startIndex)];
        Array.Copy(input, startIndex, result, 0, result.LongLength);
        return result;
    }
}

Output:

Guid
01de2200-ef6c-3c1a-0f3a-889881ef77e7

IntegrityBytesLength
0

PlaintextLength
10

Plaintext
blubb