Encrypting Data in SQL 2000

What is the recommended way to store data in SQL Server 2000 encrypted so
not even DBA can see the values? I know SQL 2005 has encryption at the row
level, but what are my options in SQL 2000?
Thank you,
SteveVery limited.
If using a .NET applicaiton, there is a rich encryption library that could
be used at the application level to encrypt data before it is stored in the
server, and decrypt it after if is retreived.
And there are several third party 'Add-ins' to allow encryption via extended
procedures.
A very crude 'hiding (and generally ineffective) scheme would be to use one
of the checksum() functions and do a one-way process. You couldn't 'decrypt'
it but you could use a comparison to verify if an input value matches.
As far as I know, the application level method is the only way to keep a
determined DBA (or Admin) from being able to access the encrypted data AND
the means to decrypt it. (But then, the question is "Is that a wise
decision?, Are you feeling lucky? Go ahead, make my ..."
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Steven" <Lazans@.mskcc.org> wrote in message
news:eDdtTMrpGHA.4812@.TK2MSFTNGP04.phx.gbl...
> What is the recommended way to store data in SQL Server 2000 encrypted so
> not even DBA can see the values? I know SQL 2005 has encryption at the
> row level, but what are my options in SQL 2000?
> Thank you,
> Steve
>|||What about Microsoft's Crypto API?
Steve
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:e8qenSrpGHA.3324@.TK2MSFTNGP05.phx.gbl...
> Very limited.
> If using a .NET applicaiton, there is a rich encryption library that could
> be used at the application level to encrypt data before it is stored in
> the server, and decrypt it after if is retreived.
> And there are several third party 'Add-ins' to allow encryption via
> extended procedures.
> A very crude 'hiding (and generally ineffective) scheme would be to use
> one of the checksum() functions and do a one-way process. You couldn't
> 'decrypt' it but you could use a comparison to verify if an input value
> matches.
> As far as I know, the application level method is the only way to keep a
> determined DBA (or Admin) from being able to access the encrypted data AND
> the means to decrypt it. (But then, the question is "Is that a wise
> decision?, Are you feeling lucky? Go ahead, make my ..."
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "Steven" <Lazans@.mskcc.org> wrote in message
> news:eDdtTMrpGHA.4812@.TK2MSFTNGP04.phx.gbl...
>|||You would need to use some type of third party encryption.
Check the links under field level encryption in the
following FAQ:
http://www.sqlsecurity.com/FAQs/SQL...55/Default.aspx
-Sue
On Thu, 13 Jul 2006 15:32:26 -0400, "Steven"
<Lazans@.mskcc.org> wrote:

>What is the recommended way to store data in SQL Server 2000 encrypted so
>not even DBA can see the values? I know SQL 2005 has encryption at the row
>level, but what are my options in SQL 2000?
>Thank you,
>Steve
>|||Yes, that could be used. (I'm not sure if it's status is still considered
secure...)
You may wish to check the resourses at:
http://www.sqlsecurity.com/FAQs/SQL...55/Default.aspx
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Steven" <Lazans@.mskcc.org> wrote in message
news:ucwNjZrpGHA.3324@.TK2MSFTNGP05.phx.gbl...
> What about Microsoft's Crypto API?
> Steve
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:e8qenSrpGHA.3324@.TK2MSFTNGP05.phx.gbl...
>|||Just to clarify: CryptoAPI is a set of encryption functions available in
Windows that expose standard cryptographic algorithms. .Net encryption
routines offer a higher level interface to CryptoAPI. The encryption in SQL
Server 2005 is also based on CryptoAPI. Any Windows application can use
these routines. For a list of the algorithms available through CryptoAPI,
you can take a look at:
http://msdn.microsoft.com/library/d...
y/alg_id.asp.
Thanks
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:O2IubprpGHA.4268@.TK2MSFTNGP04.phx.gbl...
> Yes, that could be used. (I'm not sure if it's status is still considered
> secure...)
> You may wish to check the resourses at:
> http://www.sqlsecurity.com/FAQs/SQL...55/Default.aspx
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "Steven" <Lazans@.mskcc.org> wrote in message
> news:ucwNjZrpGHA.3324@.TK2MSFTNGP05.phx.gbl...
>|||Thanks Laurentin, for the clarification about CRYPTOAPI library.
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Laurentiu Cristofor [MSFT]" <laur@.nospam.com> wrote in message
news:elpC%23vspGHA.4188@.TK2MSFTNGP04.phx.gbl...
> Just to clarify: CryptoAPI is a set of encryption functions available in
> Windows that expose standard cryptographic algorithms. .Net encryption
> routines offer a higher level interface to CryptoAPI. The encryption in
> SQL Server 2005 is also based on CryptoAPI. Any Windows application can
> use these routines. For a list of the algorithms available through
> CryptoAPI, you can take a look at:
> http://msdn.microsoft.com/library/d...ity/alg_id.asp.
> Thanks
> --
> Laurentiu Cristofor [MSFT]
> Software Design Engineer
> SQL Server Engine
> http://blogs.msdn.com/lcris/
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:O2IubprpGHA.4268@.TK2MSFTNGP04.phx.gbl...
>|||Here are some free XP's that perform column-level encryption using the
CryptoAPI:
http://www.sqlservercentral.com/col...oolkitpart1.asp
"Steven" <Lazans@.mskcc.org> wrote in message
news:ucwNjZrpGHA.3324@.TK2MSFTNGP05.phx.gbl...
> What about Microsoft's Crypto API?
> Steve
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:e8qenSrpGHA.3324@.TK2MSFTNGP05.phx.gbl...
>

Encrypted columns and searches (partial and full)

Hi,

For those implementing encrypted columns, what is the recommended approach when allowing users to also do partial searches on encrypted data? (ie email or creditcard info where the tables contain millions of rows). I understand one cannot have the encryption without performance impact, but the searches can be 10 to 20 times as long as when the info is stored in normal char(20) columns. Just looking as a way to try and lessen the impact.

Thanks

Prompted by your question, I just posted a new entry to my blog about how to enable searches on encrypted data. The entry is at:

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

Please take a look at it and let us know if you have additional questions.

Thanks
Laurentiu