Encrypt in SQL Server and Decrypt in C#

Recently, I had a requirement to fetch the encrypted string passed from SQL Server using function EncryptByPassPhrase (more details about this function here). This encrypted string was passed from a totally different setup to my ASP.Net web application.

My ASP.Net web Application required the decoded text. To do this I had to create a stored procedure which accepted the pass phrase and encrypted string as input and returned the decrypted string. The encrypted string type was VARBINARY. The stored procedure was:

CREATE PROCEDURE [dbo].sp_DecryptText 
    (@PassPhrase NVARCHAR(128), 
    @EncryptedText VARBINARY(MAX),
    @DecryptedText NVARCHAR(MAX) OUTPUT)
AS
BEGIN
    SET @DecryptedText = DECRYPTBYPASSPHRASE(@PassPhrase, @EncryptedText);
    RETURN
END

So I wrote a C# code to connect to stored procedure passing all the values as:

command.Parameters.AddWithValue("@PassPhrase", PassPhrase);
command.Parameters.Add("@EncryptedText", SqlDbType.VarBinary, 8000).Value = Encoding.Unicode.GetBytes(encryptedString);
command.Parameters.Add("@DecryptedText", SqlDbType.NVarChar, -1).Direction = ParameterDirection.Output;

But this did not work as the encryptedString was already in hexadecimal format and converting it to bytes resulted in a totally different string.

To get the stored procedure working, I converted the encryptedString to hexadecimal before passing it to the stored procedure by writing a separate function ParseHexString as:

static byte[] ParseHexString(string value)
{
    if (string.IsNullOrEmpty(value)) return null;
    if (1 == (1 & value.Length)) throw new ArgumentException("Invalid length for a hex string.", "value");

    int startIndex = 0;
    int length = value.Length;
    char[] input = value.ToCharArray();
    if ('0' == input[0] && 'x' == input[1])
    {
        if (2 == length) return null;
        startIndex = 2;
        length -= 2;
    }

    Func<char, byte> charToWord = c =>
    {
        if ('0' <= c && c <= '9') return (byte)(c - '0');
        if ('A' <= c && c <= 'F') return (byte)(10 + c - 'A');
        if ('a' <= c && c <= 'f') return (byte)(10 + c - 'a');
        throw new ArgumentException("Invalid character for a hex string.", "value");
    };

    byte[] result = new byte[length >> 1];
    for (int index = 0, i = startIndex; index < result.Length; index++, i += 2)
    {
        byte w1 = charToWord(input[i]);
        byte w2 = charToWord(input[i + 1]);
        result[index] = (byte)((w1 << 4) + w2);
    }

    return result;
}

So my call to stored procedure changed to:

command.Parameters.AddWithValue("@PassPhrase", PassPhrase);
command.Parameters.Add("@EncryptedText", SqlDbType.VarBinary, 8000).Value = ParseHexString(encryptedString);
command.Parameters.Add("@DecryptedText", SqlDbType.NVarChar, -1).Direction = ParameterDirection.Output;

And the output was correct and in the right format!!