12

Is it possible to generate a SHA-256 hash of a string from a stored procedure in Sql Server 2008?

For deployment reasons, I'd prefer it in TSQL.

WOPR
  • 5,313
  • 6
  • 47
  • 63

3 Answers3

24

Update: SQL Server 2012 HASHBYTES() now supports SHA-256 and SHA-512 out of the box.

HASHBYTES ( '<algorithm>', { @input | 'input' } )

<algorithm>::= MD2 | MD4 | MD5 | SHA | SHA1 | SHA2_256 | SHA2_512

Sure. You can do it in TSQL, but it will be much easier to implement it as a CLR Stored procedure.

Here's an actual example, that simply uses the .NET Framework types: Let's Hash a BLOB

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • 1
    Hi Mitch thanks for that. For deployment reasons, I'd prefer it in TSQL. I'll clarify. – WOPR Jun 02 '10 at 05:15
  • It's going to be considerably harder to write and less perform less well in TSQL ... – Mitch Wheat Jun 02 '10 at 05:18
  • 1
    Just an FYI, SQL 2012 added support for SHA-256. You just pass SHA_256 as the algorithm. – Oppositional Feb 20 '13 at 05:53
  • The TSQL link is not usable anymore; it has restricted access without anyway to register new user accounts. http://sqlblog.com/Msgs/default.aspx?MessageID=29 However you can find the original article at http://web.archive.org/web/20171003225612/http://sqlblog.com:80/blogs/michael_coles/archive/2009/04/16/13253.aspx – Akbari Apr 26 '18 at 05:31
4

SHA256, SHA512 in SQL SERVER 2008 OR SQL SERVER 2005!

You can do if you use the fnEnCryptSHA.dll!!

USE [master] GO
EXEC sp_configure 'clr enabled', 1 GO RECONFIGURE GO

CREATE ASSEMBLY InnoDll FROM 'C:\sqltip\fnEnCryptSHA.dll' WITH PERMISSION_SET= SAFE GO

CREATE FUNCTION dbo.fnGetStringToSha256 (@Str nvarchar(1000)) RETURNS varbinary(8000) AS EXTERNAL NAME InnoDll.fnEnCryptSHA.GetStringToSha256 GO

CREATE FUNCTION dbo.fnGetBinaryToSha256 (@Str varbinary(8000)) RETURNS varbinary(8000) AS EXTERNAL NAME InnoDll.fnEnCryptSHA.GetBinaryToSha256 GO

SELECT dbo.fnGetStringToSha256('abc')

SELECT dbo.fnGetBinaryToSha256(0x9F04F41A848514162050E3D68C1A7ABB441DC2B5)
Baby Groot
  • 4,637
  • 39
  • 52
  • 71
inno
  • 41
  • 2
  • Where can you find that DLL? – tigeryan Oct 07 '15 at 16:00
  • I didn't realize you could call a .dll so easily through SQL Server. You could also just throw your own SHA-256 hashing method into a class/.dll and call it via SQL. Very cool, thanks for the answer. – clamum Jan 27 '17 at 17:14
0

Does it HAVE to be SHA-256. There is a SQL HASHBYTES() function that can do SHA, SHA1, MD5 etc.

(Of course it depends on if you just want a hash representation of a value, or to securely store valuable data.)

BenMorel
  • 34,448
  • 50
  • 182
  • 322
Guy
  • 9,720
  • 7
  • 38
  • 42
  • Added in case (like me) I was searching for any native HASH function in SQL server and they come across this question. It would imply that CLR is the only way to provide a HASH function. – Guy May 13 '11 at 12:17
  • CLR Example: http://stackoverflow.com/questions/12416249/hashing-a-string-with-sha256 – James L. Jul 22 '15 at 01:25