Encrypt from C# just like SQL Server EncryptByPassPhrase?

Question

After following the answer of this question, I was able to recreate the DecryptByPassPhrase function of SQL. Now I am trying to do the EncryptByPassPhrase function.

We have different SQL version on different server which cannot be upgrade to the same version at the same time, so we want to create our c# function (with SQL CLR).

I need to encode with SHA1 (like a SQL 2012) since the SQL on the other server might not be upgraded to 2017 yet

Here is my c#, the function compile, but I can't decrypt it with my other c# function (which is exactly the function of the answer of the other question)

        int keySize = 16;

        var cryptoAlgo = TripleDES.Create();
        cryptoAlgo.Padding = PaddingMode.PKCS7;
        cryptoAlgo.Mode = CipherMode.CBC;

        cryptoAlgo.GenerateIV(); //cryptoAlgo.IV = StringToByteArray(value.Substring(8, 16));
        //cryptoAlgo.IV = StringToByteArray("7854E155CEE338D5");
        var valueInByte = Encoding.Unicode.GetBytes(value); //UTF8Encoding.UTF8.GetBytes(value); //encrypted = StringToByteArray(value.Substring(24));


        byte[] passwordBytes = Encoding.Unicode.GetBytes(password);
        var hashAlgo = SHA1.Create();
        hashAlgo.TransformFinalBlock(passwordBytes, 0, passwordBytes.Length);
        cryptoAlgo.Key = hashAlgo.Hash.Take(keySize).ToArray();

        byte[] encrypted = cryptoAlgo.CreateEncryptor().TransformFinalBlock(valueInByte, 0, valueInByte.Length);

        //byte[] encryptedData = encrypted.Skip(8).ToArray();
        //bool isUtf16 = (Array.IndexOf(encryptedData, (byte)0) != -1);
        //string encryptedText = (isUtf16 ? Encoding.Unicode.GetString(encryptedData) : Encoding.UTF8.GetString(encryptedData));

        return new SqlString(encryptedText);
        //return new SqlString(encryptedText);
        //return Convert.ToBase64String(encrypted, 0, encrypted.Length);
        //return Convert.ToBase64String(encryptedData, 0, encryptedData.Length);
        //return "0x01000000" + Convert.ToBase64String(cryptoAlgo.IV, 0, cryptoAlgo.IV.Length) + Convert.ToBase64String(encrypted, 0, encrypted.Length);
        //return "0x01000000" + Convert.ToBase64String(encryptedData, 0, encryptedData.Length);

there a lot of different return that i've tried (they are in comment). I noticed that SQL return "0x01000000" and I've guess that the next 16 character are the IV so i've tried to add them, with no luck

Strongly consider taking this opportunity to move away from the SQL-based encryption/decryption functions altogether (i.e. performing a one-time decryption pass on existing data using SQL Server itself, then re-encrypting and decrypting strictly with client code). This frees you from being tied to the undocumented internals of these functions for future versions -- they are not really suitable if data ever conceivably needs to be migrated to/from SQL Server, or even within with different versions (as you've discovered). — Jeroen Mostert, Jan 27 '20 at 16:03
I agree, but since I can't control which side will be updated first, nor is the data currently saved encrypted in the database, I can't do it. When a customer make a purchase, we encrypt some of his data while it travel on the internet to another one of our system — David, Jan 28 '20 at 22:00

Veli Gebrev · Answer 1 · 2020-06-09T11:29:04.727

Had a similar requirement, note that simply encrypting the data is not enough, your final byte array should be structured as follows:

First four bytes are the version: 2 0 0 0
Next 16 bytes are the initialization vector: cryptoAlgo.IV
The remainder is the encrypted data, structured (prior to encrypting) as follows:
- First four bytes are the Magic value: 13, 240, 173, 186 (0xbaadf00d)
- Next 2 bytes are 0
- Next 2 bytes are the length of the value bytes
- The remainder is the value bytes

Code:

public static class SqlCryptographyExtensions
{
    const uint MagicPrefix = 0xbaadf00d;

    public static byte[] EncryptByPassPhrase(this string value, string passPhrase)
    {
        var keyBytes = Encoding.Unicode.GetBytes(passPhrase);
        // Depending on whether you're working with NVARCHAR/VARCHAR on SQL Server, use Unicode/UTF encoding here
        var valueBytes = Encoding.UTF8.GetBytes(value); 
        var payload = new List<byte>();
        payload.AddRange(BitConverter.GetBytes(MagicPrefix));
        payload.AddRange(BitConverter.GetBytes((UInt16)0));
        payload.AddRange(BitConverter.GetBytes((UInt16)valueBytes.Length));
        payload.AddRange(valueBytes);
        var payloadBytes = payload.ToArray();

        HashAlgorithm hash = SHA256.Create();
        SymmetricAlgorithm encryption = Aes.Create();
        encryption.GenerateIV();
        encryption.Padding = PaddingMode.PKCS7;
        encryption.Mode = CipherMode.CBC;
        hash.TransformFinalBlock(keyBytes, 0, keyBytes.Length);
        encryption.Key = hash.Hash.Take(32).ToArray();

        byte[] encryptedPayload = encryption.CreateEncryptor().TransformFinalBlock(payloadBytes, 0, payloadBytes.Length);

        byte[] version = new byte[] { 2, 0, 0, 0 };

        var encryptedBytes = new List<byte>();
        encryptedBytes.AddRange(version);
        encryptedBytes.AddRange(encryption.IV);
        encryptedBytes.AddRange(encryptedPayload);

        return encryptedBytes.ToArray();
    }
}

Encrypt from C# just like SQL Server EncryptByPassPhrase?

1 Answers1