CODEWITHSUNDEEP
sql-serverhashcryptographysnowflake-cloud-data-platform
samba
samba

Reputation: 3101

How to compare hex-encoded strings returned by SHA-256 function in MS SQL and Snowflake?

I'm looking for a hash function in Snowflake and SQL Server that returns the same SHA-256 string value. So I decided to compare hex-encoded strings returned by sha2 (SHA-256) function from Snowflake and HASHBYTES (SHA2_256) from SQL Server. Here are the queries that I've made:

Snowflake:

SELECT sha2(TO_VARCHAR(ABS(12.5)), 256)
Returns: b902cc4550838229a710bfec4c38cbc7eb11082367a409df9135e7f007a96bda

SQL Server:

SELECT CONVERT(VARCHAR(64), HASHBYTES('SHA2_256', CAST(12.5 AS varbinary(64))), 2) as tmp
Returns: 48DCB7B99301A1AA2D8A74637173AD8A3282844983F39ED7AB079A2ADF08FEEA

They appear to return different hash strings. Do these functions indeed have a different underlying implementation or something is wrong with the queries?

I suspect that there might be a mistake in my query to SQL Server.

Upvotes: 3

Views: 1880

Answers (1)

CodeMonkey
CodeMonkey

Reputation: 477

I get the same results in SQL Server and Snowflake with this example:

SQL Server:

SELECT UPPER(master.dbo.fn_varbintohexsubstring(0, HashBytes('SHA2_256', 'Hello World'), 1, 0))
SELECT CONVERT(VARCHAR(MAX), HASHBYTES('SHA2_256','Hello World'), 2);

SELECT UPPER(master.dbo.fn_varbintohexsubstring(0, HashBytes('SHA2_256', '12.5'), 1, 0))
SELECT CONVERT(VARCHAR(MAX), HASHBYTES('SHA2_256','12.5'), 2);

Snowflake:

SELECT sha2(TO_VARCHAR('Hello World'), 256);

SELECT sha2(TO_VARCHAR('12.5'), 256);

RESULTS FOR BOTH:

Hello World: A591A6D40BF420404A011733CFB7B190D62C65BF0BCDA32B57B277D9AD9F146E 
12.5: B902CC4550838229A710BFEC4C38CBC7EB11082367A409DF9135E7F007A96BDA

Upvotes: 4

Related Questions