SQL Server 2008 R2 HASHBYTES SHA2 returns null

hashbytes in sql
sql server hashbytes to string
hashbytes vs checksum
how to decrypt hashbytes in sql server
sha256 in sql server
sql server 2016 sha256
sql server convert hashbytes to bigint
sql hashbytes performance

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

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

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  APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse. Returns the MD2, MD4, MD5, SHA, SHA1, or SHA2 hash of its input in SQL Server. Identifies the hashing algorithm to be used to hash the input. This is a required argument with no default. The single quotation marks are required.

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.

Returns the MD2, MD4, MD5, SHA, SHA1, or SHA2 hash of its input in SQL Server. Topic link icon Transact-SQL Syntax Conventions  SQL Server 2008 R2 HASHBYTES SHA2 devuelve nulo Estoy tratando de usar HASHBYTES con SHA2_512 como el algo. Sin embargo, cuando bash hacerlo en SQL Server Management Studio, todo lo que obtengo es nulo.

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

Microsoft SQL Server has supported the same hashing values from Microsoft SQL Server 2005 to Microsoft SQL Server 2008 R2. return NULL Below is the salient example of storing a password in hash code with SHA2  returns same data size as HASHBYTES, leaving it up to the end user to convert to smaller based on algorithm used. This allows the functions to support future algorithms with larger data returns. With these changes, the functions can now be used in persisted computed columns as they are now marked deterministic when created.

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

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. They are a pain but they are part of a databases method of holding no value in a column. It is upto the programmer to deal with null, not the dba who designed the database.

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

Je suis en train d'utiliser HASHBYTES avec SHA2_512 que l'algo. Cependant lorsque j'essaie de le faire dans SQL Server Management Studio tout ce que je. The following example returns the SHA2_256 hash of the values in column c1 in the table Test1. CREATE TABLE dbo.Test1 (c1 nvarchar(32)); INSERT dbo.Test1 VALUES ('This is a test.'); INSERT dbo.Test1 VALUES ('This is test 2.'); SELECT HASHBYTES('SHA2_256', c1) FROM dbo.Test1; Questo è il set di risultati. Here is the result set.

Encrypting a string using hash code algorithm in sql server using the HASHBYPES function. This is a built-in cryptographic function with hashing algorithms like MD-2, MD-4, MD-5, In these algorithm, SHA-2 (256 and 512) are introduced in SQL Server 2008. Password ] [varbinary](150) NOT NULL. Microsoft SQL Server has supported the same hashing values from Microsoft SQL Server 2005 to Microsoft SQL Server 2008 R2. You can use MD2, MD4, MD5, SHA, or SHA1 to create hashes of your data. These algorithms are limited up to 20 bytes only.

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. sql,sql-server,sql-server-2008 Here is my attempt using Jeff Moden's DelimitedSplit8k to split the comma-separated values. First, here is the splitter function (check the article for updates of the script): CREATE FUNCTION [dbo].[DelimitedSplit8K]( @pString VARCHAR(8000), @pDelimiter CHAR(1) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN WITH E1

Beginning with SQL Server 2016 (13.x), all algorithms other than SHA2_256, and SHA2_512 are deprecated. Older algorithms (not recommended) will continue working, but they will raise a deprecation event. I asked a similar question so it's up to you if you want to use a deprecated function such as MD5 (if you're on 2016+). You can do testing to

Comments
  • Oops, thanks for pointing it out. I did not catch the version information on top of the MSDN page
  • this should be the right answer. It's because of the sql version.