SQL Server 2008 R2 HASHBYTES SHA2 returns null

问题:

I am trying to use HASHBYTES with SHA2_512 as the algo. However when I try to do it in SQL Server Management Studio all that I get is null.

SELECT HASHBYTES('SHA1','test') //works
SELECT HASHBYTES('SHA2','test') //returns null

What am I doing wrong?
Is there a way to view the return value from SELECT HASHBYTES('SHA2', 'test')?

thanks

回答1:

Here a small example with 128, 256 and 512 Bits

DECLARE @HashThis nvarchar(4000);
SELECT @HashThis = CONVERT(nvarchar(4000),'This is a sample string');
SELECT HASHBYTES('SHA1', @HashThis);
SELECT HASHBYTES('SHA2_256', @HashThis);
SELECT HASHBYTES('SHA2_512', @HashThis);
GO

回答2:

SQL Server supports SHA2 512 in SQL Server 2012+.

SQL Server 2008 R2 and below do NOT support SHA2_512. Here's HASHBYTES on MSDN.

回答3:

<algorithm>::= MD2 | MD4 | MD5 | SHA | SHA1 | SHA2_256 | SHA2_512 per https://msdn.microsoft.com/en-us/library/ms174415.aspx

回答4:

SELECT HASHBYTES('SHA2_256','test')  
SELECT HASHBYTES('SHA2_512','test')  

回答5:

It is possible to return a SHA512 hash in SQL Server 2008 if you use a user-defined function (UDF) in CLR. Without including a full explanation of how to do CLR in SQLServer, here are the relevant parts.

First, the C# CLR code:

using System.Text;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Security.Cryptography;

public partial class UserDefinedFunctions
{
  [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
  [return: SqlFacet(MaxSize = -1)]
  public static SqlString hash_string_sha512([SqlFacet(MaxSize = -1)]string Value)
  {
    SHA512Managed crypt = new SHA512Managed();
    string hashString = string.Empty;
    byte[] crypto = crypt.ComputeHash(Encoding.UTF8.GetBytes(Value), 0, Encoding.UTF8.GetByteCount(Value));
    foreach (byte bit in crypto)
    {
      hashString += bit.ToString("x2");
    }
    return hashString;
  }
};

Build your CLR project, which creates a DLL. Now create an assembly in the database for the DLL, and register the function:

create assembly MyCode from '[PATH]\[DLL_Name].dll' with permission_set = external_access
create function hash_string_sha512(@val nvarchar(max)) returns nvarchar(max) as external name MyCode.UserDefinedFunctions.hash_string_sha512

Now you can hash any string:

select dbo.hash_string_sha512('What will this look like as a SHA512 hash?')

Which returns the hash:

42f8373d528cb64cdfa7ec4ffb2d754c7d4c37a28959506ec2413aacfe17500db7940ffd887390cb543a8615a6000b4f6bcbd199bb56af91bec84780f236aaf8