1

We are experiencing some issues with EF6 and Always encrypted feature.

I believe we need to set up something into DBContext, in order to instruct how to encrypt or decrypt columns, but I couldn't find a way to do this.

We already have an ADO access layer, and it works perfectly with encrypted fields. We would rather use EF instead of ADO.

Symptoms are:

  1. With EF, We are able to query the data. And decryption process works fine.
  2. Insertion process throws error below:

Operand type clash: varchar is incompatible with varchar(8000) 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 = 'Development_v2_qa') collation_name = 'SQL_Latin1_General_CP1_CI_AS'**

  1. Query with where clause, using an encrypted field, throws same error.

Technologies used:

  • EF6 with Poco entities.
  • AzureKeyVault for storing encryp/decryp masterkey.
  • Using SSL Certidicate to authenticate against KeyVault
  • Connection string contains "Column Encryption Setting=enabled;"
  • AzureSqlServer
  • FWK4.6
  • ADO

We have some code which works fine with ADO. It works fine with every SqlConnection

// Instantiate our custom AKV column master key provider.
// It uses the GetToken function as the callback function to authenticate to AKV
SqlColumnEncryptionAzureKeyVaultProvider akvprov = new SqlColumnEncryptionAzureKeyVaultProvider();
akvprov.KeyVaultClient = SecureConfigurationManager.KeyVaultClient;
// Register the instance of custom provider to SqlConnection
Dictionary<string, SqlColumnEncryptionKeyStoreProvider> providers = new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>();
// "SqlColumnEncryptionAzureKeyVaultProvider.ProviderName" is the name of the provider. It must match the string we used when we created the column master key
providers.Add(SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, akvprov);
SqlConnection.RegisterColumnEncryptionKeyStoreProviders(providers);
Vemonus
  • 868
  • 1
  • 16
  • 28
Adrian Di Nucci
  • 11
  • 2

2 Answers2

1

Yep, I just found the same problem, needed to add

[Column(TypeName = "varchar(max)")]

in the POCO type before the field for it to work. Be nice if the error was a bit clearer (and nicer still if NVARCHAR did actually work)

Paul Roub
  • 36,322
  • 27
  • 84
  • 93
Dylan Nicholson
  • 1,301
  • 9
  • 23
0

I'm working through the same issue. The problem is with the datatype mapping from C# to the database. Not all the lengths matter at all for always encrypted and varchars with Entity Framework, only varchar(max) or varchar(8000). I have all the entity framework working with azure key vault for all the datatypes, same as you. This link below shows how to do the insert with inline SQL. I've only worked with entity framework and hope I never have to work in inline sql, even though I might have to, if I can't find a way to shrink down the database storage overhead needed for encryption, or look to something like Stretch Db, also another feature in SQL Server 2016. Thanks Jakub Szymaszek and Microsoft.

I have conceded and made all of my data types varchar(max) and it works just fine. So string = varchar(max). The weird thing is that there is not 8000 characters in the encryption, but there is probably 8000 allocated.

"something1" becomes this after encryption and insert: 0x0190F9D80C3F70890FB154F2123459506AD5BDA165333710D161ED80E42FCAFA882C66FF5B68E412B5F9EE11A9F308201D0AE2BD4032151398171FDBE2F3AEA20D

Interesting thing about varchar(max) is that supposidly there is a link to a table or somewhere else the data is stored, beside the table it is inserted into, so varchar(max) may only take the amount shown. (I'm a dev)

The dataType for my column and stored procedure variables: [testVarChar] varchar COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,

The data type of the parameter targeting the SSN column is set to an ANSI (non-Unicode) string, which maps to the char/varchar SQL Server data type. If the type of the parameter was set to a Unicode string (String), which maps to nchar/nvarchar, the query would fail, as Always Encrypted does not support conversions from encrypted nchar/nvarchar values to encrypted char/varchar values. See SQL Server Data Type Mappings for information about the data type mappings.

https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/develop-using-always-encrypted-with-net-framework-data-provider

Stephen Himes
  • 655
  • 9
  • 13
  • Perhaps you can summarize the approach suggested in the link. As it stands it seems like your answer is simply saying 'me too'. – FGreg Apr 12 '17 at 16:28