question

From what you describe, it appears that you don't use the encryption options available to SQL Server but opt to use the front end code to manage protection of your data. This is a perfectly acceptable design. The size of plain data subjected to protection in your case ranges from 13 to 19 bytes theoretically, and probably 13 to 16 bytes in reality. Older VISA cards can have 13 digits, American Express - 15 and others - 16. There are no cards with 14 digits. Since the default cipher block size of Rijndael algorithm is 16 bytes, so it needs 32 bytes key and 16 bytes initialization vector to provide you a maximum strength of 256 bit, it means that some of your plain text data will become 16 bytes and some - 32 bytes in length. This also means that even if you configure the algorithm to operate in Cipher Block Chaining mode as you should, it will be used only once or twice depending on the data. For example, if you need to encrypt American Express (or older VISA) numbers then encrypted data will be 16 bytes in length (just one block) and if you need to encrypt Master Card then the size of the encrypted data will be 32 bytes (2 blocks). The native way to encrypt the data is to call **Encoding.ASCII.GetBytes(plainText)** and then feed it to the encryption method which accepts a byte array and returns a byte array. This array can then be stored in the database as varbinary(32) so you will have data length of 16 and 32 bytes in your table. If you need to store encrypted data as varchar then you will have some size overhead because you will have to call **Convert.ToBase64String(encryptedBytes)** first before storing the data in the database, and the latter will increase the size from 16 to 24 and from 32 to 44 bytes respectively, so the data type of your column in this case should be varchar(44). In my opinion, this overhead is not justified and therefore, I would definitely opt to store the data in varbinary(32) rather than make an extra call to **Convert** and then allow the size of the stored data to increase without a good reason. And finally, with outsourcing the data protection activities to the front end, you will have to remember that your data is as secure as the security of the key storage and your encryption is as strong as the strength of that key. <\!-- **Begin Edit** Here is the sample of the front end code you requested earlier. This is pretty similar to your own snippet in the comments below, it just has some bells and whistles to make a complete console app, and appears to be leak free. using System; using System.IO; using System.Text; using System.Security.Cryptography; namespace ContoseEncryptionHarness { class Program { /// /// Represents the encryption key for test purposes /// static readonly byte[] _key; /// /// Represents initialization vector for test purposes /// static readonly byte[] _vector; /// /// Static constructor. It sets the values of the privately scoped /// and members to be /// used by the data protection methods. /// static Program() { // This is by no means a suggestion of how to implement the // encryption key storage as it is, generally speaking, none // of anybody's business except the developer authoring the // data protection in accordance with company policies :) // this is what is usually stored somewhere safe and secure string password = "somewhatC0m1exPassw0rdO07*78_6dfg82+l3I|Lkwe4w"; // different salt can be used if needed. For example, consider the // system which symmetrically encrypts web app user passwords and // wants to ensure that even if 2 users have the same password, the // encrypted values of these passwords will be different. string salt = "someSaltValue"; // Generate MD5 hash using the password and salt values PasswordDeriveBytes pdb = new PasswordDeriveBytes( Encoding.ASCII.GetBytes(password), /* password bytes */ Encoding.ASCII.GetBytes(salt), /* salt bytes */ "MD5", /* MD5 is a good algorithm to use */ 3 /* 3 iterations should be plenty */); // Get the pseudo-random values for key and IV ensuring // that the key is 32 bytes and IV is 16 bytes in length _key = pdb.GetBytes(32); _vector = pdb.GetBytes(16); } /// /// Actual harness to test data protection methods /// /// string array of arguments; not used static void Main(string[] args) { Console.WriteLine("Please enter some text to encrypt or " + "hit the return key to end. "); string plainText = Console.ReadLine(); while (plainText != String.Empty) { try { // encrypt some plain text. Use return value to store in // the database in the column of varbinary data type byte[] encrypted = Encrypt(plainText); // show on the screen how it looks in friendly base64 format string encryptedText = Convert.ToBase64String(encrypted); Console.WriteLine("Here is that string encrypted: " + encryptedText); // decrypt encrypted data to test decryption method string decryptedText = Decrypt(encrypted); // show decrypted data on the screen Console.WriteLine("Here is that string decrypted: " + decryptedText); Console.WriteLine(""); Console.WriteLine("Please enter more text to encrypt " + "or hit the return key to end. "); plainText = Console.ReadLine(); } catch (Exception ex) { Console.WriteLine("Error occured: " + ex.Message + " Hit any key to end."); plainText = String.Empty; Console.ReadLine(); } } } #region Data Protection Methods /// /// Encrypts plain text passed into method as a parameter. /// /// Plain text to encrypt /// Encrypted data in byte array format. static byte[] Encrypt(string plainText) { byte[] result = null; if (string.IsNullOrEmpty(plainText)) return result; byte[] dataToEncrypt = Encoding.UTF8.GetBytes(plainText); // RijndaelManaged inherits Rijndael which in turn inherits // SymmetricAlgorithm, which implements IDisposable, so there // is no free lunch, using is a must here using (RijndaelManaged provider = new RijndaelManaged()) { // Cipher Block Chaining mode adds an extra layer of security // It XORs IV and first block of plain text before encrypting it // and then uses the encrypted block as IV for next plain text // block. This allows same blocks to be encrypted differently. provider.Mode = CipherMode.CBC; using (ICryptoTransform encryptor = provider.CreateEncryptor(_key, _vector)) { // define memory stream to store encrypted data using (MemoryStream encryptedStream = new MemoryStream()) { using (CryptoStream cryptoStream = new CryptoStream(encryptedStream, encryptor, CryptoStreamMode.Write)) { // encrypt the data and write it to the memory stream. cryptoStream.Write(dataToEncrypt, 0, dataToEncrypt.Length); cryptoStream.FlushFinalBlock(); // update the state and clear the buffer cryptoStream.Close(); } // cryptoStream is disposed result = encryptedStream.ToArray(); encryptedStream.Close(); } // encryptedStream is disposed } // encryptor is disposed provider.Clear(); } // provider is disposed return result; } /// /// Decrypts previously encrypted data. /// /// Encrypted data in byte array format /// Decrypted data as plain text static string Decrypt(byte[] encrypted) { string result = string.Empty; if (encrypted == null || encrypted.Length == 0) return result; using (RijndaelManaged provider = new RijndaelManaged()) { // Use the same mode to decrypt the data provider.Mode = CipherMode.CBC; using (ICryptoTransform decryptor = provider.CreateDecryptor(_key, _vector)) { // define memory stream to store decrypted data using (MemoryStream decryptedStream = new MemoryStream()) { using (CryptoStream cryptoStream = new CryptoStream(decryptedStream, decryptor, CryptoStreamMode.Write)) { // decrypt the data and write it to the memory stream. cryptoStream.Write(encrypted, 0, encrypted.Length); cryptoStream.FlushFinalBlock(); // update the state and clear the buffer cryptoStream.Close(); } // cryptoStream is disposed result = Encoding.UTF8.GetString(decryptedStream.ToArray()); decryptedStream.Close(); } // decryptedStream is disposed } // decryptor is disposed provider.Clear(); } // provider is disposed return result; } #endregion } } **End Edit** --> Just my 2 cents. Oleg

Personally I like to take a 2 step approach to storing encrypted data. The application is responsible for the encryption/decryption, and the database simply stores the data in whatever format comes out of the encryption process (usually text). The advantage of this is that anyone with access to the database can only see encrypted data and has no *easy* way of simply reading it, and the people with the decrypt knowledge (i.e app devs) don't have access to the database. I know it's not 100% secure, but it just adds another level to the security.

What we did is store all encrypted data in hexadecimal format. This removed any of the problems that can arise with characters and the coding. In that way it doesn't matter what the field type is.

Oleg, Below is the code I am using for this encryption object. When I encrypt the data, and later decrypt it, the decrypted data is not the same as the original value that was passed into the Encrypt function. As an example, when I encrypt Password then Decrypt the encrypted string, the resulting value is Password�\\ �O�:. At this time, I have been unable to determine the exact issue and was hoping you could offer me a quick hand. I apologize for not commenting this, but I am unable to fit this amount of text in a comment. Thanks, Zach public class clsEncryption { private byte[] _Key; private byte[] _IV; public clsEncryption() { } public clsEncryption(string KeyName) { try { using (RegistryKey rk = Registry.LocalMachine.OpenSubKey(@"Software\Wow6432Node\Encryption", true)) { if (rk.GetSubKeyNames().Contains(KeyName)) { rk.OpenSubKey(@"Software\Wow6432Node\Key\" + KeyName, false); _Key = (byte[])rk.GetValue("enKey"); _IV = (byte[])rk.GetValue("IV"); } else { //if the Registry Key does not exist, return an error throw (new Exception("Specified encryption key does not exist.")); } } } catch (Exception ex) { throw (new Exception(ex.Message)); } } public byte[] Encrypt(string plainText) { if (plainText == null || plainText.Length <= 0) throw new ArgumentNullException("plainText"); if (_Key == null || _Key.Length <= 0) throw new ArgumentNullException("Key"); if (_IV == null || _IV.Length <= 0) throw new ArgumentNullException("IV"); byte[] result = null; byte[] dataToEncrypt = Encoding.UTF8.GetBytes(plainText); try { // Create a RijndaelManaged object with the specified key and IV. using (RijndaelManaged aesAlg = new RijndaelManaged()) { aesAlg.Mode = CipherMode.CBC; aesAlg.Key = _Key; aesAlg.IV = _IV; // Create an encryptor to perform the stream transform. using (ICryptoTransform encryptor = aesAlg.CreateEncryptor(aesAlg.Key, aesAlg.IV)) { // Create the stream used for encryption. using (MemoryStream msEncrypt = new MemoryStream()) { using (CryptoStream csEncrypt = new CryptoStream(msEncrypt, encryptor, CryptoStreamMode.Write)) { csEncrypt.Write(dataToEncrypt, 0, dataToEncrypt.Length); // encrypt the data and write it to the memory stream. csEncrypt.FlushFinalBlock(); // update the state and clear the buffer csEncrypt.Close(); } result = msEncrypt.ToArray(); msEncrypt.Close(); } } aesAlg.Clear(); } } catch (Exception ex) { throw (new Exception(ex.Message)); } // Return the encrypted bytes from the memory stream. return result; } public string Decrypt(byte[] cipherText) { // Check arguments. if (cipherText == null || cipherText.Length <= 0) throw new ArgumentNullException("cipherText"); if (_Key == null || _Key.Length <= 0) throw new ArgumentNullException("Key"); if (_IV == null || _IV.Length <= 0) throw new ArgumentNullException("IV"); string result = string.Empty; try { // Create a RijndaelManaged object with the specified key and IV. using (RijndaelManaged aesAlg = new RijndaelManaged()) { aesAlg.Mode = CipherMode.CBC; aesAlg.Key = _Key; aesAlg.IV = _IV; // Create a decrytor to perform the stream transform. using (ICryptoTransform decryptor = aesAlg.CreateDecryptor(aesAlg.Key, aesAlg.IV)) { // Create the streams used for decryption. using (MemoryStream msDecrypt = new MemoryStream(cipherText)) { using (CryptoStream csDecrypt = new CryptoStream(msDecrypt, decryptor, CryptoStreamMode.Write)) { // decrypt the data and write it to the memory stream. csDecrypt.Write(cipherText, 0, cipherText.Length); csDecrypt.FlushFinalBlock(); // update the state and clear the buffer csDecrypt.Close(); } result = Encoding.UTF8.GetString(msDecrypt.ToArray()); msDecrypt.Close(); } } aesAlg.Clear(); } } catch (Exception ex) { throw (new Exception(ex.Message)); } //Return the decrypted string return result; } public void CreateNewEncryptionKey(string KeyName) { try { using (RegistryKey rk = Registry.LocalMachine.OpenSubKey("Software\\Wow6432Node\\Key", true)) { if (!rk.GetSubKeyNames().Contains(KeyName)) { using (RijndaelManaged objRJ = new RijndaelManaged()) { objRJ.KeySize = 256; _Key = objRJ.Key; _IV = objRJ.IV; rk.CreateSubKey(KeyName); rk.SetValue("enKey", _Key, RegistryValueKind.Binary); rk.SetValue("IV", _IV, RegistryValueKind.Binary); objRJ.Clear(); } } else { throw (new Exception("Specified encryption key already exist")); } rk.Dispose(); } } catch (Exception ex) { throw new Exception(ex.Message); } } }