We are trying to use Always Encrypted feature of Sql 2016 with Asp.net Identity 2.0. We have set Always Encrpted on PhoneNumber and Email columns. We are able to encrypt phone number but the register method in Account Controller fails when we try to encrypt email column. We get the following exception when we try to register:
System.Web.Http.Dispatcher.HttpControllerDispatcher.d__1.MoveNext()", "InnerException": { "Message": "An error has occurred.", "ExceptionMessage": "Operand type clash: nvarchar(256) 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 = 'StoreIdentityEF6AE') collation_name = 'Latin1_General_BIN2' is incompatible with varchar", "ExceptionType": "System.Data.SqlClient.SqlException",
Has anyone encountered this issue and resolved successfully? Looking for some suggestions to encrypt customer data with Asp.net Identity
I've solved my issue.
ASP.NET Identity uses the UserStore. This file contains some methods like FindByNameAsync that search for the given name but convert it ToUpper in the query.
The Always Encrypted columns can't be queried with a ToUpper conversion.