Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Location
    California
    Posts
    32

    Unanswered: Password Data Types

    I have recently switched my company's database over to MSS2k and all has been going fine untill i came along passwords. I was wondering if there is a password data type like there is in access (where it only shows ****** instead of PASSWORD). I was also wondering if there is a crypt command like in PHP and MySQL or am I stuck writing my own vba for that one.

  2. #2
    Join Date
    Apr 2003
    Location
    Israel
    Posts
    81
    You have to write your own vba
    --
    kukuk

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Here is a one-way encryption algorythm I wrote. You are free to use, just leave my credit in the header!

    Store the encrypted password in a table, and noone will be able to unencrypt it. When someone supplies logs in, encrypt the password they supply to see if it matches the one in their user record.

    blindman


    ---------------------------------
    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

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149
    This is a code snippet, I found somewhere, using one of those SQL Server un-documented password functions.


    DECLARE @ClearPIN varchar(255)
    DECLARE @EncryptedPIN varbinary(255)
    SELECT @ClearPIN = 'test'
    SELECT @EncryptedPIN = CONVERT(varbinary(255), pwdencrypt(@ClearPIN))
    SELECT @EncryptedPIN
    SELECT pwdcompare(@ClearPIN, @EncryptedPIN, 0)

    But I would always suggest you use your own encryption routines like the one Blindman wrote.

    Blindman- Thanks for sharing your code. If I ever use it, I will definitely leave the header in there.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    There are a few problems with pwdencrypt:

    It is an undocumented function, so that it is not guaranteed to work the same, or at all, in future realeases. In fact, its functionality changed between version 6.5 and 7.0 and it was no longer able to recognize old passwords.

    The pwdencrypt function has suffered from a Buffer Overflow vulnerability in the past. I'm not sure if this had been corrected in current patches.

    The pwdencrypt function is not very secure, and has been cracked:
    http://www.nextgenss.com/papers/crac...-passwords.pdf

    Most of the research I did recommended writing customer code rather than using pwdencrypt, so that's what I did! I have used the function I posted in several applications now and have never had an issue with it.

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •