This is the fifth and final post in my SQL Server Database Encryption with NHibernate Series. Check out the others posts here:

• Part 1 of 5: Summary
• Part 2 of 5: Create a database certificate and symmetric key for encryption
• Part 3 of 5: Configure domain and NHibernate mappings to decrypt and read an encrypted value
• Part 4 of 5: Configure domain and NHibernate mappings to encrypt and write a plain-text value
• Part 5 of 5: Wrapping Up

Wrapping Up

The last refactoring of the domain is a matter of developer convenience. Currently the SsnRead and SsnWrite columns feel “clumsy”, and although they do describe exactly their purpose, domain fields that are not encrypted do not have separate Read/Write fields. As such, I followed in the footsteps of a forum post on the Hibernate forums (https://forum.hibernate.org/viewtopic.php?p=2400105#2400105), describing that the two Read/Write properties be wrapped in a third property. As such, the below code snippet shows the refactored domain. This refactored domain has read/Write properties used internally by NHibernate for mapping purposes, and a normally named property not mapped, but usable like all other domain properties by Developers:

public class Cat {
  public virtual Guid Id { get; set; }
  public string Ssn 
  {
    get { return SsnRead; }
    set { SsnWrite = value; }
  }
  public virtual string SsnRead { get; set; }
  public virtual string SsnWrite { get; set; }
}

Summary

In retrospect, although the approach outlined above is a technically valid solution to encrypting data, I cannot help but feel the approach is unreasonably complex and leaves me feeling “dirty”.

Furthermore, specific concerns I have about the approach outlined above include:

• Requiring at least two properties (and a third for ease of use) on the domain for each encrypted database field
• Dynamic SQL manipulation of NHibernate’s auto-generated SQL is contrary to one purpose of an ORM: abstracting the database implementation from the developer
• Encrypted field values have the potential for being transmitted in plain text over the internal network, as they are not encrypted until they reach the database server

In practice, I would typically recommend encryption of data within an application’s business layer, having the ORM map data that has already been encrypted to the database. This approach addresses each of my above concerns, but is not the ultimate answer. Business layer encryption introduces at least one potential restriction on a solution’s architecture, specifically, requiring any access to the encrypted data to go through the business layer for decryption. Despite the potential restriction, in most circumstances, I would still advocate for business layer encryption.