Always Encrypted Data Validation [closed]

Question

Just a question in how could i validate duplicate data's in my database if columns is set to be encrypted(deterministic) in db?

I've tried running the following code, but receive the error below.

declare '@test1' varchar(max) = 'XXXXXX'
select * from testing where t1 = '@test1'

Encryption scheme mismatch for columns/variables '@test1'. The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '4' expects it to be (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AXS_XXX_CBA_HMAC_SHA_XXX', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'XXX') (or weaker).

How can I find the source of this error, or is there another way to determine duplicate rows?

Answer 1

Deterministic encryption will produce the same value as long as the inputs are the same. In database terms it means that:

Queries can perform equality comparison on columns encrypted using deterministic encryption, but no other operations (for example, greater/less than, pattern matching using the LIKE operator, or arithmetical operations).

Source

So yes you can see if you have duplicates. How? By comparing the stored value of one row with the others in the column.

A theoretical example would be like this:

Select EncryptedColumn, COUNT(EncryptedColumn) from yourtable
GROUP BY EncryptedColumn
    HAVING COUNT(EncryptedColumn) > 1

This will tell you which values within your EncryptedColumn are duplicated. If you can provide the table schema a better example can be given.