SQL SERVER – Best Practices for Securely Storing Passwords

Plaintext passwords provide the keys to your SQL Server kingdom. A database compromised by weak password practices leaves the entire organization vulnerable. This post will explore industry-standard techniques to securely store passwords in SQL Server without plaintexts. Let us learn about Best Practices for Securely Storing Passwords.

The Dangers of Plaintext Passwords

Saving user passwords in plain readable format is all too common yet extremely dangerous. Developers often take shortcuts to enable login while underestimating the risks.

But plaintext passwords open the door to catastrophe in case of a breach:

• Anyone gaining database access can immediately use the passwords to impersonate users and escalate privileges.
• Plaintexts allow connecting remotely or to other databases using the same credentials.
• Passwords revealing real names, dates, or dictionary words empower credential stuffing and guessing attacks.
• Exposed passwords often get reused elsewhere, compounding the damage.

Plaintext passwords provide zero protection even with firewalls and network security in place. The only solution is robust cryptographic password hashing.

Database Password Hashing in SQL Server

Hashing is a one-way encryption that cannot be decrypted. SQL Server provides cryptographic hash functions like SHA2_512 and BCRYPT to hash passwords.

Here is an example of inserting a hashed password into a table:

INSERT INTO Users(username, password_hash)
VALUES('johndoe', HASHBYTES('SHA2_512', 'supersecretpassword'));

The password is hashed via SHA2_512 before being stored in the database. The same user can then be authenticated by hashing the entered password and comparing it:

SELECT username
FROM Users
WHERE password_hash = HASHBYTES('SHA2_512', 'enteredpassword');

If the hashes match, the passwords are the same. The plaintext is never stored.

Choosing a Password Hashing Algorithm

SQL Server includes several hash options, including MD5, SHA1, SHA2_256, SHA2_512, and BCRYPT.

Current best practices recommend using computationally intensive algorithms like BCRYPT or PBKDF2, including a salt and many iterations.

Slower hashing protects against brute force attacks even with rainbow tables. BCRYPT, in particular, enables adjusting the work factor as hardware gets faster.

Upgrading Old Password Hashes

If your database contains legacy plaintext or weaker (eg. MD5) password hashes, they should be upgraded to stronger algorithms. However, you cannot just hash the existing hash – you need to rehash the original password.

One method is to require all users to reset their passwords, and apply the new hashing algorithm when they set a new password.

Alternatively, you can add a column to track the hashing algorithm used:

ALTER TABLE Users ADD password_algorithm varchar(10);
UPDATE Users
SET password_algorithm = 'MD5'
WHERE password_hash NOT LIKE '%SHA2_512%';

Now when validating passwords, you can check the algorithm used and hash accordingly:

SELECT username
FROM Users
WHERE
(password_algorithm = 'SHA2_512' 
AND password_hash = HASHBYTES('SHA2_512', @enteredpassword))
OR
(password_algorithm = 'MD5' 
AND password_hash = HASHBYTES('SHA2_512', HASHBYTES('MD5', @enteredpassword)))

This allows transparently upgrading old hashes while retaining the ability to validate passwords. The password_algorithm column tracks the algorithm each password hash used.

Upgrading hashes by rehashing passwords is more secure than just hashing the hash. Thank you to the Ivan Yakimov commenter for catching this critical issue!

With the full hashed value, you can quickly rehash it into a more robust algorithm. This upgrades all users to SHA2_512 in this example.

Enforcing Complex Passwords

Using salt and slow hashes helps even with weak passwords. But requiring password complexity via patterns (eg. min length, special chars required) provides additional protection.

Check new passwords against regex patterns or use built-in .NET libraries before hashing and storing to enforce complexity policies.

Conclusion

Storing hashed passwords instead of plaintexts provides monumental benefits for securing your SQL Server environment against intruders.

With slowed hashing algorithms like BCRYPT and enforced password policies, password hashing virtually eliminates common attacks like password dumping.

What password storage practices do you follow for your SQL Server databases? Share your experiences, and let’s raise the bar for password security!

You may follow me here: X (twitter)

Reference: Pinal Dave (https://blog.sqlauthority.com)