Francisco Tapia
fhtapia at gmail.com
Tue Jun 10 16:56:38 CDT 2008
I did some re-search on encryption and many sites touted that encrypting at
the database level was considered over-kill. It maybe because of the high
level of processing power required just to arrive at the same answer.
Additionally one article that I read made sense in stating that access to
the data should be restricted before you get to the server that way
encrypting the data would not be necessary. This is true you could locate
the column for these accounts and deny access to the general user accounts
and only enable access to those accounts that will need to review the data.
You can further do this if you have your application switch to a specific
Application login ID that allows access, but for all other general tasks
uses the default user logins.
If you need to stick to the encrypted data level then, one method I was
thinking that you could use would be to create a decryption function so that
you could run your search in the following way:
SELECT CallerAccount.CallerID,
CallerAccount.EAccountNumber
FROM CallerAccount
WHERE dbo.myDecryptFunct(CallerAccount.EAccountNumber, 'DecryptKEY') =
'AccountSearch'
This would decrypt only the account number through the search and not have
them available anywhere on the db. Of course this also depends on where
your TempDB is at, and how much ram and processing power you have on your
server.
On Tue, Jun 10, 2008 at 12:16 PM, <Elizabeth.J.Doering at wellsfargo.com>
wrote:
>
> 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
>
>
>
> _______________________________________________
> 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...