Encrypt Data using Extended Stored Procedure

Extended Store procedures are DLLs that an instance of sql server can load and run dynamically. You can create your own extended stored procedure for external routine using programming language such as C or You can use already created one. In this article I have use xp_md5.dll to explain data encryption using hash5 algorithm. I got this DLL from http://www.cr0.net:8040/code/crypto/md5/ .

1. Extract this DLL to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn (or appropriate place)
2. Create Extended Stored procedure called xp_md5 in the master data base. Right click on Extended Stored Procedure and select New Extended Store Procedure. Enter xp_md5 for name and select full path to dll.( C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\ xp_md5.dll)

Or
EXEC sp_addextendedproc 'xp_md5', 'xp_md5.dll'
3. Create a user-defined function for each database in which you plan to use the MD5 procedure

CREATE FUNCTION [dbo].[fn_md5] (@data TEXT)
RETURNS CHAR(32) AS
BEGIN
DECLARE @hash CHAR(32)
EXEC master.dbo.xp_md5 @data, -1, @hash OUTPUT
RETURN @hash
END

Use user define function (fn_md5)
SELECT dbo.fn_md5('Lakshman')

Out Put

47a502ffc3c3b43a4e4856fa88290bdd

Or if you want store return value in variable.
DECLARE @hash CHAR(32)
EXEC master.dbo.xp_md5 'Lakshman', -1, @hash OUTPUT
PRINT @hash
Result will same.