Juan Carlos

Compression and encryption: you may like or hate them, but they have been (and will be) there for a long time.

In fact, there are so many resources devoted to compression and encryption that is difficult to believe anyone could have a requirement and nobody has already developed a solution for that requirement.

Well, this is not totally true. For example, related to compression, time ago I had to develop a tool to perform on-the-fly compression/decompression for the output/input of windows programs (something similar to Unix tool mknod, but as a native Windows application, not emulated with Cygwin or similar).

Now, although the built-in support for Encryption in SQL Server 2005 seems to be enough to solve almost any encryption requirement at database level, I miss one feature: a native encrypted text data type that could be used as any other native data type in table definition.

I won’t discuss here whether it is necessary or not to have this data type or if it is better to satisfy this requirement using other techniques. This is better done by Sybase in its product features sheet (http://www.sybase.com/detail?id=1036205 or search for the words column and encryption in sybase.com site if the previous link becomes broken).
I know at least Sybase and Oracle 10g R2 are at this moment offering transparent data encryption at column level.

So, I started thinking how it could be done in SQL Server 2005. Many fancy features came to my mind during this phase, however, I tried to simplify as much as possible while keeping an interesting level of functionality for the this UDT. These are some of the features implemented in the Compressed and Encrypted UDT:

• Encryption is done using the Rijndael algorithm with a 256 bits Key and 128 bits IV derived from the pass phrase provided by the user.
• The encrypted data is salted with random data to avoid obtaining the same encrypted data with the same input plain data. This contributes to make encryption stronger.
• Prior to encryption, input plain data is compressed. This is not necessary at all, but I like compression and it is so easy to compress…
  Actually, the compression only takes place if the length of the original plain text is greater than 128 bytes as an attempt to avoid getting compressed data larger than plain data.
• There is no need to use the SQL Server built-in functions to encrypt/decrypt the data. The UDT implementation does it for you (no more open symmetric key or EncryptByKey instructions for example). So, applications code is simpler.
• Of course, you can create your own database master key and take care of it (backup it and store the backup in a secure site), but you can get ride of that work and let this implementation to create and maintain the database master key for you. In this way, when you restore the database in a different server, you only need to provide your secret pass phrase (which should be stored in the most secure site I know: your brain :-) )

If you want to see a demo about how to create and use this compressed and encrypted UDT read this article: http://jcarlossaez.blogspot.com/2006/11/demo-for-compressed-and-encrypted-udt.html

# posted by Juan Carlos @ 5:09 PM