To be honest, this procedure was not written by me.
I found it on a website. (I really can't remember its site' s name. really sorry for that.)
Let me share again and thank you the owner of this procedure.
password with encyption
_____________________
ALTER PROCEDURE [dbo].[Encrypt_PW]
AS
--CREATE SYMMETRIC KEY EncyptKey
--WITH ALGORITHM = TRIPLE_DES
--ENCRYPTION BY PASSWORD = '1234'
--UPDATE LOGIN USE PASSWORDS WITH ENCRYPTON
UPDATE Staff SET PW = dbo.fnEncDecRc4('tester', '1234')
WHERE LOGINID = 'test'
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Decryption the encrypted passwords
______________________________
USE [test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnEncDecRc4]
(
@Pwd VARCHAR(256),
@Text VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Box TABLE (i TINYINT, v TINYINT)
INSERT @Box
(
i,
v
)
SELECT i,
v
FROM dbo.fnInitRc4(@Pwd)
DECLARE @Index SMALLINT,
@i SMALLINT,
@j SMALLINT,
@t TINYINT,
@k SMALLINT,
@CipherBy TINYINT,
@Cipher VARCHAR(8000)
SELECT @Index = 1,
@i = 0,
@j = 0,
@Cipher = ''
WHILE @Index <= DATALENGTH(@Text)
BEGIN
SELECT @i = (@i + 1) % 256
SELECT @j = (@j + b.v) % 256
FROM @Box b
WHERE b.i = @i
SELECT @t = v
FROM @Box
WHERE i = @i
UPDATE b
SET b.v = (SELECT w.v FROM @Box w WHERE w.i = @j)
FROM @Box b
WHERE b.i = @i
UPDATE @Box
SET v = @t
WHERE i = @j
SELECT @k = v
FROM @Box
WHERE i = @i
SELECT @k = (@k + v) % 256
FROM @Box
WHERE i = @j
SELECT @k = v
FROM @Box
WHERE i = @k
SELECT @CipherBy = ASCII(SUBSTRING(@Text, @Index, 1)) ^ @k,
@Cipher = @Cipher + CHAR(@CipherBy)
SELECT @Index = @Index +1
END
RETURN @Cipher
END
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This function is used in Decryption function
____________________________________
USE [test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnInitRc4]
(
@Pwd VARCHAR(256)
)
RETURNS @Box TABLE (i TINYINT, v TINYINT)
AS
BEGIN
DECLARE @Key TABLE (i TINYINT, v TINYINT)
DECLARE @Index SMALLINT,
@PwdLen TINYINT
SELECT @Index = 0,
@PwdLen = LEN(@Pwd)
WHILE @Index <= 255
BEGIN
INSERT @Key
(
i,
v
)
VALUES (
@Index,
ASCII(SUBSTRING(@Pwd, @Index % @PwdLen + 1, 1))
)
INSERT @Box
(
i,
v
)
VALUES (
@Index,
@Index
)
SELECT @Index = @Index + 1
END
DECLARE @t TINYINT,
@b SMALLINT
SELECT @Index = 0,
@b = 0
WHILE @Index <= 255
BEGIN
SELECT @b = (@b + b.v + k.v) % 256
FROM @Box AS b
INNER JOIN @Key AS k ON k.i = b.i
WHERE b.i = @Index
SELECT @t = v
FROM @Box
WHERE i = @Index
UPDATE b1
SET b1.v = (SELECT b2.v FROM @Box b2 WHERE b2.i = @b)
FROM @Box b1
WHERE b1.i = @Index
UPDATE @Box
SET v = @t
WHERE i = @b
SELECT @Index = @Index + 1
END
RETURN
END
