DES Decryption in SQL CLR Scalar UDF not working

We have a legacy application that uses SQL Server as its back-end. As part of some security concerns, it encrypts some fields collected from the user using (single) DES with a key & IV that is hard-coded into the app code, then Base64 encodes the encrypted bytes, and finally stores that string in a varchar column in the DB. Woefully insecure at this point (and probably when it was first coded), along with a questionable design / implementation, but it is what it is. My task is to implement a CLR User Defined Scalar Function in SQL Server that can decrypt this type of data.

As a proof of concept, I created the following short console app to make sure I understand the DES decryption process in C#:

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

class My_Decrypt
{
    static void Main(string[] args)
    {
        DES des = new DESCryptoServiceProvider();
        byte[] IV = BitConverter.GetBytes(0xFECAEFBEEDFECEFA);
        byte[] Key = Encoding.ASCII.GetBytes("password");

        foreach (string cipherText in args)
        {
            byte[] cipherBytes = Convert.FromBase64String(cipherText);
            MemoryStream es = new MemoryStream(cipherBytes);
            CryptoStream cs = new CryptoStream(
                es,
                des.CreateDecryptor(Key, IV),
                CryptoStreamMode.Read
            );
            byte[] plainBytes = new byte[cipherBytes.Length];
            cs.Read(plainBytes, 0, plainBytes.Length);
            string plainText = Encoding.ASCII.GetString(plainBytes);
            Console.WriteLine(
                "'{0}' == '{1}'\nusing key = '{2}', IV = '{3}'\ndecrypts to '{4}' == '{5}'.\n",
                cipherText,
                BitConverter.ToString(cipherBytes),
                BitConverter.ToString(Key),
                BitConverter.ToString(IV),
                BitConverter.ToString(plainBytes),
                plainText
            );
        }
    }
}

After compiling, I am able to run the following:

C:\>My_Decrypt.exe KDdSnfYYnMQawhwuaWo2WA==
'KDdSnfYYnMQawhwuaWo2WA==' == '28-37-52-9D-F6-18-9C-C4-1A-C2-1C-2E-69-6A-36-58'
using key = '70-61-73-73-77-6F-72-64', IV = 'FA-CE-FE-ED-BE-EF-CA-FE'
decrypts to '73-65-63-72-65-74-20-64-61-74-61-00-00-00-00-00' == 'secret data     '.

Which looks correct and I verified using openssl.

So, having established that, I next tried to use the same code in a CLR scalar UDF as follows:

    [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true)]
    public static SqlString DES_Decrypt( SqlString CipherText, SqlBinary DES_Key, SqlBinary DES_IV )
    {
        if (CipherText.IsNull || DES_Key.IsNull || DES_IV.IsNull)
            return SqlString.Null;
        string cipherText = CipherText.ToString();
        byte[] cipherBytes = Convert.FromBase64String(cipherText);
        MemoryStream es = new MemoryStream(cipherBytes);
        DES des = new DESCryptoServiceProvider();
        byte[] IV = (byte[]) DES_IV;
        byte[] Key = (byte[]) DES_Key;
        CryptoStream cs = new CryptoStream(
            es, des.CreateEncryptor(Key, IV), CryptoStreamMode.Read );
        byte[] plainBytes = new byte[cipherBytes.Length];
        cs.Read(plainBytes, 0, plainBytes.Length);
        cs.Close();
        string plainText = new ASCIIEncoding().GetString(plainBytes);
        return new SqlString(plainText);
    }

However, after compiling, loading the assembly into MSSQL, creating the function, and trying to execute it -- I get garbage for the output. So, after many attempts to get this work (which included creating the POC app above), I replaced the return in the last line with the following:

        throw new ArgumentException(String.Format(
            "\n'{0}' == '{1}'\nusing key = '{2}', IV = '{3}'\ndecrypts to '{4}' == '{5}'.",
            cipherText,
            BitConverter.ToString(cipherBytes),
            BitConverter.ToString(Key),
            BitConverter.ToString(IV),
            BitConverter.ToString(plainBytes),
            plainText
        ));

Now, when I run in MSSQL the query SELECT dbo.DES_Decrypt(N'KDdSnfYYnMQawhwuaWo2WA==', CAST('password' AS binary(8)), 0xFACEFEEDBEEFCAFE);, I get the exception error message:

A .NET Framework error occurred during execution of user-defined routine or aggregate "DES_Decrypt": 
System.ArgumentException: 
'KDdSnfYYnMQawhwuaWo2WA==' == '28-37-52-9D-F6-18-9C-C4-1A-C2-1C-2E-69-6A-36-58'
using key = '70-61-73-73-77-6F-72-64', IV = 'FA-CE-FE-ED-BE-EF-CA-FE'
decrypts to '47-F7-06-E4-88-C4-50-5B-E5-4D-CC-C9-32-C7-8F-BB' == 'G????P[?M??2???'.
System.ArgumentException: 
   at DES_Decryptor.Decrypt(SqlString CipherText, SqlBinary DES_Key, SqlBinary DES_IV)
.

The input processing looks good: the base64 decoded bytes match, as do the binary versions of the Key & IV that are passed in. So, it seems to me that something is going wrong in the C# DES decryption routine when it's called from the CLR scalar UDF, but I'm frustrated and all out of ideas. Any clues as to what could be going wrong here?

Solution

After reproducing your results in both places and changing several things yet not changing the output, I went over both sets of code making sure they were the same and found the issue:

In the call to new CryptoStream(), you are using des.CreateDecryptor(Key, IV) in the console app (correct), but using des.CreateEncryptor(Key, IV) in the SQLCLR function (different and incorrect). Changing the SQLCLR function to instead use des.CreateDecryptor(Key, IV) results in the expected output.

Some general notes regarding the code:

You should use the Value property of the Sql* types (i.e. the input parameters) instead of calling ToString() or casting. For example:
```
string cipherText = CipherText.Value;
byte[] IV = DES_IV.Value;
byte[] Key = DES_Key.Value;
```
You should wrap the instantiations of MemoryStream, DESCryptoServiceProvider, CryptoStream in using() blocks so that the external resources are properly cleaned up. All 3 of those implement the IDisposable interface.
Since a NULL passed in for any of the 3 input parameters will return a NULL, you can bypass needing to handle that in the code by setting an option when creating the T-SQL wrapper function. Of course, this cannot be automated via the SSDT / Visual Studio Publish operation, but you can either handle the deployment manually in which case you issue the CREATE FUNCTION yourself, or you can add a post-release deployment script to execute an ALTER FUNCTION. So, remove this from the code:
```
if (CipherText.IsNull || DES_Key.IsNull || DES_IV.IsNull)
    return SqlString.Null;
```
and add the following to a post release rollout SQL script (SSDT / Visual Studio will handle that, at least):
```
ALTER FUNCTION [dbo].[DES_Decrypt](
    @CipherText [nvarchar](max),
    @DES_Key    [varbinary](8000),
    @DES_IV [varbinary](8000)
)
RETURNS [nvarchar](max)
WITH EXECUTE AS CALLER, RETURNS NULL ON NULL INPUT
AS EXTERNAL NAME [YourAssembly].[YourClass].[DES_Decrypt];
```
The RETURNS NULL ON NULL INPUT option in the WITH clause does the magic ;-). The more NULLs you get the more efficient this becomes as SQL Server doesn't need to call the code given it already knows the answer. Just keep in mind that this option returns a NULL if any input is NULL, so if any input params are expected to pass in a NULL, then this option won't work.

For more info on working with SQLCLR in general, please visit my site: SQLCLR Info