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());
}
}
}
}