C# 4.0 / Sql Server 2008: Encrypt/decrypt in business layer

Question

I just need some advise here. I need to protected some column data, so I have been googling about encryption in C# 4.0/Sql server 2008 (not R2).

This is probably what I need:

"When the data must be protected from sysadmins. Practically, in this scenario it would be best not to use SQL Server internal encryption and far better to rely exclusively on the middle tier to perform encryption and decryption. It would be very difficult to protect your data from a determined sysadmin if your keys are stored in SQL Server." Source: msdn

What is the correct approach?

I have tried to save data which was encrypted with Rijndael and to database.

I get error: Length of the data to decrypt is invalid

I haven't googled this yet.

Should I continue struggeling with this or should I try to solve this with CLR or what? I just can't find a tutorial which shows me how to encrypt/decrypt in business layer.

Any suggestions?

Thank you

EDIT:

Ok this is what I have so far. The data is encrypted with Rinjdael as shown before. It is stored in column

TestCrypt (varbinary(255), null)

I looks like this in the database:

0xF6CA99F5B6BFA1D9A0BD2381177E049E02F381078966195B68A54A5BEC4C

It is retrieved with a sp, such as:

SELECT cast(s.TestCrypt AS varbinary) AS TestCrypt

And in data access layer:

while (reader.Read())
{
    data.TestCrypt = (byte[])reader["TestCrypt"];
}

The error is found in DecryptStringFromBytes at this row

plaintext = srDecrypt.ReadToEnd();

The error: Length of the data to decrypt is invalid.

If you post your encrypt/decrypt code, then maybe we could help with the error. This shouldn't be terribly difficult. — Paddy, Mar 12 '12 at 10:04

score 0 · Answer 1 · answered Mar 12 '12 at 09:19

0

Please have a look on following article:

Column Encryption in SQL Server 2008 with Symmetric Keys

Cell level Encryption in SQL server

answered Mar 12 '12 at 09:19

Hi and thanks. Both url:s shows how to encrypt/decrypt in sql server. I need this to happen in business layer in my asp.net web project. – Cliff Smith Mar 12 '12 at 09:49
Okay, I thought that you need encyrption and decryption mechanism in sql server...I'll update the answer..... – Mar 12 '12 at 09:59

score 0 · Answer 2 · 2012-03-12T10:35:15.360

As far using Encryption and decryption in business layer, I prefer to use following encryption and decryption method:

#region Encryption Decription
public class CryptorEngine
{
/// <summary>
/// Encrypt a string using dual encryption method. Return a encrypted cipher Text
/// </summary>
/// <param name="toEncrypt">string to be encrypted</param>
/// <param name="useHashing">use hashing? send to for extra security</param>
/// <returns></returns>
public static string Encrypt(string toEncrypt, bool useHashing)
{
    try
    {
        byte[] keyArray;
        byte[] toEncryptArray = UTF8Encoding.UTF8.GetBytes(toEncrypt);

        System.Configuration.AppSettingsReader settingsReader = new AppSettingsReader();
        // Get the key from config file
        string key = (string)settingsReader.GetValue("SecurityKey", typeof(String));
        //System.Windows.Forms.MessageBox.Show(key);
        if (useHashing)
        {
            MD5CryptoServiceProvider hashmd5 = new MD5CryptoServiceProvider();
            keyArray = hashmd5.ComputeHash(UTF8Encoding.UTF8.GetBytes(key));
            hashmd5.Clear();
        }
        else
            keyArray = UTF8Encoding.UTF8.GetBytes(key);

        TripleDESCryptoServiceProvider tdes = new TripleDESCryptoServiceProvider();
        tdes.Key = keyArray;
        tdes.Mode = CipherMode.ECB;
        tdes.Padding = PaddingMode.PKCS7;

        ICryptoTransform cTransform = tdes.CreateEncryptor();
        byte[] resultArray = cTransform.TransformFinalBlock(toEncryptArray, 0, toEncryptArray.Length);
        tdes.Clear();
        return Convert.ToBase64String(resultArray, 0, resultArray.Length);
    }
    catch (Exception ex)
    {
        return "Invalid";   
    }
}
/// <summary>
/// DeCrypt a string using dual encryption method. Return a DeCrypted clear string
/// </summary>
/// <param name="cipherString">encrypted string</param>
/// <param name="useHashing">Did you use hashing to encrypt this data? pass true is yes</param>
/// <returns></returns>
public static string Decrypt(string cipherString, bool useHashing)
{
    try
    {
        if (string.IsNullOrEmpty(cipherString))
            return "";
        byte[] keyArray;
        byte[] toEncryptArray = Convert.FromBase64String(cipherString);

        System.Configuration.AppSettingsReader settingsReader = new AppSettingsReader();
        //Get your key from config file to open the lock!
        string key = (string)settingsReader.GetValue("SecurityKey", typeof(String));

        if (useHashing)
        {
            MD5CryptoServiceProvider hashmd5 = new MD5CryptoServiceProvider();
            keyArray = hashmd5.ComputeHash(UTF8Encoding.UTF8.GetBytes(key));
            hashmd5.Clear();
        }
        else
            keyArray = UTF8Encoding.UTF8.GetBytes(key);

        TripleDESCryptoServiceProvider tdes = new TripleDESCryptoServiceProvider();
        tdes.Key = keyArray;
        tdes.Mode = CipherMode.ECB;
        tdes.Padding = PaddingMode.PKCS7;

        ICryptoTransform cTransform = tdes.CreateDecryptor();
        byte[] resultArray = cTransform.TransformFinalBlock(toEncryptArray, 0, toEncryptArray.Length);

        tdes.Clear();
        return UTF8Encoding.UTF8.GetString(resultArray);
    }
    catch (Exception ex)
    {
       return "Invalid";   
    }
}
}
#endregion

Yeah but you can return anything such as Invalid or InCorrecrt from excepton block......... — , Mar 12 '12 at 10:34
(Deleted original comment to add this) How does that even compile with the MS compilers? `catch (Exception ex) { }` -- the function never returns a defined value if an exception is thrown. I don't think `try/catch { do nothing }` (or `return String.Empty`) is valid here: it would be much better to let *an* exception escape, lest someone uses the function like `parameters.AddWithValue("@OldValue", CryptorEngine.Encrypt(oldValue))` -- if something bad occurs during `Encrypt` you'll store an empty value. Which is probably not what you want. — ta.speot.is, Mar 12 '12 at 10:36
Yeah, I used to return "Invalid" string from exception block so, whenever I used this encrypt/decrypt method, I used to checked with return value.... — , Mar 12 '12 at 10:39
Ok thanks, I´ve been googling but I cant find how to add the SecurityKey to web.config. — Cliff Smith, Mar 13 '12 at 09:42
Ok, I found it. I was creating a new key every time. Now I keep it in a static string in my class instead. I would like to store it in a SecurityKey in web.config, cannot find how to do that. Can you show me? — Cliff Smith, Mar 13 '12 at 19:28

score 0 · Answer 3 · answered Mar 12 '12 at 10:41

0

Encrypt your data in .net with whatever encryption provider you like and store it in a column with the VARBINARY data type of the correct length.

For the rest treat the data the same as all other data.

answered Mar 12 '12 at 10:41

Filip De Vos

11,568
1
48
60

score 0 · Answer 4 · answered Mar 12 '12 at 20:54

0

SELECT cast(s.TestCrypt AS varbinary) AS TestCrypt

That line truncates the value of TestCrypt to 30 bytes:

When n is not specified in a data definition or variable declaration statement, the default length is 1. When nis not specified with the CAST function, the default length is 30.

Try:

SELECT cast(s.TestCrypt AS varbinary(255)) AS TestCrypt

answered Mar 12 '12 at 20:54

ta.speot.is

26,914
8
68
96

Thanks. Now I got another error that Im struggling with: Padding is invalid and cannot be removed. – Cliff Smith Mar 13 '12 at 08:37
@CliffSmith http://stackoverflow.com/questions/604210/padding-is-invalid-and-cannot-be-removed-using-aesmanaged – ta.speot.is Mar 13 '12 at 10:01
but it doesnt help. I have replaced both using but I havent done antything with the size, because it is not set anywhere – Cliff Smith Mar 13 '12 at 11:48

C# 4.0 / Sql Server 2008: Encrypt/decrypt in business layer

4 Answers4