1

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?

3
  • If the encryption is deterministic then duplicate values should also produce duplicate encrypted values? – George.Palacios Sep 4 '19 at 11:08
  • yes they have duplicate value but how could i store it on a temporary table – Nathan Sep 4 '19 at 11:13
  • 2
    Can you please provide the columns you're working with and your desired output? – George.Palacios Sep 4 '19 at 11:14
2

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.

2
  • Don’t know why this was downvoted, it is the correct answer – Gaius Sep 4 '19 at 11:38
  • @Gaius the question is "how" not "is it possible" as far as I can tell - although the OP does leave a lot of information out so I could be wrong there. If the question is "how" then this doesn't answer the question. – George.Palacios Sep 4 '19 at 12:38

Not the answer you're looking for? Browse other questions tagged or ask your own question.