What is the most appropriate encryption for a small to medium companys' MSSQL database

Question

I want to implement encryption as there are multiple people with access to the database and my company wants to store personnel (sensitive) information in my application. To create some separation and security I want to implement encryption ideally using a 'key' they pick to encrypt the data in one SQL table.

I know doing it myself, I am going to miss a trick and tried-and-tested is probably best, especially for a company our size where we don't need to worry too much about hackers as the DB is not externally accessible. Just enough to keep out interested parties.

I would like to know what level of security is appropriate and also I am bit lost as to what to even Google to find out what sort of encryption maybe in a third party plugin I need to use as they all want to sell their product they will all say their own is great?

Most other questions I could find and the suggested 'Similar Questions' talked about data transfer encryption, hashing or ASP.NET

Answer 1

Personally I would recommend using AES as its very easy to implement and with it being sensitive personal data would provide enough encryption to keep people out unlike something like DES.

This article goes in depth into AES if you want to have a technical understanding of how it works: http://msdn.microsoft.com/en-us/magazine/cc164055.aspx and the basic examples shipped with it : http://msdn.microsoft.com/en-us/magazine/cc164846.aspx

A very clean example of how to implement it is here: http://www.obviex.com/samples/Code.aspx?Source=EncryptionCS&Title=Symmetric%20Key%20Encryption&Lang=C%23

Example stripped down (To prevent link rott)

using System;
using System.IO;
using System.Security.Cryptography;

namespace RijndaelManaged_Examples
{
    class RijndaelMemoryExample
    {
        public static void Main()
        {
            try
            {

                string original = "Here is some data to encrypt!";

                // Create a new instance of the RijndaelManaged
                // class.  This generates a new key and initialization 
                // vector (IV).
                RijndaelManaged myRijndael = new RijndaelManaged();

                // Encrypt the string to an array of bytes.
                byte[] encrypted = encryptStringToBytes_AES(original, myRijndael.Key, myRijndael.IV);  

                // Decrypt the bytes to a string.
                string roundtrip = decryptStringFromBytes_AES(encrypted, myRijndael.Key, myRijndael.IV);

                //Display the original data and the decrypted data.
                Console.WriteLine("Original:   {0}", original);
                Console.WriteLine("Round Trip: {0}", roundtrip);

            }
            catch (Exception e)
            {
                Console.WriteLine("Error: {0}", e.Message);
            }
        }

        static byte[] encryptStringToBytes_AES(string plainText, byte[] Key, byte[] IV)
        {
            // Check arguments.
            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("Key");

            // Declare the streams used
            // to encrypt to an in memory
            // array of bytes.
            MemoryStream    msEncrypt   = null;
            CryptoStream    csEncrypt   = null;
            StreamWriter    swEncrypt   = null;

            // Declare the RijndaelManaged object
            // used to encrypt the data.
            RijndaelManaged aesAlg      = null;

            try
            {
                // Create a RijndaelManaged object
                // with the specified key and IV.
                aesAlg = new RijndaelManaged();
                aesAlg.Key = Key;
                aesAlg.IV = IV;

                // Create a decrytor to perform the stream transform.
                ICryptoTransform encryptor = aesAlg.CreateEncryptor(aesAlg.Key, aesAlg.IV);

                // Create the streams used for encryption.
                msEncrypt = new MemoryStream();
                csEncrypt = new CryptoStream(msEncrypt, encryptor, CryptoStreamMode.Write);
                swEncrypt = new StreamWriter(csEncrypt);

                //Write all data to the stream.
                swEncrypt.Write(plainText);

            }
            finally
            {
                // Clean things up.

                // Close the streams.
                if(swEncrypt != null)
                    swEncrypt.Close();
                if (csEncrypt != null)
                    csEncrypt.Close();
                if (msEncrypt != null)
                    msEncrypt.Close();

                // Clear the RijndaelManaged object.
                if (aesAlg != null)
                    aesAlg.Clear();
            }

            // Return the encrypted bytes from the memory stream.
            return msEncrypt.ToArray();

        }

        static string decryptStringFromBytes_AES(byte[] cipherText, byte[] Key, byte[] IV)
        {
            // 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("Key");

            // TDeclare the streams used
            // to decrypt to an in memory
            // array of bytes.
            MemoryStream    msDecrypt   = null;
            CryptoStream    csDecrypt   = null;
            StreamReader    srDecrypt   = null;

            // Declare the RijndaelManaged object
            // used to decrypt the data.
            RijndaelManaged aesAlg      = null;

            // Declare the string used to hold
            // the decrypted text.
            string plaintext = null;

            try
            {
                // Create a RijndaelManaged object
                // with the specified key and IV.
                aesAlg = new RijndaelManaged();
                aesAlg.Key = Key;
                aesAlg.IV = IV;

                // Create a decrytor to perform the stream transform.
                ICryptoTransform decryptor = aesAlg.CreateDecryptor(aesAlg.Key, aesAlg.IV);

                // Create the streams used for decryption.
                msDecrypt = new MemoryStream(cipherText);
                csDecrypt = new CryptoStream(msDecrypt, decryptor, CryptoStreamMode.Read);
                srDecrypt = new StreamReader(csDecrypt);

                // Read the decrypted bytes from the decrypting stream
                // and place them in a string.
                plaintext = srDecrypt.ReadToEnd();
            }
            finally
            {
                // Clean things up.

                // Close the streams.
                if (srDecrypt != null)
                    srDecrypt.Close();
                if (csDecrypt != null)
                    csDecrypt.Close();
                if (msDecrypt != null)
                    msDecrypt.Close();

                // Clear the RijndaelManaged object.
                if (aesAlg != null)
                    aesAlg.Clear();
            }

            return plaintext;

        }
    }
}

Answer 2

All security is really about raising the bar and usability tradeoffs. With encryption, there are a lot of options here, but it really all comes down to key management.

Who has the keys to decrypt?

How are they stored?

Brute-force attacks on your application data (say they get a hold of a backup tape of the encrypted SQL Server database by intercepting your FedEx to Iron Mountain) are less likely than an internal attack on the key management system - for instance an employee or developer alters the program to decrypt and dump the data.

Because the application probably in general has to decrypt this data at any time to authorized users, I would probably concentrate on the visibility of the columns which are sensitive and the roles allowed to access them first, then worry about encrypting them.

SQL Server only offers transparent encryption for data as well as encryption on the connections. This is not useful if the users have SELECT * access to a table. Encrypting it yourself within a column without SQL Server's knowledge may be problematic. For instance, if one column is pay data and that is sensitive, if you encrypt it within a column, you can't just run SELECT Employee, SUM(Pay) GROUP BY Employee

So first I would start by ensuring you have identified users and roles in your application, reviewed what kind of access they have and ensure that all connections to the database use appropriate roles.

Answer 3

scramble information can be done with a Symmetric (Rijndael) Key but I don't know how much improve performance for an application that use SQL update.

Symmetric (Rijndael) Key