Encryption Problem

I am Using SQl 2000. I use Vb as front end. I am using a simple encryption logic to encrypt password and save it in the Table. Both the encrypt and decrypt functions i have written in VB. The password is saved in the encrypt form in the table. The problem is when i decrypt the password it decrypts wrongly in some cases. In most of the cases if the password is alpha type , the decryption is right but if i use numeric passwords, while decrypting it gives different values. The ascii values are same. Is it because of SQL collation type in the Table Latin1_General_BIN

The code used for encrypting and decrypting is as given below

------
' FUNCTION : EncryptWord
' Purpose : Encrypts the Password depending on the first
' character of the Login Name
' Input : The Login Name , The Password
' Output : The encrypted password
'------------
Public Function EncryptWord(ByVal argLoginName As String, ByVal argPassword As String) As String
Dim strEncWord As String
Dim cntr As Byte
Dim strLoginName As String
Dim strPassword As String
strLoginName = Trim$(argLoginName)
strPassword = Trim$(argPassword)
If Len(strPassword) = 0 Then Exit Function
For cntr = 1 To Len(strPassword)
strEncWord = strEncWord & Chr(Abs(Asc(Mid(strPassword, cntr, 1)) + Asc(Left(strLoginName, 1)) + cntr))
Next cntr

EncryptWord = Trim$(strEncWord)
End Function

'----------------------
' FUNCTION : DecryptWord
' Purpose : Decrypts the Password depending on the first
' character of the Login Name
' Input : The Login Name , The Password
' Output : The Decrypted password
'----------------------

Public Function DecryptWord(ByVal argLoginName As String, ByVal argPassword As String) As String
On Error Resume Next
Dim strEncWord As String
Dim cntr As Byte
Dim strLoginName As String
Dim strPassword As String
strLoginName = Trim$(argLoginName)
strPassword = Trim$(argPassword)
If Len(strPassword) = 0 Then Exit Function
For cntr = 1 To Len(strPassword)
Debug.Print Abs(Asc(Mid(strPassword, cntr, 1)))
strEncWord = strEncWord & Chr(Abs(Asc(Mid(strPassword, cntr, 1)) - Asc(Left(strLoginName, 1)) - cntr))

Next cntr
DecryptWord = Trim$(strEncWord)
End FunctionGenerally, for passwords, it is unnecessary to ever decrypt the password.

The usual technique is to store the encrytped password in the database, and then when someone logs in the password they supply is encrypted using the same algorithm and compared to the stored value.

These are known as one-way encryption schemes, and because they do not ever need to be unencrypted they can be very secure. I have a one-way encryption method configured as a Function if you are interested.|||I would appreciate the one way encryption function.|||Here is the function. Store your encrypted passwords as 10 character strings. When someone logs in, use the same function to encrypt the password they supply, and the result should match the value associated with their login.|||The performance of this Forum is really starting to suck.

Here is the function code. For some reason, I can't get the file uploaded.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Encrypt_Password]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[Encrypt_Password]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE FUNCTION [dbo].[Encrypt_Password]
(@.RawPassword varchar(20))
Returns varchar(20)
as
BEGIN
--Function dbo.EncryptPassword
--Bruce Lindman, 11/19/2002
--
--This function returns a 20 character encryption string derived from a supplied password.
--It uses a non-linear deterministic number generation algorithm known as the Linear Congruential Method
--to generate pseudo-random numbers from the Ascii values of the password characters, and these
--random numbers are then converted back into Ascii characters to form the the encrypted string.
--Because the algorithm is non-linear and uses the password itself as the initial key value, it should be
--practically impossible to reverse engineer the process.
--These variables used for testing
--declare @.RawPassword varchar(20)
--set @.RawPassword = 'Pa$$w0rD'
--set @.RawPassword = '!!!!!!!!!!' --A low ascii value password
--set @.RawPassword = '' --A high ascii value password
declare @.counter int --we'll use this to step through the password character by character
declare @.seed decimal(10, 9) --The derived seed value for the random number generator
declare @.EncryptedPassword varchar(20)
set @.EncryptedPassword = ''
declare @.Modulo int --The divisor in the random number generator
set @.Modulo = 100000000
declare @.Multiplier int --The multiplier in the random number generator
declare @.AsciiValue numeric
--Extend the password to 20 characters by repeating it, separated by the character x
--The x character ensures that password ABC does not return the same value when doubled,
--as ABCABC, but passwords that are doubled with a padded x character will return the same
--encrypted value. ABC returns the same value as ABCxABC or ABCxABCxABC.
while datalength(@.RawPassword) < 20
begin
set @.RawPassword = @.RawPassword + 'x' + @.RawPassword
end
--I think it is unavoidable that for any function F() there exists a pair of values A, B such
--that F(A) = F(B).
--Derive the seed value for the random number function from the password itself
set @.counter = 0
set @.seed = 1
while @.counter < datalength(@.RawPassword)
begin
set @.counter = @.counter + 1
--Use the ascii value of each character to revise the seed value
set @.AsciiValue = ascii(substring(@.RawPassword, @.Counter, 1))
set @.seed = @.seed * (@.AsciiValue/1000)
--We don't want any leading zeros in our decimal value, or the seed may get too small
while @.seed < 0.1 set @.seed = @.seed * 10
end
--We'll derive the multiplier from the seed value, following the principle that a good multiplier
--should be 1 digit less than the Modulo, and should follow the pattern ...x21 where x is an even number
set @.Multiplier = round(@.seed * @.Modulo/100, 0) * 200 + 21
--Now encrypt the password
set @.counter = 0
while @.counter < datalength(@.RawPassword)
begin
set @.counter = @.counter + 1
set @.AsciiValue = ascii(substring(@.RawPassword, @.Counter, 1))
--This next statement is the guts of the random number generator
--It creates a new seed value between 0 and 1
set @.seed = cast(cast(1 + (@.seed + @.AsciiValue/1000) * @.Multiplier * @.Modulo as bigint) % @.Modulo as numeric)/@.Modulo
--Now use the first three digits of the seed value to lookup an ascii character between 1 and 255 and append it to the encrypted password
set @.EncryptedPassword = @.EncryptedPassword + char(1 + cast(round(@.seed * 1000, 0) as int) % 254)
end
Return @.EncryptedPassword
end|||OK, lets try it as a text file. (Why a database forum won't accept files with an sql extension, I have no idea.)|||Thanks for the Function