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:

           [DECRYPTBYCERT(C.certificate_id, KE.crypt_property)] = DECRYPTBYCERT(C.certificate_id, KE.crypt_property),
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

    ALGORITHM = AES_256,
    KEY_SOURCE = 'password to derive key',
    IDENTITY_VALUE = 'password to derive guid'

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):

WITH SUBJECT = 'TestCert';

    ALGORITHM = AES_256,
    KEY_SOURCE = 'password to derive key',
    IDENTITY_VALUE = 'password to derive guid'

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';


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


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))

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;





  • Great would be to avoid the problem of feedback.azure.com/forums/908035-sql-server/suggestions/… as well – TheConstructor Jun 18 '18 at 10:14
  • 1
    Given that you can extract the symmetric key details, you should be able to take that and implement a function (probably outside of SQL Server) that implements the 256 bit AES algorithm with that specific key. You could then compile it back into SQL Server as a CLR function to perform the decryption, and at least rotate the data to being encrypted by a new, known symmetric key. Sebastian Meine may be able to help you with more specifics on how to implement that. – mathewb Jun 18 '18 at 17:46
  • @mathewb CLR function is not what I currently aim for. Ideally I can just create a database and create the key there, so that I can extract the relevant parts from the other database, without the need to start out on a full-backup. – TheConstructor Jun 19 '18 at 11:44

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.

| improve this answer | |
  • I can decrypt the data, but I want to recreate the same key in another database. Also the query stated in the question uses the certificate (where I need to have access to the private key) to give me the real AES_256 key I "just" look for a way to reuse it. – TheConstructor Jun 19 '18 at 9:18
  • As shown by the C# implementation I do get indeed the (private) symmetric key. As such the first part of this answer is invalid. – TheConstructor Jun 19 '18 at 12:47
  • Symmetric keys don't have public and private keys. There is only one key and its encrypted in the system tables. There are several methods you can use to place the same symmetric key on a different database or server. One is to know, in advance, and use the key_source and identity_value parameters and use those on the destination. the other method would be to backup the original database and database master key separately and restore them both on the destination server. – Michael Keleher Jun 19 '18 at 12:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.