This is the third 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

Reading Encrypted Data via NHibernate

Now that we’ve covered the basics of encrypting and decrypting data in SQL, let’s look at how to configure your domain and mapping code in NHibernate to access the data. In the code examples below, we’ll be building upon a basic domain and mapping for the Cat table. As a baseline, our starting domain and mapping classes look like this:

public class Cat {
  public virtual Guid Id { get; set; }
}

public class CatMap : ClassMapping {
  public CatMap() {
    Id(x => x.Id, m => m.Generator(Generators.GuidComb));
  }
}

Reading encrypted data stored in SQL Server using NHibernate is fairly straight forward via the use of the Formula configuration option on a mapped column. For this example, I am going to amp an encrypted social security number field. After a quick refactor of the domain and mapping classes, we have the following:

public class Cat {
  public virtual Guid Id { get; set; }
  public virtual string SsnRead { get; set; }
}

public class CatMap : ClassMapping {
  public CatMap() {
    Id(x => x.Id, m => m.Generator(Generators.GuidComb));
    Property(x => x.SsnRead, m => m.Formula(“dbo.DecryptSsn(Id)”));
  }
}

As shown above, the Formula mapping option tells NHibernate to execute the specified function when accessing the value for the associated field. In my above example, I have directed NHibernate to call the “dbo.DecryptSsn()” function, passing in the value of the “Id” field for this record. Note: I chose to pass in the “Id” field, but there are other methods of decrypting a field (i.e., passing in the decrypted value instead of the Id field to a User Defined Function).

The final step required for reading the encrypted value from the database is to create the DecryptSsn function. The code below demonstrates a sample function to decrypt the Ssn field associated with the Id passed as the parameter:

CREATE FUNCTION [dbo].[DecryptSsn] (@Id uniqueidentifier)
RETURNS varchar(11)
AS
BEGIN
  DECLARE @DecryptedSsn varchar(11)
  SELECT 
    @DecryptedSsn = CAST(DECRYPTBYKEYAUTOCERT(CERT_ID(‘TestCert’), NULL, Ssn) AS varchar(11))
  FROM dbo.Cat 
  WHERE Id = @Id
  RETURN @DecryptedSsn
END