[dba-SQLServer] Encryption & SQL Server 2005

Thanks for your response Francisco.  I'm totally sidetracked from this
right now, but I will probably get back to it next week or so.  I'll
have the hardware types check for differences in set ups.  

Thanks,   


Liz   




-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
Francisco Tapia
Sent: Wednesday, May 28, 2008 5:49 PM
To: Discussion concerning MS SQL Server
Subject: Re: [dba-SQLServer] Encryption & SQL Server 2005

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...
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com