CODEWITHSUNDEEP
sql-servert-sqlpassword-hash
Viktor Fursov
Viktor Fursov

Reputation: 123

SQL Server. DataType for password hash (sha 512)

Wondering which data type to select for my SQL Server to store the sha512 password hash. (hashed value will always have fixed length)

 declare @hashedPasswd varchar(max)
 set @hashedPasswd = convert(varchar(max), HASHBYTES('SHA2_512', 'any password with any langth'), 1)
 select len (@hashedPasswd)

always returns length of 130.

What is the best choice for datatype of the column? Variants are nvarchar(max), nvarchar(130), varchar, char. If I understand correctly, nvarchar is a waste of space in my case, because It will be only ASCII symbols in hashed value.

Please assist.

Upvotes: 0

Views: 5705

Answers (2)

Stu
Stu

Reputation: 32619

According to the documentation, hasbytes returns a varbinary therefore your data type is varbinary.

Your length of 130 is only because you are casting to a varchar and is an inefficient way to store it. From the documentation, sha512 returns 64 bytes, therefore your length required is 64.

 declare @hashedPasswd varbinary(max)
 set @hashedPasswd = HASHBYTES('SHA2_512', 'any password with any length')
 select len (@hashedPasswd)

Upvotes: 1

Stephan
Stephan

Reputation: 36

SHA2_512 is 64 bytes long and internaly a varbinary so I would suggest using this datatype instead. For more safty I also would recommend to use an additional salt for password encryption and decryption. You can find a useful description here: https://www.mssqltips.com/sqlservertip/4037/storing-passwords-in-a-secure-way-in-a-sql-server-database/

Best regards, Stephan

Upvotes: 1

Related Questions