SQL Server Alway Encryption -Union issue

Amardeepthi Venkata Siripurapu 26 Reputation points
2021-11-24T08:58:25.883+00:00

Hi Team

I am trying to access data from unencrypted filed and encrypted fields from 2 different tables.
However, I am not able to proceed as one table field is encrypted and the other is not. I tried changing the collation for unencrypted field to match the encrypted ones. But still throwing operand clash error. Could you please suggest how can we fix it or if there is any workaround.

  • Sample Query:

SELECT 'DepartmentID' as FieldName,DeptID COLLATE Latin1_General_BIN2 As Datavalue From EmployeeDetails WHERE EmployeeID=1458
Union
SELECT 'EmployeeNo' as FieldName,EmployeeNumber As Datavalue From Employee WHERE EmployeeID=1458

In the above Query EmployeeNumber is encrypted but DeptID is not encrypted.

  • Error:

Operand type clash: varchar is incompatible with varchar(255) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_XX_DEV', column_encryption_key_database_name = 'XX_DEV') collation_name = 'Latin1_General_BIN2'

Kindly help

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,757 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,554 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101.4K Reputation points MVP
    2021-11-24T22:55:17.98+00:00

    That is not possible. All values in a column in a query must have the same data type. And a column encrypted with Always Encrypted has a special data type which includes the key it was encrypted with.

    Or think of the it this way: the client needs to know which values to decrypt and which to not encrypt. But it only gets the information once, for the entire column.

    You will need to run two queries.

    0 comments
  2. Seeya Xi-MSFT 16,441 Reputation points
    2021-11-25T06:16:52.123+00:00

    Hi @Amardeepthi Venkata Siripurapu ,

    Welcome to Microsoft Q&A!

    Or think of the it this way: the client needs to know which values to decrypt and which to not encrypt. But it only gets the information once, for the entire column.

    Thanks Erland for the detailed explanation.

    I tried changing the collation for unencrypted field to match the encrypted ones.

    This is needed. Because the union needs to be the same type of data.

    Therefore, you must either query separately OR decrypt the column before using the union query(That is, modify the encryption type of the column to Plaintext).

    Best regards,
    Seeya

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments