C# Decrypt bytes from SQL Server EncryptByPassPhrase?

Question

Following Replicate T-SQL DecryptByPassPhrase in C#, I am unable to get a simple encryption with MSSQL to descrypt in C#. The encrypted values in certain columns is necessary because the table is exported into Excel and Access on a regular basis so simple encryption is more than enough to "block" values without having to involve developers to (re)do views, etc.

In SQL Server 2012:

    select EncryptByPassPhrase( N'hello' , N'world'  ) 
-- returns 0x01000000AA959FFB3A8E4B06B734051437E198C8B72000A058ACE91D617123DA102287EB

In C#:

byte[] buf = System.Text.Encoding.UTF8.GetBytes( "0x010000003A95FA870ED699A5F90D33C2BF01491D9132F61BA162998E96F37117AF5DA0905D51EB6FB298EC88" );
// bytes emitted from the database
var cp = new TripleDESCryptoServiceProvider();
var m = new MemoryStream(buf);
cp.Key = System.Text.Encoding.UTF8.GetBytes( "hello" ); // throws
cp.IV = System.Text.Encoding.UTF8.GetBytes( "hello" ); // throws
CryptoStream cs = new CryptoStream( m , cp.CreateDecryptor( cp.Key , cp.IV ) , CryptoStreamMode.Read );
StreamReader reader = new StreamReader( cs );
string plainText = reader.ReadToEnd();

What should working C# code look like?

Thanks.

Abagail

did you figure it out ? – Shovelboard

Answer 1

SQL Server 2017 uses SHA256 hashing of password + AES-256 encryption

Older versions use SHA1 hashing of password + 3DES-128 encryption

IV size is the same as block size: AES = 128 bits, 3DES = 64 bits

Padding mode: PKCS #7 Cipher mode: CBC

Data encrypted by server 2017 starts with "0x02", older versions start with "0x01".

// Example decrypt:
// UInt32 - "magic" (0xbaadf00d): 0d f0 ad ba
// UInt16 - unknown (always zero): 00 00
// UInt16 - decrypted data length (16): 10 00
// byte[] - decrypted data: 4c 65 74 54 68 65 53 75 6e 53 68 69 6e 69 6e 67

DecryptCombined("0x02000000266AD4F387FA9474E825B013B0232E73A398A5F72B79BC90D63BD1E45AE3AA5518828D187125BECC285D55FA7CAFED61", "Radames");
DecryptCombined("0x010000007854E155CEE338D5E34808BA95367D506B97C63FB5114DD4CE687FE457C1B5D5", "banana");


void DecryptCombined(string FromSql, string Password)
{
    // Encode password as UTF16-LE
    byte[] passwordBytes = Encoding.Unicode.GetBytes(Password);

    // Remove leading "0x"
    FromSql = FromSql.Substring(2);

    int version = BitConverter.ToInt32(StringToByteArray(FromSql.Substring(0, 8)), 0);
    byte[] encrypted = null;

    HashAlgorithm hashAlgo = null;
    SymmetricAlgorithm cryptoAlgo = null;
    int keySize = (version == 1 ? 16 : 32);

    if (version == 1)
    {
        hashAlgo = SHA1.Create();
        cryptoAlgo = TripleDES.Create();
        cryptoAlgo.IV = StringToByteArray(FromSql.Substring(8, 16));
        encrypted = StringToByteArray(FromSql.Substring(24));
    }
    else if (version == 2)
    {
        hashAlgo = SHA256.Create();
        cryptoAlgo = Aes.Create();
        cryptoAlgo.IV = StringToByteArray(FromSql.Substring(8, 32));
        encrypted = StringToByteArray(FromSql.Substring(40));
    }
    else
    {
        throw new Exception("Unsupported encryption");
    }

    cryptoAlgo.Padding = PaddingMode.PKCS7;
    cryptoAlgo.Mode = CipherMode.CBC;

    hashAlgo.TransformFinalBlock(passwordBytes, 0, passwordBytes.Length);
    cryptoAlgo.Key = hashAlgo.Hash.Take(keySize).ToArray();

    byte[] decrypted = cryptoAlgo.CreateDecryptor().TransformFinalBlock(encrypted, 0, encrypted.Length);
    int decryptLength = BitConverter.ToInt16(decrypted, 6);
    UInt32 magic = BitConverter.ToUInt32(decrypted, 0);
    if (magic != 0xbaadf00d)
    {
        throw new Exception("Decrypt failed");
    }

    byte[] decryptedData = decrypted.Skip(8).ToArray();
    bool isUtf16 = (Array.IndexOf(decryptedData, (byte)0) != -1);
    string decryptText = (isUtf16 ? Encoding.Unicode.GetString(decryptedData) : Encoding.UTF8.GetString(decryptedData));

    Console.WriteLine("Result: {0}", decryptText);
}

// Method taken from https://mcmap.net/q/93055/-how-can-i-convert-a-hex-string-to-a-byte-array-duplicate?answertab=votes#tab-top
public static byte[] StringToByteArray(string hex)
{
    return Enumerable.Range(0, hex.Length)
                     .Where(x => x % 2 == 0)
                     .Select(x => Convert.ToByte(hex.Substring(x, 2), 16))
                     .ToArray();
}

Southard

Very good answer! Thank you! Could you please provide a C# function which has the same output as EncryptByPassPhrase SQL function? – Hob

following is the C# example that has the same output as EncryptByPassPhrase SQL function. github.com/persec10000/Test_Invoice2QrCodeWithCryptedLink – Biased

Answer 2

I believe the link you are following is suggesting a new way to encrypt and decrypt mimicking that of how the SQL EncryptByPassPhrase is made. So, you could only use the decrypt in C#, if you encrypted in C# as well.

Since, you already used EncryptByPassPhrase in SQL then I would suggest just using DecryptByPassPhrase in SQL before passing to C# code.

Example of your hello world encrypt and decrypt:

Declare @lEncryptedText VARBINARY(256) = (select ENCRYPTBYPASSPHRASE('hello','world'))
SELECT @lEncryptedText --Encrypted VALUE for world

SELECT CONVERT(VARCHAR(100),DECRYPTBYPASSPHRASE('hello',@lEncryptedText)) --Decrypted Value

Instar

Answer 3

Following is the perfect C# solution that has the same outputs as EncryptByPassPhrase and DecryptByPassPhrase in SQL functions.

Program.cs

using SQLServerCrypto;
static void Main()
{
            // EncryptByPassPhrase 
            var passphrase = "test1234";
            var encrypted = SQLServerCryptoMethod.EncryptByPassPhrase(@passphrase, "Hello world.");
            System.Console.WriteLine(encrypted.ToString().ToUpper());

            // DecryptByPassPhrase 
            var ciphertext = "0x0100000038C94F7223E0BA2F772B611857F9D45DAF781607CC77F4A856CF08CC2DB9DF14A0593259CB3A4A2BFEDB485C002CA04B6A98BEB1B47EB107";
            var password = "test1234";
            var decrypted = SQLServerCryptoMethod.DecryptByPassPhraseWithoutVerification(password, ciphertext);
            Console.WriteLine(decrypted);
}

SQLServerCryptoMethod.cs

using System;
using System.Linq;

namespace SQLServerCrypto
{
    public static class SQLServerCryptoMethod
    {
        // SQL Server: https://learn.microsoft.com/en-us/sql/t-sql/functions/encryptbypassphrase-transact-sql
        public static HexString EncryptByPassPhrase(string passphrase, string cleartext, int add_authenticator, string authenticator)
            => EncryptByPassPhrase(passphrase, cleartext, add_authenticator, authenticator, SQLServerCryptoVersion.V1);

        public static HexString EncryptByPassPhrase(string passphrase, string cleartext, SQLServerCryptoVersion sqlServerCryptoVersion)
             => EncryptByPassPhrase(passphrase, cleartext, 0, string.Empty, sqlServerCryptoVersion);

        public static HexString EncryptByPassPhrase(string passphrase, string cleartext)
             => EncryptByPassPhrase(passphrase, cleartext, 0, string.Empty, SQLServerCryptoVersion.V1);

        public static HexString EncryptByPassPhrase(string passphrase, string cleartext, int add_authenticator, string authenticator, SQLServerCryptoVersion sqlServerCryptoVersion)
        {
            var sqlServerCryptoAlgorithm = new SQLServerCryptoAlgorithm(sqlServerCryptoVersion);
            sqlServerCryptoAlgorithm.SetKeyFromPassPhrase(passphrase);

            byte[] header = new SQLServerCryptoHeader() {
                Version = sqlServerCryptoVersion,
                InitializationVector = sqlServerCryptoAlgorithm.Symmetric.IV
            };
            var sqlServerCryptoMessage = new SQLServerCryptoMessage()
            {
                AddAuthenticator = add_authenticator > 0,
                Authenticator = authenticator
            };
            sqlServerCryptoMessage.CreateFromClearText(cleartext);

            byte[] message = sqlServerCryptoMessage;
            
            var encryptedMessage = sqlServerCryptoAlgorithm.Symmetric
                .CreateEncryptor()
                .TransformFinalBlock(message, 0, message.Length);

            return new HexString(header.Concat(encryptedMessage).ToArray());
        }

        // SQL Server: https://learn.microsoft.com/en-us/sql/t-sql/functions/decryptbypassphrase-transact-sql
        public static string DecryptByPassPhrase(string passphrase, string ciphertext) 
            => DecryptByPassPhrase(passphrase, new HexString(ciphertext), 0, string.Empty, true);

        public static string DecryptByPassPhrase(string passphrase, string ciphertext, int add_authenticator, string authenticator)   
            => DecryptByPassPhrase(passphrase, new HexString(ciphertext), add_authenticator, authenticator, true);

        public static string DecryptByPassPhraseWithoutVerification(string passphrase, string ciphertext)
          => DecryptByPassPhrase(passphrase, new HexString(ciphertext), 0, string.Empty, false);

        public static string DecryptByPassPhrase(string passphrase, HexString ciphertext, int add_authenticator, string authenticator, bool verify)
        {
            byte[] ciphertextBytes = ciphertext.ToByteArray();
            var version = (SQLServerCryptoVersion)ciphertextBytes[0];

            var sqlServerCryptoAlgorithm = new SQLServerCryptoAlgorithm(version);
            sqlServerCryptoAlgorithm.SetKeyFromPassPhrase(passphrase);

            var versionAndReservedSize = 4;
            var ivSize = sqlServerCryptoAlgorithm.KeySize / 2;

            var header = new SQLServerCryptoHeader
            {
                Version = version,
                InitializationVector = ciphertextBytes.Skip(versionAndReservedSize).Take(ivSize).ToArray()
            };
            sqlServerCryptoAlgorithm.Symmetric.IV = header.InitializationVector;

            var encryptedMessage = ciphertextBytes.Skip(versionAndReservedSize+ivSize).ToArray();
                       
            var decryptedMessage = sqlServerCryptoAlgorithm.Symmetric
                .CreateDecryptor()
                .TransformFinalBlock(encryptedMessage, 0, encryptedMessage.Length);

            // Message
            var sqlServerCryptoMessage = new SQLServerCryptoMessage()
            {
                AddAuthenticator = add_authenticator > 0,
                Authenticator = authenticator
            };
            sqlServerCryptoMessage.CreateFromDecryptedMessage(decryptedMessage, verify);
            
            return ByteArray2String(sqlServerCryptoMessage.MessageBytes);
        }
              
        private static string ByteArray2String(byte[] array)
            => array.Aggregate(string.Empty, (a, b) => a + Convert.ToChar(b));
    }
}

SQLServerCryptoMessage.cs

using System;
using System.IO;
using System.Text;
using System.Linq;
using System.Security.Cryptography;

namespace SQLServerCrypto
{
    public class SQLServerCryptoMessage
    {
        private const uint MAGIC_NUMBER = 0xbaad_f00d;

        public uint MagicNumber { get; private set; }

        public ushort IntegrityBytesLength { get; private set; }

        public ushort PlainTextLength { get; private set; }

        public byte[] IntegrityBytes;

        public byte[] MessageBytes;

        public bool AddAuthenticator = false;

        private string _authenticator;

        public string Authenticator
        {
            get => _authenticator;

            set =>
                _authenticator = value.Length > 128 ?
                    throw new ArgumentOutOfRangeException("The size of the authenticator string should not exceed 128 bytes.")
                    : value;
        }
        
        public SQLServerCryptoMessage() =>  MagicNumber = MAGIC_NUMBER;
        
        public void CreateFromClearText(string cleartext)
        {
            MessageBytes = Encoding.ASCII.GetBytes(cleartext);

            if (MessageBytes.Length > 8000)
                throw new ArgumentOutOfRangeException("The size of the cleartext string should not exceed 8000 bytes.");

            MagicNumber = MAGIC_NUMBER;
            IntegrityBytesLength = 0;

            PlainTextLength = (ushort)MessageBytes.Length;

            if (AddAuthenticator)
            {
                var integrityMessage = MessageBytes.Concat(Encoding.ASCII.GetBytes(Authenticator)).ToArray();
                IntegrityBytes = SHA1.Create().ComputeHash(integrityMessage);
                IntegrityBytesLength = (ushort)IntegrityBytes.Length;
            }
        }

        public void CreateFromDecryptedMessage(byte[] decryptedMessage, bool verify = true)
        {
            MagicNumber = BitConverter.ToUInt32(decryptedMessage, 0);
            IntegrityBytesLength = BitConverter.ToUInt16(decryptedMessage, 4);
            PlainTextLength = BitConverter.ToUInt16(decryptedMessage, 6);

            var messageWithoutHeader = decryptedMessage.Skip(8);

            if (IntegrityBytesLength > 0 || IntegrityBytesLength < 0xffff)
                IntegrityBytes = messageWithoutHeader.Take(IntegrityBytesLength).ToArray();

            if (IntegrityBytesLength != 0xffff)
                MessageBytes = messageWithoutHeader.Skip(IntegrityBytesLength).ToArray();
            else
                MessageBytes = messageWithoutHeader.ToArray();

            if (verify)
                VerifyMessage();
        }
        
        private void VerifyMessage()
        {
            if (MagicNumber != MAGIC_NUMBER)
                throw new Exception("Message integrity error. Magic numbers are different.");

            var integrityMessage = MessageBytes.Concat(Encoding.ASCII.GetBytes(Authenticator)).ToArray();
            var hash = SHA1.Create().ComputeHash(integrityMessage);

            if (IntegrityBytes.Length > 0 && !hash.SequenceEqual(IntegrityBytes))
                throw new Exception("Message integrity error. Invalid authenticator.");
            
            if (PlainTextLength != MessageBytes.Length)
                throw new Exception("Message integrity error. Invalid message length.");
        }

        public static implicit operator byte[](SQLServerCryptoMessage sqlServerCryptoMessage) => sqlServerCryptoMessage.ToByteArray();
        
        public byte[] ToByteArray()
        {
            byte[] result;
            using (var memoryStream = new MemoryStream())
            {
                using (var binaryWriter = new BinaryWriter(memoryStream))
                {
                    binaryWriter.Write(MagicNumber);
                    binaryWriter.Write(IntegrityBytesLength);
                    binaryWriter.Write(PlainTextLength);

                    if (IntegrityBytes != null)
                        binaryWriter.Write(IntegrityBytes);

                    if (MessageBytes != null)
                        binaryWriter.Write(MessageBytes);
                }
                result = memoryStream.ToArray();
            }
            return result;
        }
    }
}

SQLServerCryptoAlghorithm.cs

using System;
using System.Text;
using System.Linq;
using System.Security.Cryptography;

namespace SQLServerCrypto
{
    public class SQLServerCryptoAlgorithm
    {
        public readonly SQLServerCryptoVersion Version;

        public readonly HashAlgorithm Hash;

        public readonly SymmetricAlgorithm Symmetric;

        public readonly int KeySize;

        public SQLServerCryptoAlgorithm(SQLServerCryptoVersion sqlCryptoVersion)
        {
            Version = sqlCryptoVersion;
            switch (Version)
            {
                case SQLServerCryptoVersion.V1:
                    Hash = SHA1.Create();
                    Symmetric = TripleDES.Create();
                    KeySize = 16;
                    break;
                case SQLServerCryptoVersion.V2:
                    Hash = SHA256.Create();
                    Symmetric = Aes.Create();
                    KeySize = 32;
                    break;
                default:
                    throw new Exception("Unsupported SQLServerCryptoVersion");
            }
            Symmetric.Padding = PaddingMode.PKCS7;
            Symmetric.Mode = CipherMode.CBC;
        }

        public void SetKeyFromPassPhrase(string passphrase)
            => Symmetric.Key = Hash
                .ComputeHash(Encoding.Unicode.GetBytes(passphrase))
                .Take(KeySize)
                .ToArray();
    }
}

SQLServerCryptoHeader.cs

using System.Collections.Generic;

namespace SQLServerCrypto
{
    public class SQLServerCryptoHeader
    {
        public SQLServerCryptoVersion Version = SQLServerCryptoVersion.V1;
        public byte[] Reserved = { 0, 0, 0 };
        public byte[] InitializationVector = { };

        public SQLServerCryptoHeader(SQLServerCryptoVersion sqlServerCryptoVersion = SQLServerCryptoVersion.V1)
            => Version = sqlServerCryptoVersion;

        public static implicit operator byte[] (SQLServerCryptoHeader sqlServerCryptoHeader) => sqlServerCryptoHeader.ToByteArray();

        public byte[] ToByteArray()
        {
            var result = new List<byte>();
            result.Add((byte)Version);
            result.AddRange(Reserved);
            result.AddRange(InitializationVector);
            return result.ToArray();
        }
    }
}

SQLServerCryptoVersion.cs

namespace SQLServerCrypto
{
    public enum SQLServerCryptoVersion
    {
        ///<summary>
        /// TripleDES/SHA1
        /// </summary> 
        V1 = 0x01,
        /// <summary>
        /// AES256/SHA256
        /// </summary>
        V2 = 0x02
    }
}

HexString.cs

using System;
using System.Linq;
using System.Text.RegularExpressions;

namespace SQLServerCrypto
{
    public sealed class HexString
    {
        private readonly byte[] _byteArray;

        private readonly Regex RegExValidation = new Regex("^[0-9a-fA-F]+$", RegexOptions.Compiled);
        private const string PREFIX = "0x";

        public HexString(string hexString)
        {
            if (string.IsNullOrEmpty(hexString))
                throw new ArgumentException("Input string is null or empty.");

            hexString = RemovePrefix(hexString);

            if (hexString.Length % 2 != 0)
                throw new ArgumentException("Invalid number of hexcharacters.");

            if (!RegExValidation.IsMatch(hexString))
                throw new ArgumentException("Input string does not contain hexadecimal characters.");

            _byteArray = HexStringToByteArray(hexString);
        }

        public HexString(byte[] byteArray)
        {
            if (byteArray == null)
                throw new ArgumentException("Input array is null.");

            if (byteArray.Length == 0)
                throw new ArgumentException("Input array is empty.");

            _byteArray = byteArray;
        }

        public string ValueWithoutPrefix => ByteArrayToHexString(_byteArray);
        
        public string ValueWithPrefix => PREFIX + ValueWithoutPrefix;
        
        public override string ToString() => ValueWithPrefix;

        public byte[] ToByteArray() => _byteArray;

        public static implicit operator string(HexString hexString) => hexString.ToString();

        public static implicit operator byte[](HexString hexString) => hexString._byteArray;

        private static string RemovePrefix(string input) => input.StartsWith(PREFIX) ? input.Remove(0, 2) : input;
        
        // https://mcmap.net/q/93055/-how-can-i-convert-a-hex-string-to-a-byte-array-duplicate#321404
        // Looks nice but could be faster.
        private static byte[] HexStringToByteArray(string hexString) => Enumerable.Range(0, hexString.Length) 
               .Where(x => x % 2 == 0)
               .Select(x => Convert.ToByte(hexString.Substring(x, 2), 16))
               .ToArray();

        private static string ByteArrayToHexString(byte[] byteArray) =>
            BitConverter.ToString(byteArray).Replace("-", string.Empty).ToLower();
    }
}

Please refer to the following link for the full source project. https://github.com/persec10000/Test_Invoice2QrCodeWithCryptedLink

Biased