encryption optimization

Hi,
I have an application that requires the storing of personal data, name,
address, ssn with a requirement that key fields, first name, last name,
address1, city, zip, ssn be encrypted. Currently, there are over 100,000
records, and growing fast. Searches need to be done based on these key
fields (lastname like 'A%' for instance). I am loading the return set into
SqlDataSource and GridView using .net version 2.0.
I'm using a stored procedure to return the data.
When the page first loads (which filters on lastname = 'A%', it loads
slowly, about 20 seconds), if I change the filter (lastname = 'Q%') it times
out.
Is there a best practice to follow in a case like this that would result in
the best performance possible. I realize with all the encryption that it is
very processing intensive, but with a first load at least usable, but the
second load timing out, there may be some things I should do (clear buffers
or something) that I do not know about doing.
Can you help on this?
Thanks.The bottom line is that you can't efficiently search (use indexes) on these
encrypted key columns because you are searching using the decrypted value
and this value is not stored in the database. During the search, not only
must each value be decrypted, a scan of all table rows is required. This is
a very expensive operation.
For an equality search, you can store a hash in clear text, index the hash
value and add the hash search criteria to your search. This will greatly
reduce the number of qualifying rows and usually result in acceptable
performance. See Laurentiu Cristofor's blog
(http://blogs.msdn.com/lcris/archive.../22/506931.aspx) for a more
complete discussion.
Unfortunately, there is no way to perform efficient wildcard/range searches
on encrypted data. If you must have this functionality, you'll need to take
a different approach like encrypting at the file level (EFS) rather than
column level. I don't know if that's an option in your environment.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gerhard" <acsla@.community.nospam> wrote in message
news:CA33D347-57EC-451F-A3BB-A3931D8CFF65@.microsoft.com...
> Hi,
> I have an application that requires the storing of personal data, name,
> address, ssn with a requirement that key fields, first name, last name,
> address1, city, zip, ssn be encrypted. Currently, there are over 100,000
> records, and growing fast. Searches need to be done based on these key
> fields (lastname like 'A%' for instance). I am loading the return set
> into
> SqlDataSource and GridView using .net version 2.0.
> I'm using a stored procedure to return the data.
> When the page first loads (which filters on lastname = 'A%', it loads
> slowly, about 20 seconds), if I change the filter (lastname = 'Q%') it
> times
> out.
> Is there a best practice to follow in a case like this that would result
> in
> the best performance possible. I realize with all the encryption that it
> is
> very processing intensive, but with a first load at least usable, but the
> second load timing out, there may be some things I should do (clear
> buffers
> or something) that I do not know about doing.
> Can you help on this?
> Thanks.|||Hi Acsla,
I am interested in this issue. Would you mind letting me know the result of
the suggestions?
I noticed that you bind the query result to a Gridview, so I guess that the
loading time (20s and time out) refers to the whole loading process when
all data has been displayed in your GridView. Does the Gridview have a
paging function? According to my experience, if the query result is also
huge, the render time of the control may also spend a long time.
Basically I agree with Dan's suggestions on SQL. Additionally, I would like
your checking how long it will spend if you separately run the SQL
statement in Query Analyzer.
If you need further assistance, feel free to let me know. I will be more
than happy to be of assistance.
Have a great day!
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============sql

Encryption of Data in SQL05

My current experience is with access and sql2000. In my current application, I compress/encrypt my data prior to storing in my database. Does the new verion of sql support compression/encryption and if so can you please point me to any links which discuss.

thanks,

Fred Herring

Encryption is supported in SQL Server 2005, see for example:

http://msdn2.microsoft.com/en-us/library/ms345262(en-US,SQL.90).aspx

I also have a number of postings with examples on my blog:

http://blogs.msdn.com/lcris/archive/category/10357.aspx

If you want to evaluate the encryption features of SQL Server 2005, they're also available in the Express edition, which you can get from here:

http://msdn.microsoft.com/sql/express/

If you need more specific information, this is the right place to post questions.

Thanks
Laurentiu

|||

I have read that encryption is available for SQL 05. I have also attempted encryption on a column.

What are some good examples and practices when using encryption in SQL 05? How and where is the key stored?

How does the key work per column, or one per database?

How do you manage encryption keys?

Where are encryption keys stored and where is a safe place to store them? I don't have a full understanding of how encryption works in SQL 05, so any help would be great- I've read syntax and how to create and use them- but only in an example- I have not used them in practice- I don't understand how to use them in a real world example, or at least for our business.

Any help?

|||

You can find detailed answers to these questions by following these links:

Encryption Hierarchy article in Books Online:
http://msdn2.microsoft.com/en-us/library/ms189586.aspx

My blog with various examples and discussions of encryption functionality:
http://blogs.msdn.com/lcris/archive/category/10357.aspx

If after reading those articles, you still have questions that are unanswered, let us know.

Thanks
Laurentiu

|||Great! Thanks for the blog and the links! I look forward to diving into these!

Encryption and storing credit cards

We need to store credit cards for a limited amount of time because we charge for orders as they ship and occasionally the authorization expires before the item can ship.

I know I can use AES_256 to encrypt our credit cards but I am not sure if all I need to do is follow the directions in the "How-to" for encrypting data.

I created the master key, backed it up off the server the certificate and the symmetric key and I can encrypt and decrypt data.

Is this all I need to do or am I missing something?

Sounds like you have it under control. The 'How to' is nicely put together.

The next step is to test, test, test.

|||I have been testing and keep thinking in the back of my head "This is much too easy. I must be doing something wrong."

I got the encryption working on the second try.|||Microsoft got it right. It really shouldn't be difficult.

encrypting a column

We have a SOX requirment that disallows the storing of passwords as plain
text... they must be encrypted in the database. Does SQL Server handle this
natively?Not in 2000. Use Windows authentication or use the MS crypto API / .NET
crypto classes.
SQL Server 2005 will support native encryption and password management
policies for SQL Server logins.
David Portas
SQL Server MVP
--

encrypt the data of collumn

Hi!
Is there any way to encrypt the data stored in collumn of a table ? actually
we are storing the password of all employees in that table.
Regards,
Swatiswati
Serach on internet for this subject.There were discussions at the forum a
week (or something like that) ago.
Some postes by David Portas and Steve Kass.
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:OFksBWXzEHA.1932@.TK2MSFTNGP09.phx.gbl...
> Hi!
> Is there any way to encrypt the data stored in collumn of a table ?
actually
> we are storing the password of all employees in that table.
> Regards,
> Swati
>
>|||thanks , I got the required link
Regards,
Swati
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OhGjbdXzEHA.2016@.TK2MSFTNGP15.phx.gbl...
> swati
> Serach on internet for this subject.There were discussions at the forum a
> week (or something like that) ago.
> Some postes by David Portas and Steve Kass.
>
>
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:OFksBWXzEHA.1932@.TK2MSFTNGP09.phx.gbl...
> > Hi!
> >
> > Is there any way to encrypt the data stored in collumn of a table ?
> actually
> > we are storing the password of all employees in that table.
> >
> > Regards,
> > Swati
> >
> >
> >
> >
>

encrypt the data of collumn

Hi!
Is there any way to encrypt the data stored in collumn of a table ? actually
we are storing the password of all employees in that table.
Regards,
Swatiswati
Serach on internet for this subject.There were discussions at the forum a
week (or something like that) ago.
Some postes by David Portas and Steve Kass.
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:OFksBWXzEHA.1932@.TK2MSFTNGP09.phx.gbl...
> Hi!
> Is there any way to encrypt the data stored in collumn of a table ?
actually
> we are storing the password of all employees in that table.
> Regards,
> Swati
>
>|||thanks , I got the required link
Regards,
Swati
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OhGjbdXzEHA.2016@.TK2MSFTNGP15.phx.gbl...
> swati
> Serach on internet for this subject.There were discussions at the forum a
> week (or something like that) ago.
> Some postes by David Portas and Steve Kass.
>
>
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:OFksBWXzEHA.1932@.TK2MSFTNGP09.phx.gbl...
> actually
>

encrypt the data of collumn

Hi!
Is there any way to encrypt the data stored in collumn of a table ? actually
we are storing the password of all employees in that table.
Regards,
Swati
swati
Serach on internet for this subject.There were discussions at the forum a
week (or something like that) ago.
Some postes by David Portas and Steve Kass.
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:OFksBWXzEHA.1932@.TK2MSFTNGP09.phx.gbl...
> Hi!
> Is there any way to encrypt the data stored in collumn of a table ?
actually
> we are storing the password of all employees in that table.
> Regards,
> Swati
>
>
|||thanks , I got the required link
Regards,
Swati
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OhGjbdXzEHA.2016@.TK2MSFTNGP15.phx.gbl...
> swati
> Serach on internet for this subject.There were discussions at the forum a
> week (or something like that) ago.
> Some postes by David Portas and Steve Kass.
>
>
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:OFksBWXzEHA.1932@.TK2MSFTNGP09.phx.gbl...
> actually
>