Always encrypted mapping in NHibernate

Question

Currently I'm using SQL Server 2016 to make benefit of Always Encrypted feature. There are a couple of columns that I should encrypt. I've encrypted those columns with SQL Server. NHibernate can easily read data from SQL Server but when it tries to insert data in the DB it will throw an exeption like below:

Operand type clash: nvarchar(4000) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'EncTest') is incompatible with nvarchar(250) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'EncTest')

This is my column mapping for the specific column in NHibernate:

<column name="DisableTxt" length="100" sql-type="NVarChar" />

What mapping I should define in my hbm files?

Answer 1

I found a solution to this problem, first I would like to describe why NHibernate can't work with Encrypted Columns in Always Encrypted feature:

When we enable AlwaysEncrypted in our connection string ,ADO.NET automatically executes a store procedure sp_describe_parameter_encryption before any db operations to to determine which parameters correspond to database columns that are protected by using the Always Encrypted feature. This sp is sensitive for the length of the fields and if the specified parameter length isn't equal to the column length, SQL Server will give us error below:

Operand type clash: nvarchar(4000) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'EncTest') is incompatible with nvarchar(250) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'EncTest')

This happens because NHibernate always define parameter size of 4000 for NVarchar columns (if column length isn't NVarchar(max)). So Imagine We've got a column with length of 30 but NHibernate define a parameter with the length of 4000 for specified column. Why NHibernate does that?

if you take a look on SqlClientDriver.cs line 146 on Nhibernate source, you will see comment below:

// Do not override the default length for string using data from SqlType, since LIKE expressions needs
// larger columns. https://nhibernate.jira.com/browse/NH-3036

So how we can solve this problem? We can create a new Driver for NHibernate which define accurate parameter length. (of course if you don't care about %% like expressions). (I've used this method on NHiberate 3.x)

public class NewDriver : NHibernate.Driver.Sql2008ClientDriver
{
        public override IDbCommand GenerateCommand(CommandType type, SqlString sqlString, SqlType[] parameterTypes)
        {
            IDbCommand command = base.GenerateCommand(type, sqlString, parameterTypes);
            NewDirver.SetParameterSizes(command.Parameters, parameterTypes);
            return command;
        }

        public static void SetParamterSizes(IDataParameterCollection parameters, SqlType[] parameterTypes)
        {
            for(int index=0;index<parameters.Count;++index)
            {
                NewDriver.SetVariableLengthParameterSize((IDbDataParameter)parameters[index], parameterTypes[index]);
            }
        }

        public static void SetVariableLengthParmaeterSize(IDbDataParameter dbParam, SqlType sqlType)
        {
            SqlClientDriver.SetDefaultParameterSize(dbParam, sqlType);
            if(sqlType.LengthDefined && !IsText(dbParam, sqlType) && !IsBlob(dbParam, sqlType))
            {
                dbParam.Size = sqlType.Length;
            }

            if(sqlType.PrecesionsDefined)
            {
                dbParam.Precision = sqlType.Precision;
                dbParam.Scale = sqlType.Scale;
            }
        }


}