encrypting password

[jshanoo]

Hi all,
Please find my table named 'login'
i have somewhere around 150 users active at any given point of time. presently i am using text datatype and storing the user login and password [lgpwd is password field].
4-5 more people access the database and they are filtering out the password.
I want to know how to encrypt the password to avoid any misuse of login user and password.



autoid LoginId lgpwd enable
1 admin admin No
2 john john No
3 sara sara No


Best regards
John Philip

*** Even the Best, did the Bad and Made the Best ***

John Philip

 

[TysonLPrice]

Here is a link about it.

http://www.winnetmag.com/Article/ArticleID/14035/14035.html

Here is a quote out of the link.

"SQL has a built-in function to encrypt stored-procedures - however the algorithms for 6.5 and 7.0 have been broken and so de-cryption is now possible if you know how. There is no encryption facility for tables/data."

This was just a quick look up on my part. Some of the gurus here may know different.

 

[jshanoo]

Hi,
I have created the function , but it is not executing
it gives the following error message
'------------------------------------------
Server: Msg 195, Level 15, State 10, Line 1
'EncryptPassword' is not a recognized function name.
'-----------------------

regards
John Philip
------------------------------------------
CREATE FUNCTION EncryptPassword (
@Password as varchar(255)
)
RETURNS varchar(255)
AS


BEGIN
DECLARE @PasswordEncrypted as varchar(255)
DECLARE @PasswordLength as int
DECLARE @iPOS AS int
DECLARE @XOR AS int
--Ensure password IS NOT case-sensitive
SET @Password = lower(@Password)
--Only encrypt IF parameter IS NOT NULL
IF @Password IS NOT NULL
BEGIN
SET @PasswordLength = len(@Password)
-- CREATE the KEY using a combination the the lenth OF the password + the pos of the first 'e' found in the PW.
-- (The 'e' IS the most commonly used letter IN the alphabet)
SET @XOR = @PasswordLength + charindex(@Password,'e')
SET @iPOS = 1
SET @PasswordEncrypted = ''
WHILE @iPOS <= @PasswordLength
BEGIN
SET @PasswordEncrypted = @PasswordEncrypted + char(Ascii(substring(@Password, @iPOS, 1)) ^ @XOR)
SET @iPOS = @iPOS + 1
END
END
--If @Password parameter IS NULL THEN do nothing
IF @Password IS NULL
BEGIN
SET @PasswordEncrypted = NULL
END
RETURN @PasswordEncrypted
END

*** Even the Best, did the Bad and Made the Best ***

John Philip

 

[donutman]

When you call the function do use <owner>.<functionname> syntax? It's probably dbo.EncryptPassword.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[jshanoo]

Hi thanks it worked.
Regards
John

*** Even the Best, did the Bad and Made the Best ***

John Philip