Hi people,

We're looking to encrypt a field using DB-side encryption (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)

Well, this seems to work, but it's a big hairy hack.

Use 2 properties:

- one property, for encryption, is mapped with a custom usertype: SQLTypes() returns a BinarySqlType, and NullSafeSet() embeds the EncryptByKey() function in the SQLCommand - change the SQLParameter.DbType to a string to fool System.Data.SqlClient into accepting your input, and watch out for caching which might make the embedding recursive;

- the other property, for decryption, is mapped with a formula attribute containing the DecryptByKey() function - this will further need to be converted to say nvarchar, so extend MsSql2005Dialect to register nvarchar (as a function, since RegisterKeyword() is internal to NHibernate)

This will result in a single encypted column on the db. We "wrapped" the 2 properties in a 3rd property with a nice name to make the object less ugly to read and write to, and used the name attribute in the mapping file so the db column matches.

We weren't too careful about key management, but note that the OPEN ... KEY statement needs to be called inline before queries, as it doesn't seem to accept variable syntax, and that you need to be careful about transaction scope.

As ever, if anyone has warnings or improvements to suggest, go ahead!

... gimme TDE any day

Hello, G!

Could you please send me your code? I am facing the same situation.

Thanks,

Ricardo Peres