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.You have to write your own vba|||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|||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.|||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/cracking-sql-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