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 and query performance

I am trying to implement encryption but have run into some serious performance issues. I am required to encrypt the SSN in our database. In and of itself, this is not a problem. The problem comes in because there is also a need to be able to query the table based on the SSN. Since the SSN is encrypted, the query basically performs an index scan, decrypting each value as it goes along. As a result, the query for one record out of 10 million records in the table takes three minutes. It needs to occur immediately.

If I could encrypt my SSN parameter and then compare it to the encrypted value in the column, it would work fine. Unfortunately, everytime you encrypt a particular value, the resultant encrypted value is different. Hence, I have to decrypt the column to match my parameter, instead of encrypting th parameter to match the column.

Does anyone have any suggestions to help alleviate this problem?

Thanks,

See the following links - they discuss this issue in detail:

http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspx

http://blogs.msdn.com/raulga/archive/2006/03/11/549754.aspx

Thanks
Laurentiu

|||

Laurentiu,

Thanks for the links. They supplied the information that I was afraid I'd find. In the example of using the MAC key, is there a benefit of having one column containing the encrypted SSN, if another column contains the MAC of the plaintext? That is, if the MAC of the plaintext is secure enough to be able to use for indexing, then are things more secure by separately encrypting the SSN with a symmetric key?

Two ideas that were proposed for our situation were:

1.) Encrypt the SSN and also store, let's say the last four of the SSN in plaintext. Our last four field would be included in the query condition. That would, in effect, reduce the amount of the index searched by a factor of 10000 (e.g search and decrypt only 1000 of the 10 million records)

2.) Create a separate table containing the plaintext SSN (indexed) and the encrypted value of the PK to the customer table. The SSN would be plaintext, but would not be linkable to any particular customer. Searches by SSN would use this table to retrieve the one row and then decrypt the encrypted value of the PK to link to the customer table. I'm not sure I like this idea, as the SSN are all visible, regardless of whether they can be linked to a particular person.

What are your thoughts on either of these ideas?

Thanks,

Larry

|||

The HMAC cannot be used to retrieve the original data, hence the encrypted value needs to be stored as well. The HMAC is only used for the search, to leverage the index for retrieving the row. Once the row is retrieved, the data is extracted from the encrypted column value.

I would avoid both ideas, as both are disclosing information about your customers' SSNs. The solution we recommend is to index an HMAC of the SSN, instead of indexing portions of the unencrypted SSN. There is no significant overhead compared to the other two solutions, so why not implement this approach?

Thanks
Laurentiu

|||

Again, thanks for your reply. Your answer makes sense to me. I, too, was not real keen on leaving SSN (or parts of it) in plaintext, but I was running short on other ideas and short on time. I hope you'll forgive my ignorance when it comes to HMAC, actually when it comes to encryption in general. When I looked at Raul's sample code for implementing this solution, I'll have to admit I was rather intimidated by it. I wish I had more time to really digest the whole thing. I will re-read his blog and try to digest it completely. Are there any other places where I might be able to get a crash course on HMAC's?

Thanks,

Larry

|||One other question I have is regarding replication. This table is one that I am replicating to another database. If I create these same certificates and keys on both database, can I still search from database B on an indexed value that was created on database A?|||

Laurentiu,

The example you pointed me to in Raul's blog implements the encryption and the generation of the MAC in a trigger. All of our data access logic (INSERTs and UPDATEs) currently resides in stored procedures. What are benefits or drawbacks to implementing the encryption logic in triggers as opposed to the stored procedures that we already have?

|||

You can generate the HMAC in procedures as well. Raul's example shows how you can compute the HMACs without changing existing stored procedures - this is why he's doing the HMAC computations in a trigger. If changing the stored procedures is not an issue for you, then you can move the HMAC computation in their code.

Also, for HMAC generation, you can find out more by looking at a cryptography book or at online resources. The algorithm that Raul uses is to concatenate the original value (the sensitive data) with a secret value (@.key) and then to compute a SHA1 hash of the resulting concatenation - that is the HMAC of the original value:

SELECT @.RetVal = HashBytes( N'SHA1', convert(varbinary(8000), @.Message) + @.Key )

Thanks

Laurentiu

Encryption and query performance

I am trying to implement encryption but have run into some serious performance issues. I am required to encrypt the SSN in our database. In and of itself, this is not a problem. The problem comes in because there is also a need to be able to query the table based on the SSN. Since the SSN is encrypted, the query basically performs an index scan, decrypting each value as it goes along. As a result, the query for one record out of 10 million records in the table takes three minutes. It needs to occur immediately.

If I could encrypt my SSN parameter and then compare it to the encrypted value in the column, it would work fine. Unfortunately, everytime you encrypt a particular value, the resultant encrypted value is different. Hence, I have to decrypt the column to match my parameter, instead of encrypting th parameter to match the column.

Does anyone have any suggestions to help alleviate this problem?

Thanks,

See the following links - they discuss this issue in detail:

http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspx

http://blogs.msdn.com/raulga/archive/2006/03/11/549754.aspx

Thanks
Laurentiu

|||

Laurentiu,

Thanks for the links. They supplied the information that I was afraid I'd find. In the example of using the MAC key, is there a benefit of having one column containing the encrypted SSN, if another column contains the MAC of the plaintext? That is, if the MAC of the plaintext is secure enough to be able to use for indexing, then are things more secure by separately encrypting the SSN with a symmetric key?

Two ideas that were proposed for our situation were:

1.) Encrypt the SSN and also store, let's say the last four of the SSN in plaintext. Our last four field would be included in the query condition. That would, in effect, reduce the amount of the index searched by a factor of 10000 (e.g search and decrypt only 1000 of the 10 million records)

2.) Create a separate table containing the plaintext SSN (indexed) and the encrypted value of the PK to the customer table. The SSN would be plaintext, but would not be linkable to any particular customer. Searches by SSN would use this table to retrieve the one row and then decrypt the encrypted value of the PK to link to the customer table. I'm not sure I like this idea, as the SSN are all visible, regardless of whether they can be linked to a particular person.

What are your thoughts on either of these ideas?

Thanks,

Larry

|||

The HMAC cannot be used to retrieve the original data, hence the encrypted value needs to be stored as well. The HMAC is only used for the search, to leverage the index for retrieving the row. Once the row is retrieved, the data is extracted from the encrypted column value.

I would avoid both ideas, as both are disclosing information about your customers' SSNs. The solution we recommend is to index an HMAC of the SSN, instead of indexing portions of the unencrypted SSN. There is no significant overhead compared to the other two solutions, so why not implement this approach?

Thanks
Laurentiu

|||

Again, thanks for your reply. Your answer makes sense to me. I, too, was not real keen on leaving SSN (or parts of it) in plaintext, but I was running short on other ideas and short on time. I hope you'll forgive my ignorance when it comes to HMAC, actually when it comes to encryption in general. When I looked at Raul's sample code for implementing this solution, I'll have to admit I was rather intimidated by it. I wish I had more time to really digest the whole thing. I will re-read his blog and try to digest it completely. Are there any other places where I might be able to get a crash course on HMAC's?

Thanks,

Larry

|||One other question I have is regarding replication. This table is one that I am replicating to another database. If I create these same certificates and keys on both database, can I still search from database B on an indexed value that was created on database A?|||

Laurentiu,

The example you pointed me to in Raul's blog implements the encryption and the generation of the MAC in a trigger. All of our data access logic (INSERTs and UPDATEs) currently resides in stored procedures. What are benefits or drawbacks to implementing the encryption logic in triggers as opposed to the stored procedures that we already have?

|||

You can generate the HMAC in procedures as well. Raul's example shows how you can compute the HMACs without changing existing stored procedures - this is why he's doing the HMAC computations in a trigger. If changing the stored procedures is not an issue for you, then you can move the HMAC computation in their code.

Also, for HMAC generation, you can find out more by looking at a cryptography book or at online resources. The algorithm that Raul uses is to concatenate the original value (the sensitive data) with a secret value (@.key) and then to compute a SHA1 hash of the resulting concatenation - that is the HMAC of the original value:

SELECT @.RetVal = HashBytes( N'SHA1', convert(varbinary(8000), @.Message) + @.Key )

Thanks

Laurentiu

Encrypting data across replication

We are looking at setting up peer-to-peer transactional replication between two databases. We have a customer requirement to encrypt the SSN in this database. I have configured replication successfully. I have also successfully encrypted the SSN using a symmetric key (with encryption by certificate). What I haven't done yet is set up encryption to work across a replication topology.

What steps would I have to follow in order to be able to encrypt the SSN on one server, replicate it to the subscriber, and then decrypt the SSN on the subscriber? For this scenario, is there a better way to handle encryption other than a symmetric key encrypted by a certificate?

The most important thing is to set up your encryption key so that you can manually recreate it on the subscriber. The setup must be manual, because replication will only work for the content of the tables, not for the keys used to encrypt that content. Have a look at this post for additional information: http://blogs.msdn.com/lcris/archive/2006/07/06/658364.aspx.

Thanks
Laurentiu

|||As a followup question, I have one field -- SSN -- that appears in several tables. I also have another field that needs to be encrypted. Is there any "rule of thumb" to use when deciding whether to create separate certificates/symmetric keys for each of these fields?

Encrypting data across replication

We are looking at setting up peer-to-peer transactional replication between two databases. We have a customer requirement to encrypt the SSN in this database. I have configured replication successfully. I have also successfully encrypted the SSN using a symmetric key (with encryption by certificate). What I haven't done yet is set up encryption to work across a replication topology.

What steps would I have to follow in order to be able to encrypt the SSN on one server, replicate it to the subscriber, and then decrypt the SSN on the subscriber? For this scenario, is there a better way to handle encryption other than a symmetric key encrypted by a certificate?

The most important thing is to set up your encryption key so that you can manually recreate it on the subscriber. The setup must be manual, because replication will only work for the content of the tables, not for the keys used to encrypt that content. Have a look at this post for additional information: http://blogs.msdn.com/lcris/archive/2006/07/06/658364.aspx.

Thanks
Laurentiu

|||As a followup question, I have one field -- SSN -- that appears in several tables. I also have another field that needs to be encrypted. Is there any "rule of thumb" to use when deciding whether to create separate certificates/symmetric keys for each of these fields?

encrypting a field in SQL Server 2000

Hi,

I have a SSN field that I need to encrypt. Only persons who wants to get information about a user should be able to decrypt that field. What is the best way to encrypt and decrypt the ssn field? Thanks

Maybe you can write a UDF which uses some encryption algorithm to generate the encrypted data from SSN and something known by only the user and DBA (something like password salt). However there're some available softwares, for example:

http://www.appsecinc.com/products/dbencrypt/mssql/

orhttp://www.activecrypt.com/products.html

|||

I wouldn't use SQL Server to do the encrypt/decrypt for a number of reasons (It's unencrypted when sent from the web server to the database server, it's viewable with trace utilities like SQL Profiler, and you would either need to store the encrption key in the database unencrypted, or send it and then the encryption/decryption key is sent unencrypted).

I would look into the encryption stuff that .NET has built in to start.

Encrypt Data Issue

Does SQL Server 2000 provide any data encryption/decryption functionality so that certain fields (e.g. SSN, Age and Salary) will be encrypted before writing into the table and decrypted once loading out of the table?
J827Look up encryption in Books Online

Using Encryption Methods
Encryption is a method for keeping sensitive information confidential by changing data into an unreadable form. Encryption ensures that data remains secure by keeping the information hidden from everyone, even if the encrypted data is viewed directly. Decryption is the process of changing encrypted data back into its original form so it can be viewed by authorized users.

Microsoft SQL Server encrypts or can encrypt:

Login and application role passwords stored in SQL Server.

Any data sent between the client and the server as network packets.

Stored procedure definitions.

User-defined function definitions.

View definitions.

Trigger definitions.

Default definitions.

Rule definitions.