DB-side encryption via NHibernate

Hi people,

We're looking to encrypt a field using DB-side encryption via NHibernate (most examples we've come across are app-side, which is not what we would like). The issue has been raised a couple of times, but no answers yet it seems.

On SQL Server 2005, for instance, and forgetting key-related issues for now, this means INSERTing EncryptByKey(Key_GUID(MyKey), MyValueToEncrypt) and SELECTing DecryptByKey(MyEncryptedField). The property might be a string, whereas the field has to be a varbinary.

We've vaguely got somewhere - using upper() and lower() instead of encryption functions - by combining paired properties, one mapped with a custom user type and the other with a formula attribute.

However if anyone has any ideas on how to do this in a nicer way, we'd be most grateful for a few pointers :o)

From stackoverflow

• There is a project on SourceForge, MindLib, that has useful NHibernate utility functions and libraries. One of those is an EncryptedString library which allows you to store an encrypted string. Unfortunately, this is not doing it on the server side, as you originally requested.

  I've been looking for something similar. The only thing I could come up with is to create a User Function in SQL Server to encrypt the string. Something like this.

  MindLib EncryptedString

• Hi Todd, thanks for the feedback! We're keeping the MindLib approach as a last resort because it effectively displaces data ownership from the DB to the app. As far as we can tell, TDE-enabled DBs (say SQL Server 2008 or Oracle 10.2) make the problem go away, but not all our customers are willing to splash out on upgrades ;o)

• Posted outline of nasty hack on the NHibernate forum