SQL hashing a password

Question

I am manually importing from old data to MSSQL using SQL statement. when i am hashing from raw text password to MVC 5 passwordHASH field in database.I get funny characters. How do i insert hash to mvc5 identity table?MVC 5 table is use nvarchar also

select HASHBYTES('SHA2_512', 'P@ssw0rd')as beforehash --looks good in result
0x6BFCC4026B5F162799A6DC8305C09DB9C1674AC616BD5C7422A45FBB6D0816AC163047C47A1F426F4F4C6B5B5042C671EABC4FDC7310FD5B183EEF59DC274604

declare @afterhash nvarchar(128)
set @afterhash=HASHBYTES('SHA2_512', 'P@ssw0rd')
select @afterhash as afterhash --funny character if put inside a variable or insert to data base

ﱫ˄彫✖ꚙ菜쀅릝柁왊봖瑜ꐢ뭟࡭갖〖쑇ὺ潂䱏孫䉐燆볪�ၳ寽㸘姯⟜ц

Solution

hashbytes returns a varbinary data type, which you are trying to insert into an nvarchar variable.

If you don't want to store this value as binary but want the text that you can see currently you have a couple options depending on whether or not you want to keep the 0x binary prefix:

declare @afterhash varbinary(500) = HASHBYTES('SHA2_512', 'P@ssw0rd')

select @afterhash as varbinaryValue
        ,convert(nvarchar(1000), @afterhash, 1) as nvarcharIncludePrefix
        ,convert(nvarchar(1000), @afterhash, 2) as nvarcharExcludePrefix

Output:

varbinaryValue         - 0x6BFCC4026B5F162799A6DC8305C09DB9C1674AC616BD5C7422A45FBB6D0816AC163047C47A1F426F4F4C6B5B5042C671EABC4FDC7310FD5B183EEF59DC274604
nvarcharIncludePrefix  - 0x6BFCC4026B5F162799A6DC8305C09DB9C1674AC616BD5C7422A45FBB6D0816AC163047C47A1F426F4F4C6B5B5042C671EABC4FDC7310FD5B183EEF59DC274604
nvarcharExcludePrefix  - 6BFCC4026B5F162799A6DC8305C09DB9C1674AC616BD5C7422A45FBB6D0816AC163047C47A1F426F4F4C6B5B5042C671EABC4FDC7310FD5B183EEF59DC274604

Comments

[Alex K.]

What is the type of the field you want to insert that into? If its binary/varbinary you don't need to convert it at all, if its nvarchar (it probably should not be) and you want the hexadecimal value as a string use CONVERT(NVARCHAR(130), HASHBYTES('SHA2_512', 'P@ssw0rd'), 1) (changing the last argument to 2 to lose the 0x)