Hash Algorithms – How does SQL Server store Passwords?

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Hash Algorithms – How does SQL Server store Passwords?，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3933字，纯文字阅读大概需要6分钟。

内容图文

原文链接：https://sqlity.net/en/2460/sql-password-hash/

https://sqlity.net/en/2460/sql-password-hash/

Introduction

I am often asked how SQL Server protects the passwords for SQL Logins. We know already that the passwords are hashed and that the hashed value can be queried using the sys.sql_logins?catalog view. But, how is this value actually calculated? How do you get from a password to its hash?

SHA_512 with Salt

Since SQL Server 2012, passwords are stored using the SHA_512 hashing algorithm with a 32-bit salt.

SHA_512 is twelve years old and it is starting to show a few signs of ageing. All SHA algorithms are based on multiple rounds. While there has not been a successful attack against the full number of rounds (80), the number of rounds that can be broken is rising. Now, in a real application, you would always use all 80 rounds, and if you do, the algorithm is still secure. However, the rising number of broken rounds means that it likely will be broken within the next few years.

Calculating the Hash Value

The first step to calculate the hash value is to convert the password from NVARCHAR to VARBINARY. Afterwards SQL Server uses a CSPRNG to generate the 32-bit Salt and append it to the converted password. Of this new concatenated VARBINARY value SQL Server calculates the SHA_512 hash. The last step is to concatenate 0x0200, the salt and the calculated hash together to build the stored hash value.
The code below demonstrates this calculation:

DECLARE @pswd NVARCHAR(MAX) = 'APassword'; 
DECLARE @salt VARBINARY(4) = CRYPT_GEN_RANDOM(4);
DECLARE @hash VARBINARY(MAX); 
SET @hash = 0x0200 + @salt + HASHBYTES('SHA2_512', CAST(@pswd AS VARBINARY(MAX)) + @salt);

SELECT @hash AS HashValue, PWDCOMPARE(@pswd,@hash) AS IsPasswordHash;

We can use the PWDCOMPARE?function to confirm that our result is indeed a valid SQL Server password hash:

Other SQL Server Versions

SQL Server versions before SQL Server 2012 used a very similar algorithm. The only difference is that instead of SHA_512, SHA1 was used. The first two bytes of the hash value are a version indicator. For SHA1 hashes, this version number is set to 0x0100. If you upgrade a server from an old version to 2012 or later, the stored passwords are not automatically upgraded. Therefore the PWDCOMPARE function can actually read both versions.

Below is the adapted version of the code to calculate the hash value. It runs on all SQL Server versions from SQL Server 2005 onwards.

DECLARE @pswd NVARCHAR(MAX); SET @pswd = 'APassword'; 
DECLARE @salt VARBINARY(4); SET @salt = CAST(NEWID() AS VARBINARY(4));
DECLARE @hash VARBINARY(MAX); 
SET @hash = 0x0100 + @salt + 
            HASHBYTES('SHA1', CAST(@pswd AS VARBINARY(MAX)) + @salt);

SELECT @hash AS HashValue, PWDCOMPARE(@pswd,@hash) AS IsPasswordHash;

Because the CRYPT_GEN_RANDOM?function was introduced in SQL Server 2008, the above code uses NEWID as an ersatz function. However, that is not really an adequate replacement, so do not use it in your application.

SQL Server 2000

Even SQL Server 2000 used already the SHA1 function to hash the passwords. However, passwords were not case sensitive at that time. The actual hash value therefore contained two hashed versions of the password, one of the unchanged password and one of the all-caps version of the password:

SET @hash = 0x0100 + @salt + 
HASHBYTES('SHA1', CAST(@pswd AS VARBINARY(MAX)) + @salt) + 
HASHBYTES('SHA1', CAST(UPPER(@pswd) AS VARBINARY(MAX)) + @salt);

If you have a an old hash value like that, PWDCOMPARE can still work with it. However, it ignores the all caps version. That means that an upgrade from SQL 2000 to a new version potentially breaks passwords. If you are one of the few people that still have to work with SQL 2000, keep this in mind when you finally upgrade.

Summary

SQL Server stores the passwords for SQL logins as a salted hash value. For this, SQL Server versions 2012 and later use the SHA_512 algorithm and a 32-bit salt.

 

内容总结

以上是互联网集市为您收集整理的Hash Algorithms – How does SQL Server store Passwords?全部内容，希望文章能够帮你解决Hash Algorithms – How does SQL Server store Passwords?所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---