Can't convert a value (not column) from HEX to Base64

Question

I've seen a lot of examples matching the keywords. However those are often for other languages and/or the conversion on values from select, which I haven't been able to adapt to my case.

I'm converting a hashed string in C# to Base64 (previously it was stored as HEX). As the fiddle shows, the HEX'ed result is precisely what the following SQL produces.

declare @Pass as varchar(max) = 'abc'
declare @HexPass as varchar(max) = convert(varchar(max), hashbytes('SHA2_512', @Pass), 2)
select @Pass as Plain, @HexPass as HEX

However, when replacing the old Convert.ToHexString(hash) by the new Convert.ToBase64String(hash) changes the output and I haven't figured out a way to introduce a corresponding conversion in SQL.

string text = "abc";
string expectedResult = "3a81oZNherrMQXNJriBBMRLm+k6JqX6iCp7u5ktV05ohkpkqJ0/BqDa6PCOj/uu9RU1EI2Q86A4qmslPpUyknw==";

byte[] data = Encoding.UTF8.GetBytes(text);
byte[] hash = SHA512.Create().ComputeHash(data);
string actualResult = Convert.ToBase64String(hash);
Console.WriteLine(expectedResult == actualResult ? "equal": "not equal");

Researching it, I stumbled in several instances on a solution relying on xs:base64Binary (like here, here, definitely here etc.).

However, I fail to figure out the syntax of how to follow that applying @HexPass above. I've tried a "gazillion" things, concatenating strings in that schema expression, replacing parts of it etc.

Answer 1

Your C# code is using Encoding.UTF8.GetBytes.

You can use

declare @Pass as nvarchar(max) = N'abc' 

select 
cast('' as xml).value(
    'xs:base64Binary(sql:column("v.col"))', 'varchar(max)'
) as Base64Encoding
from (values(hashbytes('SHA2_512', CAST(@Pass COLLATE Latin1_General_100_CI_AI_SC_UTF8  AS VARCHAR(MAX))))) v(col)

This returns

3a81oZNherrMQXNJriBBMRLm+k6JqX6iCp7u5ktV05ohkpkqJ0/BqDa6PCOj/uu9RU1EI2Q86A4qmslPpUyknw==

The Latin1_General_100_CI_AI_SC_UTF8 doesn't make any difference for abc but can do for other strings