CODEWITHSUNDEEP
c#asp.net.netsql-serverencryption
Hossam Barakat
Hossam Barakat

Reputation: 1397

SQL Server Consistent Column Encryption

I have a fairly simple SQL Server requirements where I have a column of type int and I want to encrypt that column given that the function that will be used to encrypt the column should generate the same value each time it is called.

Context

  1. I have system with 2 user roles Source Role, Target Role
  2. The Source Role users generate tables with the following structure ID => int metadata columns => varchar
  3. My system will generate copy of the source table with obfuscated ID
  4. The Target role users will have access to the generated table and can add/update the metadata columns however they shouldn't know the clear ID

My Trials

  1. I have tried ENCRYPTBYKEY with authenticator and without authenticator however each time I call the function,It generates different value which could be beneficial in some scenarios however mine is not one of them :)
  2. Also I have tried using ENCRYPTBYASYMKEY and the same behavior appeared which is different output with each call
  3. I have also found this forum post that is mentioning the SQL Server is using random initialization vector

My question is, What are the possible solutions to achieve my requirements considering that the function will be used to encrypt tables with millions of records.

Upvotes: 1

Views: 154

Answers (2)

oleksii
oleksii

Reputation: 35925

What are the possible solutions to achieve my requirements

There is none.

TLDR;

Modern encryption must satisfy several properties, one of each is Semantic security. Semantic security in layman's terms means that if you have encrypted messages you cannot derive any additional information about the plain text message. You requirements, however, directly contradict this, as by having access to enough cyphered messages, one can derive the meaning of the plain text. This is how German machine Enigma was cracked during 2-nd World War (they reused the key, whereas you want to fix IV. The result is the same though - plain text can be revealed).

For this very purpose Initialisation Vectors have been invented and must be used.

What shall you do? Well there are two options really:

  • Clarify your requirements
  • Use a map which does not imply semantic security, e.g. (1 -> 9478, 2 -> 9572, etc). This option is useful if you just trying to obfuscate things like ID, e.g. you don't want to make it obvious how many products a company has for sale.

Upvotes: 1

level_zebra
level_zebra

Reputation: 1533

You would have to write your own encryption functionality to do this, why would you want to do this though, imo could be a security risk

Upvotes: 0

Related Questions