When you CAST() short value to NVARCHAR then SQL Server adds some right-padding to the value, and this "some value" is then hashed:

-- CAST the whole value
SELECT HASHBYTES('SHA2_512','Password' + CAST('Salt' AS NVARCHAR(36)))
-- Concatenation w/o CAST
SELECT HASHBYTES('SHA2_512','Password' + 'Salt')
-- Concatenation with manual padding
SELECT HASHBYTES('SHA2_512','Password' + 'Salt' + CAST('' AS NVARCHAR(32)))
       

fiddle

MySQL have soft datatype system, and it simply truncates the padding symbols before thay are provided to the hashing function.

SELECT SHA2(CONCAT('Password', CAST('Salt' AS CHAR(36))), 512);
SELECT SHA2(CONCAT('Password', 'Salt'), 512);
SELECT SHA2(CONCAT('Password', 'Salt', CAST('' AS CHAR(32))), 512);

fiddle

And I cannot find the solution...