Saturday, July 18, 2009

SQL Tips : II (SHA 256 Encryption in SQL Server2000)

Heloo Every1,
This time it's encryption I dealt with. There are a lot of encryption Gurus and many forums full of algorithms. The only issue is none of them gave me a full implementation which is the most essential thing. Well I did implement the SHA 256 hashing algorithm in SQL server 2000 using extended stored procedure and in SQL server 2008 using assembly. I will explain you in detail what issues I did face and how did I overcome them.
First I will tell you how did I implement the hashing algorithm in SQL server 2000. I used the xp_hash_sha.dll which you can get from this link xp_hash_sha
Place this .dll file in you MSSQL/Binn folder. Now all you have to do is to register the .dll and create an extended stored procedure that can exploit the .dll file.
You can create a extended stored procedure using the following script.

USE [master]
SET NOCOUNT ON
GO
DECLARE @binn NVARCHAR(2000)
SELECT @binn = ''
CREATE TABLE #Temp(path NVARCHAR(2000))
INSERT INTO #Temp(path)
VALUES (@binn)
GO
IF EXISTS
(
SELECT *
FROM DBO.SYSOBJECTS
WHERE [ID] = OBJECT_ID('dbo.xp_hash_sha')
AND OBJECTPROPERTY(ID, N'IsExtendedProc') = 1
)
DROP PROCEDURE dbo.xp_hash_sha
GO
DECLARE @binn NVARCHAR(2000)
SELECT TOP 1 @binn = path + N'xp_hash_sha.dll'
FROM #Temp
EXEC dbo.sp_addextendedproc N'xp_hash_sha', @binn
GO



Now your extended stored procedure is ready. Now all you need to do is to create a function which can call the extended stored procedure.The procedure returns a varbinary and therefore the function must also have the same parameters.You can create the function using the below mentioned script

USE [master]
SET NOCOUNT ON
GO

IF EXISTS
(
SELECT *
FROM dbo.SYSOBJECTS
WHERE [ID] = OBJECT_ID(N'dbo.fn_hash_sha')
AND xtype IN (N'FN', N'IF', N'TF')
)
DROP FUNCTION dbo.fn_hash_sha
GO
CREATE FUNCTION dbo.fn_hash_sha (@plaintext VARBINARY(8000),
@bits INT)
RETURNS VARBINARY(64)
AS
BEGIN
DECLARE @hashcode VARBINARY(64)
EXEC dbo.xp_hash_sha @plaintext, @hashcode OUTPUT, @bits
RETURN @hashcode
END
GO



The problem it seems is solved but trust me here was the place where I was stuck. Actually the result is returned a varbinary where as the column of the table which I had to populate using this encryption was varchar. Now if I stored this result and tried to cast it to a varchar the output was really messy and totally undesired. To fix this I created one more function which did exactly what I wanted. Below mentioned script shows how to create the function.


IF EXISTS
(
SELECT *
FROM dbo.SYSOBJECTS
WHERE [ID] = OBJECT_ID(N'dbo.fn_plain_sha')
AND xtype IN (N'FN', N'IF', N'TF')
)
DROP FUNCTION dbo.fn_plain_sha
GO

create FUNCTION dbo.fn_plain_sha (@input VARchar(15))
RETURNS VARchar(75)
AS
BEGIN
DECLARE @t VARBINARY(75)
DECLARE @output VARchar(75)
set @t = master.dbo.fn_hash_sha (convert(varbinary,@input),256)
set @output= master.dbo.fn_varbintohexstr (@t)
RETURN upper (substring(@output,3,75))
END

GO



Here you see I hard coded 256 because I needed a sha 256. Also the first two characters '0X' was undesired so substring was taken. This functioned was used by me explicitly while populating the encrypted column in the required table.

Hope this documentation makes sense and helps a lot of people. Will update the process I used in SQL server 2008 shortly.

Note: The xp_hash_sha.dll is created by Michael Coles. You can also download the whole script for instatllation and unistallation of the .dll SHA.zip



References: http://www.sqlservercentral.com/articles/Administration/sql2000dbatoolkitpart4/2364/

No comments:

Post a Comment

Hope You are having a good time reading My Blog