Encryption w/ not in

Last Post 28 Dec 2007 08:05 AM by SQLUSA. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
bretaylor
New Member
New Member

--
28 Dec 2007 07:48 AM
I am trying to store encrypted ssn, but when doing a compare (not in), it always proves falese. So the script inserts 3000 on the first run, then inserts the same 3000 on the second run. Please help

Here is my code....
OPEN SYMMETRIC KEY EncryptionKey01
DECRYPTION BY CERTIFICATE EncryptionCertificate;

insert xxxx_datamart.dbo.xx_Member
(eid1)
Select distinct EncryptByKey(Key_GUID('EncryptionKey01'), cast(SSNDecrypt as varchar(255)))
from xxxx_datamart_stage.dbo.xx_Detext_MemberValidation
where SSNDecrypt is not null
and EncryptByKey(Key_GUID('EncryptionKey01'), cast(SSNDecrypt as varchar(255)))
not in (select eid1 from xxxx_datamart.dbo.xx_Member)


Note: eid1 has unique index
SQLUSA
New Member
New Member

--
28 Dec 2007 08:05 AM
>and EncryptByKey(Key_GUID('EncryptionKey01'), cast(SSNDecrypt as varchar(255)))
not in (select eid1 from xxxx_datamart.dbo.xx_Member)


You cannot do this. The encryption produces different strings at subsequent application.

You have to get the clear text and compare against the clear text.

Cypher text is not good for comparison.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/highperformance2005/ The Best SQL Server 2005 Training in the World!
bretaylor
New Member
New Member

--
28 Dec 2007 08:25 AM
So I am not going insane. thanks!!!!!
SQLUSA
New Member
New Member

--
28 Dec 2007 10:22 AM
The only way to search on a cypher text column is decypher each cell until you hit your target.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/highperformance2005/ The Best SQL Server 2005 Training in the World!


Acceptable Use Policy
---