Keywords: SQL Server | Password Hashing | pwdencrypt | Security Mechanism | SHA Algorithm
Abstract: This article provides an in-depth analysis of SQL Server's password hashing mechanism, focusing on the one-way hash characteristics of the pwdencrypt function and its security principles. Through detailed technical implementation explanations, it elucidates why password hashing is irreversible and introduces correct password verification methods. The article also explores the evolution of hashing algorithms across different SQL Server versions, from SHA-1 in SQL Server 2000 to SHA-512 in SQL Server 2012, analyzing modern password security best practices.
Fundamental Principles of Password Hashing
In SQL Server's security architecture, password protection employs one-way hash functions rather than reversible encryption algorithms. When a user executes SELECT pwdencrypt('AAAA'), the system does not encrypt the password but generates an irreversible hash value. This design is based on the core principle of cryptography: hash functions should be easy to compute but difficult to reverse-engineer.
Detailed Analysis of SQL Server Hashing Algorithm
The hashing algorithm used in SQL Server 2000 follows the structure: hashBytes = 0x0100 | fourByteSalt | SHA1(utf16EncodedPassword+fourByteSalt). Using the password "correct horse battery staple" as an example, the system first generates a random salt value 0x9A664D79, then concatenates the UTF-16 encoded password with the salt, and finally applies the SHA-1 hashing algorithm.
The stored password hash consists of three components: version header (0x0100), 4-byte salt value (9A664D79), and 20-byte SHA-1 hash result. This structure can be directly observed in the sys.syslogins system table:
SELECT name, CAST(password AS varbinary(max)) AS PasswordHash FROM sys.syslogins WHERE name = 'sa'
Correct Methods for Password Verification
Due to the one-way nature of hash functions, directly "decrypting" password hash values is theoretically impossible. The correct verification method involves recalculating the hash value of candidate passwords and comparing them with stored values:
SELECT password_field FROM mytable WHERE password_field = pwdencrypt(userEnteredValue)
This approach ensures password verification security because the system does not need to store or transmit plaintext passwords.
Algorithm Evolution and Security Considerations
The hashing algorithm introduced in SQL Server 7 was considered secure in 1999, employing salt mechanisms and appending salt values after passwords, which helps resist rainbow table attacks. However, modern security standards require stronger protection measures.
SQL Server 2012 introduced the more robust SHA-512 algorithm: hashBytes = 0x0200 | fourByteSalt | SHA512(utf16EncodedPassword+fourByteSalt). The new algorithm uses 64-byte hash outputs, significantly improving collision resistance.
Practical Application Recommendations
In practical applications, direct use of undocumented functions like pwdencrypt and pwdcompare should be avoided, as they may change in future SQL Server versions. A better approach is to handle passwords using standard hashing algorithms (such as the SHA-2 family) at the application layer before storing them in the database.
For forgotten password scenarios, system administrators can reset passwords using the ALTER LOGIN command rather than attempting to "recover" original passwords. This method is both secure and aligns with best practices.