Here is a sql script I rewrote from php code for RC4 encryption as well as
an active-x script for DTS to do the same thing:
CREATE function dbo.fn_rc4
(@ENC_keyword nvarchar(256),@ENC_data nvarchar(256)) returns nvarchar(768)
as begin
-- Class Made By Mukul Sabharwal [[Email address protected]
-- http://www.devhome.net/php/
-- On October 21, 2000
-- Updated February 24, 2001
-- Now passes RC4 Vector Harness
-- Trevor Weir, CareSoure
-- Converted PHP version to SQL
-- June 22, 2001
declare @dataout nvarchar(768)
declare @key varchar(768)
declare @box varchar(768)
declare @ipos int
declare @xpos int
declare @apos int
declare @jpos int
declare @kpos int
declare @show int
declare @swapi char(3)
declare @swapx char(3)
declare @swapa char(3)
declare @swapj char(3)
declare @ENC_pwlen int
declare @ENC_swapi int
declare @ENC_swapx int
declare @ENC_swapa int
declare @ENC_swapj int
declare @ENC_key nvarchar(256)
declare @ENC_I int
declare @ENC_x int
declare @ENC_w int
declare @ENC_temp nvarchar(256)
declare @ENC_box nvarchar(256)
declare @ENC_k nvarchar(256)
declare @dataoutby nvarchar(256)
declare @ENC_hold1 nvarchar(256)
declare @ENC_hold2 nvarchar(256)
declare @ENC_a int
declare @ENC_j int
declare @ENC_len int
declare @ikey int
declare @ibox int
set @box = ''
set @key = ''
set @ENC_pwlen= len(@ENC_keyword)
set @ENC_box= ''
set @ENC_hold1= '' --replicate(' ',256)
set @ENC_I= 1
WHILE @ENC_I <= 256
begin
set @show = (@ENC_i % @ENC_pwlen) + 1
set @ikey=ascii(substring(@ENC_keyword, @show, 1))
set @key = @key + right('000' + cast(@ikey as varchar(3)),3)
set @box = @box + right('000' + cast(@ENC_i as varchar(3)),3)
set @ENC_I = @ENC_I + 1
END
set @ENC_x= 0
set @ENC_i= 1
WHILE @ENC_I <= 256
begin
set @ipos = ((@ENC_i - 1) * 3) + 1
set @swapi = substring(@box,@ipos,3)
set @ikey = cast(substring(@key,@ipos,3) as int)
set @ibox = cast(@swapi as int)
set @ENC_x= ((@ENC_x + @ibox + @ikey) % 256) + 1
set @ENC_swapi = @ibox
set @xpos = ((@ENC_x - 1) * 3) + 1
set @swapx = substring(@box,@xpos,3)
set @ENC_swapx = cast(@swapx as int)
set @box = left(@box,@ipos - 1) + @swapx + right(@box,768 - (@ipos +
2))
set @box = left(@box,@xpos - 1) + @swapi + right(@box,768 - (@xpos +
2))
set @ENC_I= @ENC_I + 1
END
set @ENC_temp= ''
set @ENC_k= ''
set @dataoutby= ''
set @dataout= ''
set @ENC_a= 0
set @ENC_j= 0
set @ENC_i= 1
set @ENC_LEN= (len(replace(@ENC_data,' ','X')) )
WHILE @ENC_I <= @ENC_LEN
begin
set @ENC_a= ((@ENC_a + 1) % 256)
set @apos = ((@ENC_a - 1) * 3) + 1
set @swapa = substring(@box,@apos,3)
set @ENC_swapa = cast(@swapa as int)
set @ENC_j= ((@ENC_j + @ENC_swapa) % 256) + 1
set @jpos = ((@ENC_j - 1) * 3) + 1
set @swapj = substring(@box,@jpos,3)
set @ENC_swapj = cast(@swapj as int)
set @box = left(@box,@apos - 1) + @swapj + right(@box,768 - (@apos +
2))
set @box = left(@box,@jpos - 1) + @swapa + right(@box,768 - (@jpos +
2))
set @ENC_w= ((@ENC_swapj + @ENC_swapa) % 256) + 1
set @kpos = ((@ENC_w - 1) * 3) + 1
set @ENC_k = cast(substring(@box,@kpos,3) as int)
set @dataoutby= (ascii(substring(@ENC_data, @ENC_i, 1)) ^ @ENC_k)
set @dataout= @dataout + char(@dataoutby)
set @ENC_I= @ENC_I + 1
END
return @dataout
end
GO
/* ACTIVE-X
'**********************************************************************
' Visual Basic ActiveX Script
'-- Class Made By Mukul Sabharwal [[Email address protected]
'-- http://www.devhome.net/php/
'-- On October 21, 2000
'-- Updated February 24, 2001
'-- Now passes RC4 Vector Harness
'-- Trevor Weir, CareSoure
'-- Converted PHP version to VB
'-- June 22, 2001
'***********************************************************************
*
Function RC4(ByRef pStrMessage, ByRef pStrKey)
Dim lBytAsciiAry(255)
Dim lBytKeyAry(255)
Dim lLngIndex
Dim lBytJump
Dim lBytTemp
Dim lBytY
Dim lLngT
Dim lLngX
Dim lLngKeyLength
' Validate data
If Len(pStrKey) = 0 Then Exit Function
If Len(pStrMessage) = 0 Then Exit Function
' transfer repeated key to array
lLngKeyLength = Len(pStrKey)
For lLngIndex = 0 To 255
lBytKeyAry(lLngIndex) = Asc(Mid(pStrKey, ((lLngIndex) Mod
(lLngKeyLength)) + 1, 1))
Next
' Initialize S
For lLngIndex = 0 To 255
lBytAsciiAry(lLngIndex) = lLngIndex
Next
' Switch values of S arround based off of index and Key value
lBytJump = 0
For lLngIndex = 0 To 255
' Figure index to switch
lBytJump = (lBytJump + lBytAsciiAry(lLngIndex) +
lBytKeyAry(lLngIndex)) Mod 256
' Do the switch
lBytTemp =
lBytAsciiAry(lLngIndex)
lBytAsciiAry(lLngIndex) = lBytAsciiAry(lBytJump)
lBytAsciiAry(lBytJump) = lBytTemp
Next
lLngIndex = 0
lBytJump = 0
For lLngX = 1 To Len(pStrMessage)
lLngIndex = (lLngIndex + 1) Mod 256 ' wrap index
lBytJump = (lBytJump + lBytAsciiAry(lLngIndex)) Mod 256 '
wrap J+S()
' Add/Wrap those two
lLngT = (lBytAsciiAry(lLngIndex) + lBytAsciiAry(lBytJump)) Mod
256
' Switcheroo
lBytTemp =
lBytAsciiAry(lLngIndex)
lBytAsciiAry(lLngIndex) = lBytAsciiAry(lBytJump)
lBytAsciiAry(lBytJump) = lBytTemp
lBytY = lBytAsciiAry(lLngT)
' Character Encryption ...
RC4 = RC4 & Chr(Asc(Mid(pStrMessage, lLngX, 1)) Xor lBytY)
Next
End Function
Function Main()
DTSGlobalVariables("DecrAcct").value =
trim(RC4(trim(DTSGlobalVariables("Acct").value),trim(DTSGlobalVariables(
"Key").value)))
DTSGlobalVariables("DecrUser").value =
trim(RC4(trim(DTSGlobalVariables("User").value),trim(DTSGlobalVariables(
"Key").value)))
DTSGlobalVariables("DecrApwd").value =
trim(RC4(trim(DTSGlobalVariables("Apwd").value),trim(DTSGlobalVariables(
"Key").value)))
DTSGlobalVariables("DecrUpwd").value =
trim(RC4(trim(DTSGlobalVariables("Upwd").value),trim(DTSGlobalVariables(
"Key").value)))
Main = DTSTaskExecResult_Success
End Function
*/
-----Original Message-----
From: Natalia Brett
[mailto:mssqldba-ezmlmshield-x45693405.[Email address protected]
Sent: Wednesday, March 30, 2005 9:08 AM
To: LazyDBA Discussion
Subject: Encrypt and Decrypt information in the table
Hi,
we are going to store passwords in a column is there any native SQL function
to
encrypt and decrypt, or do we need to create our own encyption algorithm??
(is there a system sp that is used to encrypt the SQL Server user
usernames??)
Thanks,
Natalia
The information within this e-mail is confidential and intended solely for
the use of the recipient(s). If you are not the intended recipient then
please notify Natalia Brett via email at Natalia.[Email address protected]
or by telephone on +44(0)1244 319912. Then delete the email from your
system. Please do not read, copy, print, forward, disclose, or use the
information contained within this email. Note that any views or opinions
presented in this email are solely those of the author and do not
necessarily represent those of Aktiv Kapital. Finally, the recipient should
check this email and any attachments for the presence of viruses. The
company accepts no liability for any damage caused by any virus transmitted
by this email.
Aktiv Kapital (UK) Ltd
Merchants House
Hamilton Place
Chester
CH12BE
UK
www.AktivKapital.co.uk
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
MS Sql Server LazyDBA home page