SHA256 in T-sql stored procedure

问题:

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.

回答1:

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

回答2:

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)

回答3:

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.)