1

I'm using 'Always Encrypted with Secure Enclaves'. I created an encrypted column that allows null:

    MiddleName NVARCHAR(512) COLLATE Latin1_General_BIN2 ENCRYPTED 
    WITH (COLUMN_ENCRYPTION_KEY = [CEK_Loyal], 
    ENCRYPTION_TYPE = Randomized, 
    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL

In SSMS, I checked the 'Enable Always Encrypted' and provided an Attestation URL in the connection dialog. I also ensured the 'Enable Parameterization for Always Encrypted' is set in the query options. When I run the following query I get an error:

    Select ISNULL(MiddleName, '') from TheTable

The error:

Operand type clash: varchar is incompatible with nvarchar(512) encrypted with
(encryption_type = 'RANDOMIZED',
 encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
 column_encryption_key_name = '{keyname}', 
 column_encryption_key_database_name = '{database}')

I read here: https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-enclaves

Expressions defining computed columns can't perform any computations on enclave-enabled columns using randomized encryption

I understand the overhead involved with computed columns but in the case of 'ISNULL', even if you connect to the database without specifying 'always encrypted' (where you can only see the encrypted values), the value in the column that is null is always null, decrypted or not. As it stands, in order to make my applications usable, I need to declare all encrypted columns as 'not null'. While this may seem to be a small issue, this is just one of many fields we could allow to be null and when we want to import data from other sources, the importers will need to add an empty string where the fields don't exist in the source data. While we can document this necessity, I can well imagine the people that create the importers will roll their eyes when they find out.

On the flip side, requiring 'not null' will make comparison operations a little easier.

sartoris
  • 816
  • 1
  • 7
  • 21
  • I would imagine because you can't have some rows containing plain text and other rows containing always encrypted values for the same column – Martin Smith Feb 03 '22 at 23:16
  • I don't know why you think my comment indicates that you **can** do that. I am also assuming this isn't possible to have a mix in the same column in a result set either. Or more generally have an expression that may be either non encrypted plain text or encrypted binary – Martin Smith Feb 04 '22 at 00:00
  • dang. I must have misread that... – sartoris Feb 05 '22 at 01:47

1 Answers1

0

Thank you Martin Smith and sartoris posting your discussion as answer to help other community members.

Single column cant have some rows with plain text and some rows with always encrypted values Same thing is applicable for result set as well.

Have an expression that may be either non encrypted plain text or encrypted binary

Madhuraj Vadde
  • 1,099
  • 1
  • 5
  • 13