What is the recommended practice for storing user passwords in SQL Server 2008?
I am storing user details for an intranet, and would like to get advice on best way to store a user details such as name, password and user access privillages etc. I am thinking of creating a nvarchar column and then encrypt this text before inserting into the table.
There's nothing special about SQL Server; good password security policies are pretty consistent.
that is generally the way to do it.
Your application will handle the encrypting (and optionally decrypting), the database will just store the password.
I recommend using something stronger than the dated defacto - MD5
Most .net developers seem to like using TDES
T-Sql includes encryption functions - 4Guys had a good article on it for SQL Server 2005 aa while back but I think it all still applies to 2008.
Encryption for sensitive data is good. However, with passwords, you should never need to know the original value, and since anything which is encrypted can also be decrypted, you put that information at jeopardy of being discovered.
Instead, you should keep a hash of the password. This process takes the value and generates what amounts to a very complicated checksum. Given the number, there's no way to go back to the original password, which increases the security of such information. When you want to know whether someone has given you the correct password, you hash the value they gave you and compare the hashes.
Security is a complicated topic. Even with hashes, you can end up having a system that has significant security flaws. Getting the help of a security consultant is not a bad idea if nobody else on your team already has that kind of knowledge.
The usual way to store password, is to use a hash function on the password, but to salt it beforehand. It is important to "salt" the password, to defend oneself against rainbow table attacks.
So your table should look something like that
._______._________________.______________.
|user_id|hash |salt |
|-------|-----------------|--------------|
|12 |adsgasdg@g4wea...|13%!#tQ!#3t...|
| |... |... |
When checking if a given password matches a user, you should concatenate the salt to the given password, and calculate the hash function of the result string. If the hash function output matches the hash column - it is the correct password.
It is important to understand however that the salt-hash idea has a specific reason -- to prevent anyone with access to the database from knowing anyone password (it is considered difficult problem to reverse a hash function output). So for example, the DBA of the bank, wouldn't be able to log-in to your bank account, even if he has access to all columns.
You should also consider using it if you think your users will use a sensitive password (for example their password for their gmail account) as a password to your website.
IMHO it is not always a security feature which is needed. So you should think whether or not you want it.
See this article for a good summary of this mechanism.