Applying AES Encryption to Databases
AES encryption requires a key and an initialization vector ("IV" in cryptospeak). You cannot encrypt or decrypt anything in AES without having both the key and the vector. From this standpoint, you can keep the encryption key private to your application and generate different IVs for each record you store in the database.

It's best to have different IVs for each record to prevent people from figuring out what one value means (even though it's garbled) and then drawing conclusions about the content of similar records. For example, if you encrypt "ABCD" using an encryption key and it changes it to "!S(*Z", and at a later date you encrypt another "ABCD" record, the second record will also be "!S(*Z"—if you used the same key to encrypt it. The value is still encrypted, but someone can use a little deductive reasoning while looking at encrypted values in the database to infer the content of values in other records.

So, if you have a column called ShelterName that stores the name of the battered women's shelter the client is staying in, and a person browsing your back end data knows that Client X is in the "Fourth Street Shelter" and that the encrypted value stored in that column is "&@ASD&*A!" then that person also knows that all clients whose record contains "&@ASD&*A!" in the ShelterName column are also staying in that shelter. This defeats the point of encrypting the column in the first place. Using a separate IV for each record makes the encrypted value unique for each record.

In the Client table, you want to be able to search on LastName so you can store the LastName using a shared IV, but for the rest of the columns you can store the data using AES with a unique IV for each record. Since all records encrypted with the same encryption key and IV will have the same binary value, this will allow you to search on the LastName column.

Column	Data Type	Nullable
ClientID	Int, Identity	No
IV	Varbinary(100)	No
FirstNameCrypted	Varbinary(100)	No
LastNameCrypted	Varbinary(100)	No
LastNameMD5	Varbinary(100)	No
ShelterName	Varbinary(100)	No

This solution lets you search on the client's last name but not on any of the other fields, and it encrypts all the fields other than last name in a way that makes the encrypted value unique on a per record basis—even if they share values with other records. It's perfectly safe to store the IV value in each record because your application holds the encryption key. Because the database doesn't contain any copy of the encryption key, someone with access to the database would still need your application to decrypt the values. The IV exists merely to keep the encrypted values stored in your database unique even when the value stored is the same.