Ef6: EF6 not able to work with encrypted columns in some cases

[jozsurf]

Hello

Recently, I added an encrypted nullable datetime column to a table in our MSSQL database (using Always Encrypted via documentation for e.g. https://blogs.msdn.microsoft.com/sqlsecurity/2015/08/27/using-always-encrypted-with-entity-framework-6/) and have run into some inexplicable issues:

• Similar to https://github.com/aspnet/EntityFramework6/issues/265 I am seemingly unable to retrieve entities with navigational properties that have an encrypted column. And so the following code:

using (var ctx = new DbContext())
{
   return ctx.Locations.Where(...).Include(l => l.UserLocations.Select(ul => ul.User)).ToList();
}

does not work when I've added an encrypted, nullable datetime field to User. It fails with the following exception: Operand type clash: datetime2 is incompatible with datetime2(7) encrypted with (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'cek_user_dob', column_encryption_key_database_name = 'DB') (actually it's in the inner exception)

• I have found that I am also unable to update the encrypted value from my web app i.e.

using (var ctx = new DbContext())
{
   var user = ctx.Users.Single(...);
   user.DateOfBirth = DateTime.Now;
   await ctx.SaveChangesAsync();
}

This fails with the following exception: Operand type clash: datetime2(7) encrypted with (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'cek_user_dob', column_encryption_key_database_name = 'DB') is incompatible with datetime encrypted with (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'cek_user_dob', column_encryption_key_database_name = 'DB') Statement(s) could not be prepared.

I have found however that other operations appear to work i.e. retrieving one or more users e.g.

            using (var ctx = new DbContext())
            {
                return
                    await ctx.Users
                        .Include(...)
                        .FirstOrDefaultAsync(...);
            }

We were previously on EF 6.1.3 - I have since updated to EF 6.2.0 but this is still a problem for me. I can confirm that User.DateOfBirth is not being used as a filter for my queries, nor is it the PK of the User table.

Would you be able to shed any light on this please?

[jozsurf]

So looking a bit more into this, I can confirm that the second issue is the same as #187 - and changing the type to datetime2 resolves the issue for me.

I am however still experiencing the first issue (i.e. similar to #265). I have worked around this by refactoring it so that my encrypted datetime2 field is not nullable and this appears to work fine. Still interested to know if this is a known issue and if my workaround is the best approach to dealing with this.

[vijayaramb]

anyone found a solution for using EF with Sql server 2016(with some tables having columns encrypted). Even, a simple select on column encrypted table is not working for me. Some workaround could be of great help.

[ErikEJ]

@vijayaramb did you read: https://blogs.msdn.microsoft.com/sqlsecurity/2015/08/27/using-always-encrypted-with-entity-framework-6/ ?

[vijayaramb]

Yes i have read this blog, i followed the steps suggested by Jakub Szymaszek post, but still left with just select query issue, i am just selecting one table with a column encrypted, it is not working, i am getting timeout error, but when i try with another table which does not have any column encrypted, i am getting results immediately.

[vijayaramb]

Here is the details error:
{System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync() at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket() at System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer() at System.Data.SqlClient.TdsParserStateObject.TryReadByteArray(Byte[] buff, Int32 offset, Int32 len, Int32& totalRead) at System.Data.SqlClient.TdsParser.TryReadSqlValue(SqlBuffer value, SqlMetaDataPriv md, Int32 length, TdsParserStateObject stateObj, SqlCommandColumnEncryptionSetting columnEncryptionOverride, String columnName) at System.Data.SqlClient.SqlDataReader.TryReadColumnData() at System.Data.SqlClient.SqlDataReader.TryReadColumnInternal(Int32 i, Boolean readHeaderOnly) at System.Data.SqlClient.SqlDataReader.TryReadColumn(Int32 i, Boolean setTimeout, Boolean allowPartiallyReadColumn) at System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i) at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i) at System.Data.Entity.Core.Common.Internal.Materialization.Shaper.ErrorHandlingValueReader1.GetUntypedValueDefault(DbDataReader reader, Int32 ordinal)
at System.Data.Entity.Core.Common.Internal.Materialization.Shaper.ErrorHandlingValueReader1.GetValue(DbDataReader reader, Int32 ordinal) at System.Data.Entity.Core.Common.Internal.Materialization.Shaper.GetPropertyValueWithErrorHandling[TProperty](Int32 ordinal, String propertyName, String typeName) at lambda_method(Closure , Shaper ) at System.Data.Entity.Core.Common.Internal.Materialization.Shaper.HandleEntityAppendOnly[TEntity](Func2 constructEntityDelegate, EntityKey entityKey, EntitySet entitySet)
at lambda_method(Closure , Shaper )
at System.Data.Entity.Core.Common.Internal.Materialization.Coordinator1.ReadNextElement(Shaper shaper) at System.Data.Entity.Core.Common.Internal.Materialization.Shaper1.SimpleEnumerator.MoveNext()
at System.Data.Entity.Internal.LazyEnumerator1.MoveNext() at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable1 source)
at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.b__1TResult
at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.ExecuteTResult
at System.Data.Entity.Internal.Linq.DbQueryProvider.ExecuteTResult
at System.Linq.Queryable.FirstOrDefaultTSource
at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary2 parameters) at System.Web.Mvc.Async.AsyncControllerActionInvoker.ActionInvocation.InvokeSynchronousActionMethod() at System.Web.Mvc.Async.AsyncControllerActionInvoker.<BeginInvokeSynchronousActionMethod>b__39(IAsyncResult asyncResult, ActionInvocation innerInvokeState) at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult2.CallEndDelegate(IAsyncResult asyncResult)
at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResultBase`1.End()
at System.Web.Mvc.Async.AsyncResultWrapper.EndTResult
at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult)
at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.b__3d()
at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.b__3f()
ClientConnectionId:1e0c7c7a-4631-4394-a100-15b7b2d8414e
Error Number:-2,State:0,Class:11}

[thuannguy]

I got a similar problem with the following (obfuscated) code:

        return dbContext.ACs
            .Include("FUTCV.CD")
            .Include("DUTCV.DCO.CD")
            .Include("Con")
            .Include("Con.CE")
            .Include("PCEnabledACs.PC")
            .Include("PCEnabledACs.PC.Con")
            .Include("BearingCD")
            .Include("Organization")
            .Include("CTACs.CT.ConditionNATPCs")
            .Include("CTACs.CT.ConditionNATACs");

Which generated: https://gist.github.com/thuannguy/20901ac26ce7bdfc408b07b89f324d53 (I have greatly cut it off!)

The problem here is that when joining many tables, EF had to generate a complex query with many UNION which in turn needed a bunch of default values such as

    CAST(NULL AS varchar(1)) AS [C28],

and if that column is correspondingly union with an encrypted column, I got the incompatible issue. Due to NDA, I can't publish database schema of the product I'm developing. I will try to see if I can provide a simplified schema when I have time (probably not any time soon 😞 ). I believe this is a known, long time issue that still has no solution. People who encounter it have to either abandon Always Encrypted usage or refactoring their database schemas which is not always a viable option.

[husseinraoouf]

this bug is blocking me 😞
i really need a fix

[rickychongjl]

This is still an issue, is there any fix to this yet?