[dba-SQLServer] Encryption & SQL Server 2005

Francisco Tapia fhtapia at gmail.com
Wed May 28 17:48:44 CDT 2008


This is a puzzler, a while back I had to create a system that would encrypt
credit card numbers, but I had chosen an outside algorithm so I was not
using the MS encryption routines.  This provided me with two choices for
encrypted data, one where it was all hexidecimal and I was able to easily
use the searched id encrypted in my own sipher, then used the results to
search the database.  I'm going to guess you don't get this luxury, but none
the less I wanted to chime in with what I have done in the past.  What kind
of hardware are you dealing with?

--
Francisco

On Wed, May 28, 2008 at 2:38 PM, <Elizabeth.J.Doering at wellsfargo.com> wrote:

> Dear List,
>
> This ought to be simple:
>
> I have 90000+ records in a table called CallerAccount.  There's also an
> identity primary key, CallerAccountID, and a foreign key CallerID which
> links to the rest of the information about our caller.
>
> Not surprisingly, I need to search for records in the table pertaining
> to one account.  This may actually span several calls, and several
> callers, and many days, so there may be several entries with the same
> account number.
>
> If I were searching cleartext account numbers, this would be a piece of
> cake:  Select * from CallerAccount where AccountNumber = '1234567890'.
>
> Sadly, I am not searching cleartext account numbers.  AccountNumber is a
> sensitive piece of data around here, so it is necessary that it be
> encrypted, hence the table also contains a field EAccountNumber,
> containing the binary encrypted value for AccountNumber.
>
> I could decrypt all the account numbers in the table, write them to a
> new field and search that.  But since 90000+ records is only the very
> beginning of this table, it seemed like it OUGHT to be more sensible to
> encrypt the one account number that I know, using the same certificate
> and key that I have used on the whole table, then search for the
> matching encrypted value.  To make this easy, I wrote the encrypted
> value out to another table.  Then, I thought, I could arrive at the
> records I am interested in with a join like this:
>
>        SELECT        CallerAccount.CallerID,
> CallerAccount.EAccountNumber
>        FROM            CallerAccount INNER JOIN
>                                 tempAccount ON
> CallerAccount.EAccountNumber = tempAccount.tempEAccountNumber
>
> Apparently however, the same data encrypted with the same certificate
> and the same key does not actually turn out to the same binary value
> twice.  It's taken me all afternoon to arrive, kicking and screaming, at
> this conclusion, but I suppose it makes sense.  So I'm at a standstill,
> back at decrypting all the records in the table before searching.
>
> Do any of you have any advice, workarounds, wisdom or comfort for me?
>
> Thanks,
>
>
> Liz
>
>
> Liz Doering
> elizabeth.j.doering at wellsfargo.com
> <mailto:elizabeth.j.doering at wellsfargo.com>
> 612.667.2447
>
> This message may contain confidential and/or privileged information. If
> you are not the addressee or authorized to receive this for the
> addressee, you must not use, copy, disclose, or take any action based on
> this message or any information herein. If you have received this
> message in error, please advise the sender immediately by reply e-mail
> and delete this message. Thank you for your cooperation.
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>


-- 
-Francisco
http://sqlthis.blogspot.com | Tsql and More...



More information about the dba-SQLServer mailing list