Data encryption

If you need to protect the data in SQL Server 200 database, you might want to consider using one of the AES compliant symmetrical algoritms available in .NET Framework and write your custom code to encrypt existing data in your tables. The standard guidelines spell for creating a 32 byte long key and protecting it with 1024 bit strong asymmetric RSA. Using the .NET sn utility, you can create your own certificate and then use it to protect your symmetric key which you will use to protect your data. This way, you will get best results: your key is secured with RSA and your data is secured with one of the extremely fast symmetric algorithms: TripleDES or Rijndael

• TripleDES: this one needs 24 byte key and 8 byte initialization vector. The 24 bytes key is split by the algorithm into 3 8-byte keys. The data is encrypted with first, decrypted with second and then encrypted again with the third key. This means that the 24-byte key should be chosen with utmost care. For example, if first and second 8-byte chunks are the same then the algoritms will be forced to waste its time just to effectively encrypt the data with the third key reducing the protection strength. The strength of this algorithm is 168, not 192 as one would expect (24 bytes * 8 bit per byte). This is because TripleDES requires that the number of set bits in every byte of every key is odd, so it will use the rightmost bit of every byte for parity, thus reducing it strength to somewhere between 24 * 7 = 168 and 24 * 8 = 192 depending on the key, so 168 is a guaranteed miminum assumming that the supplied key is not weak.
• Rijndael: This algoritms is simply as strong as its key with maximum key size of 32 bytes, so it offers up to 256 bit strength. It needs 16 byte initialization vector to work. If you would like the same data to be encrypted to the same value then you use the same IV, otherwise, you keep the same key but use different IV to ensure salting of the data.

Both of the algorithms described above are used everywhere, including secure connections to the Internet sites, SQL Server TDE and cell-level encryption, codeless protection of the .NET configuration files sections.

Please let me know if you need a sample leak-free C# code to wrap one of the existing algoritms and I will add it to this answer.

Oleg

Oleg's answer is truly impressive, and I second all of it.

But let me point out one specific option you may want to look at.

Do the encryption and decryption in the client.

This means that the version of SQL you have to work with will be immaterial. If you just let SQL store the encrypted data and do the encryption/decryption on the client then you can use whatever encryption library you are most comfortable with or use any other encryption tool with a reasonable API. Depending on your circumstances you can choose symmetric or asymmetric encryption (you did not provide enough informaiton to provide any real advice on that front), or as Oleg alluded you can choose a hybrid approach where a symmetric key is encrypted with an asymmetric algorithm (this is what is done most often in the real world when you wish to use an asymmetric algortithm on a large amount of data due to symmetric encryption being vastly faster than asymmetric).

You can then easily search the database for a specific from your client application by just encrypting the value to be found the same way you encrypted the data originally. (You will however loose the ability to do like comparisons with wildcards and you will only be able to do size comparisons like <, >, or between if you choose an encryption algorithm that preserves those like a Caesar Cipher, but any encryption algorithm that preserves that type of relationship will necessarily be weak and almost not worth using).

Oleg already linked to a fine .Net tool you would use, and there is a good discussion of Python encryption libraries at http://docs.python.org/library/crypto.html and any other major client language will have something similar.

Another option, if you control the data access layer, is XP_Crypt. If you're looking for something seamless to implement to an existing code base, there is also NetLib Encryptionizer.