storing passwords in SQL Server

Question

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.

Answer 1

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.

Update: It is worth mentioning, that for extra security against targeted attack for reversing individual password's hash, you should use bcrypt, which can be arbitrarily hard to compute. (But unless you're really afraid from mysterious man in black targeting your specific database, I think sha1 is good enough. I wouldn't introduce another dependency for my project for this extra security. That said, there's no reason not to use sha1 100 times, which would give a similar effect).