I need to encrypt and decrypt data but the problem is our client is using sql server 2000 through to 2008. I need to use the same procedure throughout. Is there a third party or other way to do this without having to upgrade from sql2000 which isn't an option at the moment?
asked Nov 02 '10 at 07:17 AM in Default
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
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.
answered Nov 02 '10 at 09:01 AM
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.