sqlserver joining on columns that were encrypted by passphrase

Question

I have a requirement to encrypt several columns using encrypt by passphrase method. unfortunately these encrypted columns need to be used in joins to one another. and there are many views stored procs etc that I don't want to have to modify.

here is a fiddle of what is beneath if you would like to try it https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=cc71b4969526eb0fedd22de2828abaa3

setup

create table encrypt.empsone(empno varchar(10),empno_encrypted varbinary(100));
create table encrypt.empstwo(empno varchar(10),empno_encrypted varbinary(100));

insert into encrypt.empsone(empno, empno_encrypted )
       values('001', EncryptByPassPhrase('my passphrase','001'))

insert into encrypt.empstwo(empno, empno_encrypted )
       values('001', EncryptByPassPhrase('my passphrase','001'))

everything looks ok

select a.empno,
      CONVERT(varchar,(DECRYPTBYPASSPHRASE('my passphrase', a.empno_encrypted))) from encrypt.empsone a

empno      no name
  001        001

but the join does not work

select * from encrypt.empsone a join encrypt.empstwo b
on a.empno_encrypted = b.empno_encrypted

I am assuming because the encrypted values are not identical is there an option in encrypt by passcode to achieve this? I am hopeful I do not need to rewrite all of the joins to use the decryptbypassphrase function

Why would you need to encrypt the keys? They should be completely meaningless out of context. — PM 77-1, Mar 01 '19 at 19:46
they are foreign keys not primary keys. and yes they are completely meaningless but I was told they need to be encrypted. no idea why — Bryan Dellinger, Mar 01 '19 at 19:47
Foreign keys in one table are primary keys (or their part) in some other tables. — PM 77-1, Mar 01 '19 at 19:48
You want to change one static value for another. Why would it be any safer if `001` becomes `998`? — PM 77-1, Mar 01 '19 at 19:51
The encryption is case sensitive. Meaning that key, Key and KEY will produce three distinct encrypted values. My guess is that is what is happening here. But something seems tragically flawed with the design if you need to join tables on values that need to be encrypted. — Sean Lange, Mar 01 '19 at 19:52
again I've argued the logic of this decision to no avail. this is my requirement — Bryan Dellinger, Mar 01 '19 at 19:52
I understand it is your requirement. But my point about case sensitivity is almost certainly the issue here. I would push back at least a little bit and either fix the design or don't encrypt data that doesn't need to be. — Sean Lange, Mar 01 '19 at 19:54
so what they would like encrypted is basically an employee id like 00034587 so to my thinking out of context it is totally meaningless. I will once again try to persuade the security team this is not necessary but... — Bryan Dellinger, Mar 01 '19 at 20:07
OK something that is all numbers should encrypt to the same value. But also the datatype can come into play here. Are you 100% certain the datatype is exactly the same in every table? Is it possible that in some tables it was treated as an int before being encrypted? That would mean the two input values are different. There are just so many possibilities here. — Sean Lange, Mar 01 '19 at 20:11
If this is an audit requirement, you might as well go with SQL Server's Always Encrypted feature, but you'll need 2016. — alans, Mar 01 '19 at 20:40
@alans thanks for the Always Encrypted feature, that looks like the way to go — Bryan Dellinger, Mar 04 '19 at 13:42

score 0 · Answer 1 · answered Mar 01 '19 at 22:07

Oh dear I just realized you are using EncryptByPassPhrase. Every time it runs it generates a new symmetric key. As such the output cipher will be different everytime you run this.

Take a look.

select EncryptByPassPhrase('my passphrase','001')
    ,  EncryptByPassPhrase('my passphrase','001')

To accomplish what you are trying to do requires you to decrypt the value which will give you the binary of the value you started with.

select * 
    , decryptbypassphrase('my passphrase', a.empno_encrypted)
    , decryptbypassphrase('my passphrase', b.empno_encrypted)
from empsone a 
join empstwo b on decryptbypassphrase('my passphrase', a.empno_encrypted) = decryptbypassphrase('my passphrase', b.empno_encrypted)

And if you need to get the original value back you simply cast the result of decryptbypassphrase to the original datatype.

select * 
    , convert(varchar(10), decryptbypassphrase('my passphrase', a.empno_encrypted))
    , convert(varchar(10), decryptbypassphrase('my passphrase', b.empno_encrypted))
from empsone a 
join empstwo b on decryptbypassphrase('my passphrase', a.empno_encrypted) = decryptbypassphrase('my passphrase', b.empno_encrypted)

Yeah trying to avoid that so i dont have to change all of the views stored procs — Bryan Dellinger, Mar 01 '19 at 23:20
yeah I think I am going to try the always encrypted feature, seems to be working well. I used deterministic encryption so the views stored procs still work. and the .net entity framework supports it so the web application can still display and manipulate the data — Bryan Dellinger, Mar 04 '19 at 15:10

sqlserver joining on columns that were encrypted by passphrase

1 Answers1