Vryjdfkk

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

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.