x

Data encryption

Hi,

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?

Thanks
more ▼

asked Nov 02, 2010 at 07:17 AM in Default

Nova gravatar image

Nova
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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][1], 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

[1]: http://msdn.microsoft.com/en-us/library/k5b5tt23(v=VS.80).aspx
more ▼

answered Nov 02, 2010 at 09:01 AM

Oleg gravatar image

Oleg
15.9k 2 4 24

(comments are locked)
10|1200 characters needed characters left

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][1] and any other major client language will have something similar.

[1]: http://docs.python.org/library/crypto.html
more ▼

answered Nov 02, 2010 at 09:49 AM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

(comments are locked)
10|1200 characters needed characters left

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][2].

[2]: http://www.netlib.com/
more ▼

answered Nov 04, 2010 at 02:31 AM

K. Brian Kelley gravatar image

K. Brian Kelley
933 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x38

asked: Nov 02, 2010 at 07:17 AM

Seen: 1278 times

Last Updated: Nov 02, 2010 at 07:17 AM