Re: SQL 2005 - Searching Encrypted SSN

That was the kicker. Unless you can specify your own salt and IV (and I
don't have SQL 2K5 installed here at work, so I can't test it), you'll
either:

1) have to perform decryption on every column and compare,
2) write your own encryption/decryption functions (not too difficult with a
SQLCLR hosted .NET System.Security.Cryptography namespace), or 3) Store a
hash of the encrypted data. If you only need the SSN for identification
purposes, and not for reporting purposes, you could probably hash the SSN
and forget about encryption altogether.

BTW, according to BOL, SQL 2005 does not salt the encryption performed with
RC4 and RC4_128.

"Ron Brent" <RonBrent@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:82A6C044-195A-403A-8BD5-E9CF2F163035@xxxxxxxxxxxxxxxx
Hi Mike,

According to Laurentiu's blog -
http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspx -
"The encryption algortihms in SQL Server 2005 are salted. By salting, I
mean
that the encryption algorithms are always using a random initialization
vector (IV), which leads to the following property: encrypting twice the
same
piece of data using the same key will produce two different ciphertexts."

You say that theoretically I should be able to encrypt the SSN I'm
searching
for and compare that to the already-encrypted columns.
How would the second encryption (that is done within the query) produce
identical cipher text results to the first encryption given the fact that
the
encryption algorithm uses a random initialization vector?

Thanks,

Ron

"Mike C#" wrote:


"Ron Brent" <RonBrent@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1929F115-F507-4448-9AD7-1F900B9A1F3F@xxxxxxxxxxxxxxxx
Thanks Uri!

It will be helpful once we decide to implement the encryption, but my
question is this -
is it possible to search an encrypted column the way it's described in
the
blog and get the results quickly (at the same speed it takes to search
on
a
clear text)?

If you want to search using = in the WHERE clause, you can get comparable
speed. Just don't decrypt the SSN's in the column. Theoretically you
should be able to encrypt the SSN you're searching for and compare that
to
the already-encrypted columns. Of course this won't work if your column
looks like this:

999-99-9999

And you want to search on last 4 digits or something similar. In that
case
you need to decrypt and compare row-by-row (unless you split the SSN up
into
separate columns...).





.

Relevant Pages

  • Re: how to decrypt an encrypted stored proc in 2005
    ... SQL 2005 still obfuscates object encryption in a similar manner to SQL 2000. ... tinyint,@procNameLength int ... SELECT @BasePos = 1 ...
    (microsoft.public.sqlserver.security)
  • Re: how to decrypt an encrypted stored proc in 2005
    ... remove the feature for the same reason. ... Using a stronger encryption algorithm ... So the feature is unchanged in SQL 2005 from SQL 2000. ... SELECT @BasePos = 1 ...
    (microsoft.public.sqlserver.security)
  • Re: Encryption of application configuration block
    ... If your trying to protect an Sql login password then instead create an Sql ... access to Sql Server objects based on the current users' Windows account. ... If your really must encrypt your connection strings here are some links, ... I come to secure the app.config file via encryption. ...
    (microsoft.public.dotnet.general)
  • Re: Connection to SQL Server CE Windows Service via C# - Error 250
    ... This is probably 3rd time I am hereing to a case the encryption related ... In SQL CE v3.1 case, can you try using Crypto API under the same credentials ... specific to your database or if it's really the password itself. ... connect to it via the windows service under the Local Service ...
    (microsoft.public.sqlserver.ce)
  • Re: how to decrypt an encrypted stored proc in 2005
    ... Everyone that cares needs to realize that object "encryption" in SQL 2005 is ... tinyint,@procNameLength int ... SELECT @BasePos = 1 ...
    (microsoft.public.sqlserver.security)