1

I want to use AlwaysEncrypted in a SQL 2016 Standard SP2 server with Entity Framework 6 but my query always fails with this error:

Microsoft.Data.SqlClient.SqlException (0x80131904): Operand type clash: nvarchar(11) encrypted with (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'MyDb') is incompatible with nvarchar Operand type clash: nvarchar(11) encrypted with (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'MyDb') is incompatible with nvarchar

My database collation is Latin1_General_CI_AS_KS_WS which is not compatible with AlwaysEncrypted because it needs _BIN2. I have chaned the problemtaic column collation to Latin1_General_BIN2 but it doesn't work.

UPDATE I'm sorry but I forgot to mentiop that I use stored procedure for querying the from database. :| It returns a filtered row set and the total number of filtered records.

UPDATE2

The problem is not my stored procedure but the filter on the nvarchar column. When I run my select without filter is Ok, but when I put this filter ( where EncryptedColumn like N'111 222 333 444') on the encrypted column it gives this error:

Operand type clash: nvarchar(11) 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 = 'MyDb') is incompatible with nvarchar

And if I change the 'LIKE' operator to an equal operator it returns with this: ... and nvarchar are incompatible in the equal to operator.

Improve this question
19
  • 1
    What is the query you are running? Not all queries are possible in AE Commented Jun 18, 2024 at 7:21
  • 1
    Are there triggers on the target table? It will be difficult for them to do anything when the AE private key is in possession of the client. Please Edit your question to include the full DDL of the table. Commented Jun 18, 2024 at 7:21
  • 1
    Randomized encryption prevents searching. That's explained in the docs: ` prevents searching, grouping, indexing, and joining on encrypted columns.` What are you trying to do in the first place? Do you really need Always Encrypted? Properly hashing and salting passwords is better than any form of encryption for example. If you really do, why not use Deterministic encryption? Commented Jun 18, 2024 at 8:26
  • 1
    Randomized cannot be used for joins etc, you need deterministic for that. It's not enough saying you're using a procedure, that's like saying: i'm driving a volvo when the mechanic is asking you for details on what's wrong with your car, just post the gist of y our query Commented Jun 18, 2024 at 8:28
  • 1
    If you use encryption to protect unhashed passwords, you aren't protecting anything. SQL injection or similar attacks on the client will be able to read the cleartext password on the client's side. Always Encrypted is used when you don't trust DBAs or the software between client and database. Protection over the wire is offered by HTTPS. Protection from disk theft is provided by transparent disk encryption (TDE). Commented Jun 18, 2024 at 8:29
Related questions
4343
How do I UPDATE from a SELECT in SQL Server?
3327
How to add a column with a default value to an existing table in SQL Server?
0
Using Always Encrypted Inside STORED PROCEDURE SQL SERVER
Load 6 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.