C＃4.0/Sql Server 2008：业务层加密/解密

Question

我只是需要一些建议在这里。我需要保护一些列数据，所以我一直在使用Google搜索C＃4.0/Sql server 2008（不是R2）。

这可能是我所需要的：

"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

什么是正确的做法？

我试图将使用Rijndael加密的数据保存到数据库。

我得到错误：Length of the data to decrypt is invalid

我还没有这一派还。

我应该继续与此结合还是应该尝试用CLR或什么来解决这个问题？ 我只是找不到教程，告诉我如何在业务层中加密/解密。

有什么建议吗？

谢谢

编辑：

确定这是我到目前为止所。 数据用Rinjdael加密，如前所示。 它存储在列

TestCrypt (varbinary(255), null) 

我看起来像这样在数据库中：

0xF6CA99F5B6BFA1D9A0BD2381177E049E02F381078966195B68A54A5BEC4C 

它与一个SP检索，如：

SELECT cast(s.TestCrypt AS varbinary) AS TestCrypt 

而在数据访问层：

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

误差在DecryptStringFromBytes在这行

plaintext = srDecrypt.ReadToEnd(); 

错误发现：数据的长度来解密是无效的。

Answer 1

请对下面的文章一看：

Column Encryption in SQL Server 2008 with Symmetric Keys

Cell level Encryption in SQL server

Answer 2

由于使用业务层加密和解密远，我更喜欢使用以下加密和解密方法：

#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 

Answer 3

使用您喜欢的任何加密提供程序在.net中加密数据，并将其存储在列的数据类型为VARBINARY，长度正确。

其余数据与其他所有数据一样。

Answer 4

SELECT cast(s.TestCrypt AS varbinary) AS TestCrypt 

那行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.

尝试：

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