Data Encryption: Searching Encrypted Data

Hey there,
I am beginning a new project that will require data to be encrypted and
that data is obviously going to be stored within a Sql 2000 database.
The majority of the data is basically OCR text and will be required to
be searched via a web application via calling a Stored Procedure, with
the rest being Customer Order information. From what I can gather, I
will not be able to store my data encrypted if I want to search it.
Therefore, does anyone have a suggestion on possibly how to go about
this differently, if there even is an option. Anyone have a good
suggested read on how to stored, say credit card information encrypted,
as well as how to search that encrypted data?
Thanks in advance for any suggestions you may have.
XVYou can start by reviewing the encryption section in this
security FAQ. It lists some third party providers that offer
encryption solutions. Some of the sites have white papers on
encryption as well consulting services:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
-Sue
On 26 Jan 2005 10:31:04 -0800, xanthviper@.xanthviper.com
wrote:

>Hey there,
>I am beginning a new project that will require data to be encrypted and
>that data is obviously going to be stored within a Sql 2000 database.
>The majority of the data is basically OCR text and will be required to
>be searched via a web application via calling a Stored Procedure, with
>the rest being Customer Order information. From what I can gather, I
>will not be able to store my data encrypted if I want to search it.
>Therefore, does anyone have a suggestion on possibly how to go about
>this differently, if there even is an option. Anyone have a good
>suggested read on how to stored, say credit card information encrypted,
>as well as how to search that encrypted data?
>Thanks in advance for any suggestions you may have.
>XV|||Thank you Sue. I appreciate your input.

Data encryption and keys

Hi,
I would like to encrypt data in my database. I want encrypted column value to be viewable only for certain group of users. Users that has access to my database doesn't meant they can access to my encrypted data.

Currently, I am using the following "approach" as my key management.

create master key encryption by password= 'MasterKeyPass'

CREATE ASYMMETRIC KEY MyAsymmKey AUTHORIZATION MyUser
WITH ALGORITHM = RSA_1024
ENCRYPTION BY PASSWORD ='MyAsymmPass'

CREATE SYMMETRIC KEY MySymmKey WITH ALGORITHM = DES
ENCRYPTION BY ASYMMETRIC KEY MyAsymmKey

My data will be encrypted using Symmetric key MySymmKey.

User who want to access my data must have MasterKey and MyAsymmKey password.
Is it OK? Any better way?

Thank you

As long as the user you are trying to protect against is not a dbo or sysadmin, you can also use permissions (i.e. "GRANT CONTROL ON ASYMMETRIC KEY :: MyAsymmKey TO user1") to restrict access rather than through passwords. The advantage is the user then doesn't have to depend on memorizing a password and you don't have to pass any password values in which is safer from a security standpoint.

Sung

|||Fyi, Books online links up a section about BACKUP and RESTORING encryption keys http://msdn2.microsoft.com/en-US/library/ms157275.aspx link.

Data Encryption

Hi,

We need to set up a data export process from a SQL DB.

The output (be it XML, Text Files or whatever) needs to be encrypted before it is FTPd somewhere.

Is there support for encrption in SSIS? How / where in the package designer would you achive this?

Thanks in advance.

Martin

There is no built in support for encryption of data. It would be an interesting custom task though if you fancy having a go.

Otherwise, request this for a future enhancement at http://connect.microsoft.com

-Jamie

|||Thanks, shame I was hoping to use it as a lever to kick the upgrade process off from SQL2000.|||

Actually come to think of it - you could leverage .Net's encyption routines quite easily using the script component.

Try doing that!

-Jamie

|||I've been working on an encryption transform actually. We're waiting to get everything in place from corp to release it. If you would like, send an e-mail to jason.gerard at idea.com and I'll let you know when it's ready.

In the meantime, you could use the .NET ecryption API's from inside a Script Transform.