1

I have a requirement to encrypt the customer's membership number in our database. So okay, we're using MS SQL Server, I figured I use encryptbypassphrase, change every place that writes this field to encrypt and every place that reads it to decrypt, etc.

Except ... there are places in the DB where the field is used to link multiple records. I was thinking no problem, I just change the join from "join blahblah on a.member_number=b.member_number", to "join blahblah on a.member_number_encrypted=b.member_number_encrypted". But this doesn't work, because I discovered that if I encrypt the same value twice, I don't get the same encrypted value. I guess it's adding some salt in there or something. And I don't want to say "join blahblah on decryptbypassphrase(@pw, a.member_number_encrypted)=decryptbypassphrase(@pw, b.member_number_encrypted), as that would turn an index lookup into a full-file search. I thought it would be ugly to have to index on the much bigger encrypted value, but ...

So ... Is there some way to force encryptbypassphrase to always encrypt the same input to the same output? Or does this mean that it is simply not practical to join on an encrypted field in most cases?

Or am I missing something?

Jay
  • 26,876
  • 10
  • 61
  • 112

1 Answers1

0

What you are looking for is deterministic encryption. You can achieve deterministic encryption by using a hash of plaintext as your IV, thus, you will get the same ciphertext every time you encrypt a given plaintext. Please see this article for reference.

If you are using/can use MS SQL Server 2016 you should look at Always Encrypted feature. Depending on what operations you want to do on your data. Always Encrypted will make your task easier, since it supports deterministic encryption. Few links that will help you.

Getting Started with Always Encrypted
This feature will make it easier to test Always Encrypted without writing an app
Configure Always Encrypted using SQL Server Management Studio
I have briefly explained the security guarantee provided by Always Encrypted here

Community
  • 1
  • 1
Nikhil Vithlani - Microsoft
  • 797
  • 5
  • 13
  • "What you are looking for is deterministic encryption." Yes. " you are using/can use MS SQL Server 2016" Sadly, I'm not. I've got 2014 sp2-gdr. – Jay Mar 13 '17 at 21:22
  • If you use deterministic encryption, there are many attacks against your approach. Please ask such questions in https://crypto.stackexchange.com/. – Emad Van Ben Apr 24 '21 at 08:41