Re: Search encrypted column in SQL 2005http://msdn.microsoft.com/w
- From: Brian <Brian@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sat, 27 Jan 2007 23:16:01 -0800
We are required to encrypt SSN and customer credit card number. The user
will need to perform an exact or partial search on the encrypted SSN or
credit card number. Below select statement can do the job but very slow...
I am still searching the net to see if I can find a solution.
Is there any sample that you can share how the Hashbytes() function work?
Can we do like search on Hashbytes function?
"Erland Sommarskog" wrote:
Brian (Brian@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:.
Can someone provide me a sample of how to search encrypted column? Using
below method seems pretty slow.
select FirstName, LastName,
convert(varchar,decryptbykey(SSN)) as SSN
from PatientTable
WHERE convert(varchar,decryptbykey(SSN)) = '123-45-6789'
Yes, if you encrypt key columns it will be very very slow. There is no
way an index can be used, and the decryption is expensive too.
Take a look at the HashBytes() function. This gives a deterministic
hash of the key value, which is good for quick key lookups. It's of
course less good for security, since an intruder who is looking for a
person with a known SSN easily will find what he is looking for. But
security and performance don't go hand in hand.
The alternative is to use application-specific keys like customer numbers.
Then you can encrypt the SSN fully. But this also means that if the
customer calls in, and don't know his customer number, but only the SSN,
the helpdesk will not be able to find his data.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
- Follow-Ups:
- Re: Search encrypted column in SQL 2005http://msdn.microsoft.com/w
- From: Mike C#
- Re: Search encrypted column in SQL 2005http://msdn.microsoft.com/w
- From: Erland Sommarskog
- Re: Search encrypted column in SQL 2005http://msdn.microsoft.com/w
- From: dineshasanka@xxxxxxxxx
- Re: Search encrypted column in SQL 2005http://msdn.microsoft.com/w
- From: Uri Dimant
- Re: Search encrypted column in SQL 2005http://msdn.microsoft.com/w
- References:
- Re: Search encrypted column in SQL 2005http://msdn.microsoft.com/wn3/a
- From: Erland Sommarskog
- Re: Search encrypted column in SQL 2005http://msdn.microsoft.com/wn3/a
- Prev by Date: Re: SQL Server and SSL
- Next by Date: Re: Search encrypted column in SQL 2005http://msdn.microsoft.com/w
- Previous by thread: Re: Search encrypted column in SQL 2005http://msdn.microsoft.com/wn3/a
- Next by thread: Re: Search encrypted column in SQL 2005http://msdn.microsoft.com/w
- Index(es):
Relevant Pages
|
