Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

How to decrypt value in SQL Server.

Author: DevASP
Download Source Code : 424_Decrypt.zip

This article is about how you can decrypt a value if it is store in encrypted form in the table. Here you can take the example of passwords which you have store in an encrypted form and know you want to decrypt.

  • Steps you will do. 
  • Start SQL server and create a new user define function as below.

CREATE FUNCTION dbo.bs_RETURN_DECRYPT_FIELD(@s VARCHAR(8000))

returns VARCHAR(8000)

as

BEGIN

--Returns a string decrypted with key k ( TEA encryption )

--New variant, fixing a couple of minor weaknesses

--TEA- Tiny Encryption Algorithm, copyrightDavid J Wheeler & Roger M Needham

DECLARE @result VARCHAR(8000), @l bigint, @i bigint, @j bigint, @temp bigint, @x bigint, @k VARCHAR(16)

SET @K='EDWIFUNCTION'

declare @y bigint, @z bigint, @sum bigint, @delta bigint, @n bigint, @q bigint

declare @k0 bigint, @k1 bigint, @k2 bigint, @k3 bigint

declare @v0 bigint, @v1 bigint, @tvb VARBINARY(4), @t1 bigint, @t2 bigint, @t3 bigint

SET @i=LEN(@k)

IF @i<16--if the pwd<16 char

BEGIN

SET @k=@k+@k+@k+@k+@k+@k+@k+@k+@k+@k+@k+@k+@k+@k+@k+@k--add pwd to itself

SET @k=LEFT(@k,16)

END

SET @l=(LEN(@s) % 8)

IF @l<>0--if there are no complete 64 bit blocks

BEGIN

SET @i=(LEN(@s))/8+1

SET @l= @i*8-len(@s)

SET @s=@s+replicate(CHAR(0),@l)

END

SET @k0=ASCII(SUBSTRING(@k, 1,1))*16777216+ASCII(SUBSTRING(@k, 2,1))*65536+

ASCII(SUBSTRING(@k, 3, 1))*256+ASCII(SUBSTRING(@k ,4, 1))

SET @k1=ASCII(SUBSTRING(@k, 5, 1))*16777216+ASCII(SUBSTRING(@k ,6, 1))*65536+

ASCII(SUBSTRING(@k, 7, 1))*256+ASCII(SUBSTRING(@k, 8, 1))

SET @k2=ASCII(SUBSTRING(@k, 1,9))*16777216+ASCII(SUBSTRING(@k ,10, 1))*65536+

ASCII(SUBSTRING(@k, 11, 1))*256+ASCII(SUBSTRING(@k ,12, 1))

SET @k3=ASCII(SUBSTRING(@k, 1,13))*16777216+ASCII(SUBSTRING(@k ,14, 1))*65536+

ASCII(SUBSTRING(@k, 15, 1))*256+ASCII(SUBSTRING(@k ,16, 1))

SET @i=1

SET @result=''

WHILE @i<=LEN(@s)

BEGIN

SET @v0=convert(bigint,(ASCII(SUBSTRING(@s,(@i-1)+ 1,1))))*16777216+

convert(bigint,(ASCII(SUBSTRING(@s,(@i-1)+ 2,1))))*65536+

convert(bigint,(ASCII(SUBSTRING(@s, (@i-1)+3, 1))))*256+

convert(bigint,(ASCII(SUBSTRING(@s ,(@i-1)+4, 1))))

SET @v1=convert(bigint,(ASCII(SUBSTRING(@s,(@i-1)+ 5, 1))))*16777216+

convert(bigint,(ASCII(SUBSTRING(@s ,(@i-1)+6, 1))))*65536+

convert(bigint,(ASCII(SUBSTRING(@s, (@i-1)+7, 1))))*256+

convert(bigint,(ASCII(SUBSTRING(@s, (@i-1)+8, 1))))

set @delta=2654435769

SET @sum=3337565984

set @n=32

SET @y=@v0

SET @z=@v1

WHILE @n>0

BEGIN

set @t1= (convert(bigint,(@y*16))) & convert(bigint,4294967295)

set @t2= (convert(bigint,(@y/32)) ) & convert(bigint,4294967295)

IF ((@sum/2048) & 3)=0

set @t3=@k0

ELSE

IF ((@sum/2048) & 3)=1

set @t3=@k1

ELSE

IF ((@sum/2048) & 3)=2

set @t3=@k2

ELSE

IF ((@sum/2048) & 3)=3

set @t3=@k3

SET @z=@z- (convert(bigint,(@t1^@t2+@y^@sum+@t3))) & convert(bigint,4294967295)

SET @z=convert(bigint,@z) & convert(bigint,4294967295)

SET @sum=(@sum-@delta) & convert(bigint,4294967295)

set @t1= (convert(bigint,(@z*16))) & convert(bigint,4294967295)

set @t2= (convert(bigint,(@z/32)) ) & convert(bigint,4294967295)

IF (@sum & 3)=0

set @t3=@k0

ELSE

IF (@sum & 3)=1

set @t3=@k1

ELSE

IF (@sum & 3)=2

set @t3=@k2

ELSE

IF (@sum & 3)=3

set @t3=@k3

SET @y=@y- (convert(bigint,(@t1^@t2+@z^@sum+@t3))) & convert(bigint,4294967295)

SET @y=convert(bigint,@y) & convert(bigint,4294967295)

set @n=@n-1

END

SET @v0=@y

SET @v1=@z

SET @tvb=CONVERT(VARBINARY(4),@v0)

SET @result= @result+CONVERT(varchar(4),@tvb)

SET @tvb=CONVERT(VARBINARY(4),@v1)

SET @result= @result+CONVERT(varchar(4),@tvb)

SET @i=@i+8

END

SET @result=REPLACE(@result,CHAR(0),'')

RETURN @result

END

 

Now let’s suppose that you are going to pick some information about the user who registers to your application but you have store the password in encrypted form at the time of registration, but now the moment you are going to authenticate that use against his/her user name and password. To get the decrypted value pass those values as parameter in the user define function as below.

 

dbo.bs_RETURN_ENCRYPT_FIELD(Password)

 

Here Password is a field in the table which contains the password of the user in encrypted form. This will return you the actual password. For more help the script of the UDF is attached with this article.

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< How to encrypt value in SQL Server

Disclaimer - Privacy
© 2002-2017 DevASP.net