encrypt sensitive data

How do you encrypt sensitive data that may be stored in a database such as
passwords, creditcard numbers,etc.. ?
3rd party tools ? Internal ..If so how.. ? Does decrypting add latency ..
Any info that you can provide would be valuable. Using SQL 2000Hi
Do it at application level using the Windows API's of if you use .NET, use
the Crypto classes.
SQL server 2005 will support encryption at row level.
Regards
Mike
"Hassan" wrote:

> How do you encrypt sensitive data that may be stored in a database such as
> passwords, creditcard numbers,etc.. ?
> 3rd party tools ? Internal ..If so how.. ? Does decrypting add latency ..
> Any info that you can provide would be valuable. Using SQL 2000
>
>|||Hassan,
If you use passwords for user authentication purposes only (i.e. you do not
need to know their plaintext values, but want to make sure that the user
logging on submits the right password) use hashing instead of encryption.
This way you don't have to deal with key protection.
For values that need to be decrypted (e.g. SSN or credit card number), use
symmetric key encryption, such as Rijndael algorithm, with strong keys.
Alternatively, you can use public-private keys, but the performance can be
bad.
Whether you use hashing or encryption, implement it in C#, C/C++, VB or
whatever your application is written in, not in SQL Server (T-SQL).
Although, I have to say that there are tools which allow encryption directly
in the database (such as nCipher: http://www.ncipher.com/dbe/, netlib:
http://www.netlib.com/, etc), I do not have much experience with them, so I
cannot comment.
SQL Server 2000 (and earlier) has very limited cryptographic features, so
they are of little use. As Mike mentioned, the next version of SQL Server
offers much better cryptographic features, but the main problem I see is
that if you decrypt a value on the database side and pass it to the
application which resides on a different machine (which is a typical case in
most enterprise environments), a simple network sniffer will reveal all your
secrets. That is unless you use SSL between the application server and the
database server, which you can do, but it would require more administrative
hassles and cause performance degradation since all payloads will have to be
encrypted and decrypted.
A couple of notes. If you use encryption in application (not T-SQL), you may
need to store the same value twice: one hashed (without salt), the other
encrypted. Let me illustrate. Say you store SSNs encrypted. Normally, you
would need to use an initialization vector (IV) or a pseudo-IV with
encryption, so depending on the IV value the result of the encryption can be
different. In this case, how do you perform a search for a record with a par
ticular SSN? Unless you use the same IV for all records (which kinda defeats
the purpose), you cannot just encrypt the submitted value and run a select
query (SELECT * FROM ... WHERE SSN = <encrypted value> ), because you do not
know which IV to use to generate the encrypted result. In this case, when
storing SSN, you may need to keep two values: a hash of the SSN and an
encrypted value, so in your SELECT statement you can use the hash. (Note:
You can generate hash using SQL Server's PWDENCRYPT function, but then again
you will be passing the value in plain text over the network.)
Finally, when you use encryption, you have to protect the key (or a
passphrase - and other characteristics - from which the key is derived).
Basically, your application "owns" the key, so when the application is
installed on a server, you - or a server admin - must "install" the key in
some way and store it in a secure manner so that the application can
retrieve it and use for encryption and decryption (the idea here is to allow
different key to be used in different environments - development, test,
production - and have a person responsible for the key access: i.e.
developers should not know which key is used in production).
Unfortunately, there are no totally secure ways of doing this and better or
worse options depend on the type of your application. You can use third
party tools (e.g. CipherSafe: http://www.obviex.com/ciphersafe) or implement
your own protection mechanism using DPAPI (which is the best option,
although with some limitations, partially in the context of ASP.NET apps),
or something else. If you want to learn more about this topic, check this
article: "Protect It: Safeguard Database Connection Strings and Other
Sensitive Settings in Your Code" at
[url]http://msdn.microsoft.com/msdnmag/issues/03/11/ProtectYourData/default.aspx.;[/url
]
it covers several related aspects.
And, yes, whichever method you choose, expect encryption and decryption to
cause performance degradation, but depending on the method you choose it can
be anything from negligible to non-acceptable.
Good luck,
Alek
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OJsFSrEMFHA.3076@.TK2MSFTNGP14.phx.gbl...
> How do you encrypt sensitive data that may be stored in a database such as
> passwords, creditcard numbers,etc.. ?
> 3rd party tools ? Internal ..If so how.. ? Does decrypting add latency ..
> Any info that you can provide would be valuable. Using SQL 2000
>