1

I'm trying to create a clr helper function to show the plain text values of some encryptyed data in our db. Below is the function, the decryption code you see (2nd and 3rd methods) are used successfully in non-clr code but here it is failing with the ever famous "Padding is invalid and cannot be removed." error. The encryption side is done entirely in c# code and encrypted string values inserted into the db. I've replaced the key and salt with nonsense strings fyi. data is stored in nvarchar columns. Anyone have any clue whats going on wrong?

Just to reiterate, same code (2nd and 3rd methods) work outside of a sql clr function but not working here.

using System.Data.Sql;
using System.IO;
using System.Security.Cryptography;
using System.Data.SqlTypes;
using System.Text;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;

public class Functions
{
    private static byte[] _Key { get { return System.Text.Encoding.ASCII.GetBytes("abcdefghijklmnopqrstuvwxyz"); } }
    private static byte[] _IV { get { return System.Text.Encoding.ASCII.GetBytes("abcdefghijklmn"); } }

    [SqlFunction(IsDeterministic = true, IsPrecise = true, DataAccess = DataAccessKind.None)]
    public static string Decrypt(SqlString value)
    {
        return _Decrypt(value.ToString(), _Key, _IV);
    }

    public static string _Decrypt(string encryptedString, byte[] key, byte[] iv)
    {
        if (string.IsNullOrEmpty(encryptedString))
        {
            return null;
        }

        using (RijndaelManaged rijndael = new RijndaelManaged())
        {
            ICryptoTransform decryptor = rijndael.CreateDecryptor(key, iv);
            return _Decrypt(encryptedString, decryptor);
        }
    }
    public static string _Decrypt(string encryptedString, ICryptoTransform decryptor)
    {
        if (string.IsNullOrEmpty(encryptedString))
        {
            return null;
        }
        UTF8Encoding textConverter = new UTF8Encoding();
        using (MemoryStream msDecrypt = new MemoryStream())
        {

            // rijndael.Padding = PaddingMode.None;
            using (CryptoStream csDecrypt = new CryptoStream(msDecrypt, decryptor, CryptoStreamMode.Write))
            {
                byte[] encrypted = Convert.FromBase64String(encryptedString);
                csDecrypt.Write(encrypted, 0, encrypted.Length);
                csDecrypt.FlushFinalBlock();
                return textConverter.GetString(msDecrypt.ToArray());
            }
        }
    }
}
danatcofo
  • 703
  • 8
  • 18
  • by the way, forcing the Padding.None padding on `new RijndaelManaged{ Padding = Padding.None }` just results in garbage text – danatcofo Apr 15 '14 at 19:18
  • There's nothing obviously wrong. Encryption and decryption just do not match. Find the difference. – usr Apr 15 '14 at 22:43
  • Figured it out. had a mix of encrypted and unencrypted data and was using the wrong key/iv – danatcofo Apr 16 '14 at 14:48

0 Answers0