Recreate symmetric key of unknown CREATE SYMMETRIC KEY

Question

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

Answer 1

If anyone can simply write a sql statement to extract the passwords or arguments or recreate the same key, it would defeat the encryption. An AES_256 key has 2^256 possibilities. The encryption in SQL Server is based on a hierarchy of keys protecting each other. And because the symmetric keys are protected by a certificate or asymmetric key and you can't get to the private key, which itself is protected by other keys, you won't be able to break the system unless you can break the data protection API on the host. If you are trying to solve a problem where you've lost control of the encrypted data and need to recover, then it is possible to do if you know only the Key_source argument used to create the key originally because the first 16 bytes of data encrypted with a symmetric key is the guid and that can be replaced with the guid of the new key prior to decryption.