SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Generate Password Procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 02/08/2007 :  23:16:18  Show Profile  Reply with Quote
Stored procedure P_GENERATE_PASSWORDS returns a list of randomly generated passwords designed to meet typical password complexity requirements of a minimum of 8 characters, with at least one each of uppercase letters, lowercase letters, numbers, and special characters. It can generate from 1 to 10,000 passwords as a result set.

The passwords are meant to be somewhat mnemonic by generating syllables consisting of an uppercase consonant, followed by a lower case vowel, and a lowercase consonant. A single number or special character separates syllables, except in the case of 2 syllables. If there are only 2 syllables, they will be separated by a number and a special character.

Input parameters @SYLLABLE_COUNT and @PASSWORD_COUNT determine the password length and the number of passwords.





if objectproperty(object_id('dbo.P_GENERATE_PASSWORDS'),'IsProcedure') = 1
	begin drop procedure dbo.P_GENERATE_PASSWORDS end
go
create procedure dbo.P_GENERATE_PASSWORDS
	(
	@SYLLABLE_COUNT		int 	= null ,
	@PASSWORD_COUNT		int	= null ,
	@PASSWORD_STRENGTH	float	= null	output
	)
as

/*
Procedure Name: P_GENERATE_PASSWORDS


Procedure Description:

P_GENERATE_PASSWORDS returns a list of randomly generated passwords
designed to meet typical password complexity requirements of a minimum
of 8 characters, with at least one each of uppercase letters,
lowercase letters, numbers, and special characters.

The passwords are meant to be somewhat mnemonic by generating
syllables consisting of an uppercase consonant, followed by a
lower case vowel, and a lowercase consonant.  Syllables are separated
by a single number or special character, except in the case of 2 syllables.
If there are only 2 syllables, the syllables will be separated by
a number and a special character.

Passwords can be from 2 to 8 syllables in length.

Input parameter @SYLLABLE_COUNT is the total syllables in each output password.
The value of @SYLLABLE_COUNT must be between 2 and 8.  If it is < 2 or null,
it is set to 3.  If it is > 8 it is set to 8.

Input parameter @PASSWORD_COUNT is the total passwords to be returned.
The value of @SYLLABLE_COUNT must be between 1 and 10,000.
If it is < 1, it is set to 1. If it is null, it is set to 10.
If it is > 10,000 it is set to 10,000.

Output parameter @PASSWORD_STRENGTH returns the total possible
passwords that are possible for the selected @SYLLABLE_COUNT.

*/

set nocount on


-- Set password syllable count
set @SYLLABLE_COUNT =
	case
	when @SYLLABLE_COUNT is null
	then 3
	when @SYLLABLE_COUNT < 2
	then 3
	when @SYLLABLE_COUNT > 8
	then 8
	else @SYLLABLE_COUNT
	end

-- Set password count
set @PASSWORD_COUNT =
	case
	when @PASSWORD_COUNT is null
	then 10
	when @PASSWORD_COUNT < 1
	then 1
	when @PASSWORD_COUNT > 10000
	then 10000
	else @PASSWORD_COUNT
	end

declare @con varchar(200)
declare @vowel varchar(200)
declare @special varchar(200)
declare @num varchar(200)
declare @special_only varchar(200)
declare @con_len int
declare @vowel_len int
declare @special_len int
declare @num_len int
declare @special_only_len int
declare @strings int

-- set character strings for password generation
select
	@con		= 'bcdfghjklmnpqrstvwxyz',
	@vowel		= 'aeiou',
	@num		= '1234567890',
	@special_only	= '~!@#$%^&*()_+-={}|[]\:;<>?,./'

set 	@special = @num+@special_only

-- set string lengths
select	@con_len		= len(@con),
	@vowel_len		= len(@vowel),
	@special_len		= len(@special),
	@num_len		= len(@num),
	@special_only_len	= len(@special_only) ,
	@strings 		=
			case
			when @SYLLABLE_COUNT < 3
			then 2
			else @SYLLABLE_COUNT-1
			end

--select @con, @vowel, @special, @num, @special_only,
--SELECT @con_len, @vowel_len, @special_len, @num, @special_only_len, @strings

-- Declare number tables to generate rows
declare @num1 table (NUMBER int not null primary key clustered)
declare @num2 table (NUMBER int not null primary key clustered)
declare @num3 table (NUMBER int not null primary key clustered)

declare @rows_needed_root int
set @rows_needed_root = convert(int,ceiling(sqrt(@PASSWORD_COUNT)))

-- Load number 0 to 16
insert into @num1 (NUMBER)
select 0 union all select 1 union all select 2 union all select 3 union all
select 4 union all select 5 union all select 6 union all select 7 union all
select 8 union all select 9
order by 1

-- Load table with numbers zero thru square root of the number of rows needed +1
insert into @num2 (NUMBER)
select
	NUMBER = a.NUMBER+(10*b.NUMBER)
from
	@num1 a cross join @num1 b
where
	a.NUMBER+(10*b.NUMBER) < 
	@rows_needed_root
order by
	1

-- Load table with the number of passwords needed
insert into @num3 (NUMBER)
select
	NUMBER = a.NUMBER+(@rows_needed_root*b.NUMBER)
from
	@num2 a
	cross join
	@num2 b
where
	a.NUMBER+(@rows_needed_root*b.NUMBER) < @PASSWORD_COUNT
order by
	1

-- Declare password string table
declare @p table (
	number	int		not null
		primary key clustered,
	m1 	varchar(10)	not null,
	m2 	varchar(10)	not null,
	m3 	varchar(10)	not null,
	m4 	varchar(10)	not null,
	m5 	varchar(10)	not null,
	m6 	varchar(10)	not null,
	m7 	varchar(10)	not null,
	m8 	varchar(10)	not null,
	
	s1 	varchar(10)	not null,
	s2 	varchar(10)	not null,
	s3 	varchar(10)	not null,
	s4 	varchar(10)	not null,
	s5 	varchar(10)	not null,
	s6 	varchar(10)	not null,
	s7 	varchar(10)	not null
)

insert into @p
select
	NUMBER,
	-- M1 through M8 will be syllables composed of a single randomly selected
	-- uppercase consonant, a single randomly selected lowercase vowel,
	-- followed by as single randomly selected lowercase consonant.
	m1 =
		upper(substring(@con,  (R11%@con_len)+1,1))+
		substring(@vowel,(R12%@vowel_len)+1,1)+
		substring(@con,  (R13%@con_len)+1,1),
	m2 =
		upper(substring(@con,  (R21%@con_len)+1,1))+
		substring(@vowel,(R22%@vowel_len)+1,1)+
		substring(@con,  (R23%@con_len)+1,1),
	m3 =
		upper(substring(@con,  (R31%@con_len)+1,1))+
		substring(@vowel,(R32%@vowel_len)+1,1)+
		substring(@con,  (R33%@con_len)+1,1),
	m4 =
		upper(substring(@con,  (R41%@con_len)+1,1))+
		substring(@vowel,(R42%@vowel_len)+1,1)+
		substring(@con,  (R43%@con_len)+1,1),
	m5 =
		upper(substring(@con,  (R51%@con_len)+1,1))+
		substring(@vowel,(R52%@vowel_len)+1,1)+
		substring(@con,  (R53%@con_len)+1,1),
	m6 =
		upper(substring(@con,  (R61%@con_len)+1,1))+
		substring(@vowel,(R62%@vowel_len)+1,1)+
		substring(@con,  (R63%@con_len)+1,1),
	m7 =
		upper(substring(@con,  (R71%@con_len)+1,1))+
		substring(@vowel,(R72%@vowel_len)+1,1)+
		substring(@con,  (R73%@con_len)+1,1),
	m8 =
		upper(substring(@con,  (R81%@con_len)+1,1))+
		substring(@vowel,(R82%@vowel_len)+1,1)+
		substring(@con,  (R83%@con_len)+1,1),

	-- S1 through S7 will each be a single randomly selected
	-- number or special character.  At least one of the used
	-- columns will be a number and one will be a special character.

	s1 =
		case
		when NUMBER_COL = 1
		then substring(@num,(RS1%@num_len)+1,1)
		when SPECIAL_COL = 1
		then substring(@special_only,(RS1%@special_only_len)+1,1)
		else substring(@special,(RS1%@special_len)+1,1)
		end,
	s2 =
		case
		when NUMBER_COL = 2
		then substring(@num,(RS2%@num_len)+1,1)
		when SPECIAL_COL = 2
		then substring(@special_only,(RS2%@special_only_len)+1,1)
		else substring(@special,(RS2%@special_len)+1,1)
		end,
	s3 =
		case
		when NUMBER_COL = 3
		then substring(@num,(RS3%@num_len)+1,1)
		when SPECIAL_COL = 3
		then substring(@special_only,(RS3%@special_only_len)+1,1)
		else substring(@special,(RS3%@special_len)+1,1)
		end,
	s4 =
		case
		when NUMBER_COL = 4
		then substring(@num,(RS4%@num_len)+1,1)
		when SPECIAL_COL = 4
		then substring(@special_only,(RS4%@special_only_len)+1,1)
		else substring(@special,(RS4%@special_len)+1,1)
		end,
	s5 =
		case
		when NUMBER_COL = 5
		then substring(@num,(RS5%@num_len)+1,1)
		when SPECIAL_COL = 5
		then substring(@special_only,(RS5%@special_only_len)+1,1)
		else substring(@special,(RS5%@special_len)+1,1)
		end,
	s6 =
		case
		when NUMBER_COL = 6
		then substring(@num,(RS6%@num_len)+1,1)
		when SPECIAL_COL = 6
		then substring(@special_only,(RS6%@special_only_len)+1,1)
		else substring(@special,(RS6%@special_len)+1,1)
		end,
	s7 =
		case
		when NUMBER_COL = 7
		then substring(@num,(RS7%@num_len)+1,1)
		when SPECIAL_COL = 7
		then substring(@special_only,(RS7%@special_only_len)+1,1)
		else substring(@special,(RS7%@special_len)+1,1)
		end
from
(
select
	aaaa.*,
	-- Select random columns numbers to force at least
	-- one special character and one number character
	-- in each password
	NUMBER_COL  = (X1%@strings)+1 ,
	SPECIAL_COL = ((((X2%(@strings-1))+1)+X1)%@strings)+1
from
(
select top 100 percent
	NUMBER,
	-- Generate random numbers for password generation
	R11 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R12 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R13 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R21 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R22 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R23 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R31 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R32 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R33 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R41 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R42 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R43 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R51 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R52 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R53 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R61 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R62 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R63 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R71 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R72 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R73 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R81 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R82 = abs(convert(bigint,convert(varbinary(20),newid()))),
	R83 = abs(convert(bigint,convert(varbinary(20),newid()))),

	RS1 = abs(convert(bigint,convert(varbinary(20),newid()))),
	RS2 = abs(convert(bigint,convert(varbinary(20),newid()))),
	RS3 = abs(convert(bigint,convert(varbinary(20),newid()))),
	RS4 = abs(convert(bigint,convert(varbinary(20),newid()))),
	RS5 = abs(convert(bigint,convert(varbinary(20),newid()))),
	RS6 = abs(convert(bigint,convert(varbinary(20),newid()))),
	RS7 = abs(convert(bigint,convert(varbinary(20),newid()))),

	X1 = convert(bigint,abs(convert(int,convert(varbinary(20),newid())))),
	X2 = convert(bigint,abs(convert(int,convert(varbinary(20),newid()))))
from
	@num3 aaaaa
	
order by
	aaaaa.NUMBER
) aaaa ) aaa
order by
	aaa.NUMBER

-- Compute password strength as the total possible passwords
-- for the selected number of syllables.
select
	@PASSWORD_STRENGTH =
	power((@con_len*@con_len*@vowel_len)*1E,@SYLLABLE_COUNT*1E)*
	(@special_only_len*@num_len*1E)*
	case
	when @strings < 3
	then 1E
	else power(@special_len*1E,(@strings-2)*1E)
	end

-- Declare output table
declare @PASSWORD table 
(
	NUMBER 		int		not null
			identity(1,1) primary key clustered,
	[PASSWORD]	varchar(32)	not null 
)

insert into @password ([PASSWORD])
select	top 100 percent
	[PASSWORD]
from
	(
	select
		distinct
		[PASSWORD] =
		convert(varchar(32),
		case
		when @SYLLABLE_COUNT = 2
		then m1+s1+s2+m2
		else
		substring(m1+s1+m2+s2+m3+s3+m4+s4+m5+s5+m6+s6+m7+s7+m8
		,1,(@SYLLABLE_COUNT*4)-1)
		end)
	from @P
	) a
where
	-- Verify at least one number in password
	[PASSWORD] like '%[1234567890]%'		and
	-- Verify at least one special character in password
	[PASSWORD] like '%[^a-z1234567890]%'
order by
	newid()

select * from @password order by NUMBER

return 0
go
grant execute on dbo.P_GENERATE_PASSWORDS to public

go

-- Test Script
declare @SYLLABLE_COUNT		int
declare @PASSWORD_COUNT		int
declare @PASSWORD_STRENGTH	float

select @SYLLABLE_COUNT = 2 , @PASSWORD_COUNT = 5
print 	'@SYLLABLE_COUNT = '+convert(varchar(20),@SYLLABLE_COUNT)+
	', @PASSWORD_COUNT = '+convert(varchar(20),@PASSWORD_COUNT)

exec dbo.P_GENERATE_PASSWORDS
	@SYLLABLE_COUNT,@PASSWORD_COUNT,@PASSWORD_STRENGTH output

print 	'@PASSWORD_STRENGTH	 = '+convert(varchar(50),@PASSWORD_STRENGTH)
print ''


select @SYLLABLE_COUNT = 3 , @PASSWORD_COUNT = 6
print 	'@SYLLABLE_COUNT = '+convert(varchar(20),@SYLLABLE_COUNT)+
	', @PASSWORD_COUNT = '+convert(varchar(20),@PASSWORD_COUNT)

exec dbo.P_GENERATE_PASSWORDS
	@SYLLABLE_COUNT,@PASSWORD_COUNT,@PASSWORD_STRENGTH output

print 	'@PASSWORD_STRENGTH	 = '+convert(varchar(50),@PASSWORD_STRENGTH)
print ''


select @SYLLABLE_COUNT = 5 , @PASSWORD_COUNT = 7
print 	'@SYLLABLE_COUNT = '+convert(varchar(20),@SYLLABLE_COUNT)+
	', @PASSWORD_COUNT = '+convert(varchar(20),@PASSWORD_COUNT)

exec dbo.P_GENERATE_PASSWORDS
	@SYLLABLE_COUNT,@PASSWORD_COUNT,@PASSWORD_STRENGTH output

print 	'@PASSWORD_STRENGTH	 = '+convert(varchar(50),@PASSWORD_STRENGTH)
print ''


select @SYLLABLE_COUNT = 8 , @PASSWORD_COUNT = 20
print 	'@SYLLABLE_COUNT = '+convert(varchar(20),@SYLLABLE_COUNT)+
	', @PASSWORD_COUNT = '+convert(varchar(20),@PASSWORD_COUNT)

exec dbo.P_GENERATE_PASSWORDS
	@SYLLABLE_COUNT,@PASSWORD_COUNT,@PASSWORD_STRENGTH output

print 	'@PASSWORD_STRENGTH	 = '+convert(varchar(50),@PASSWORD_STRENGTH)
print ''


Results of Test Script:

@SYLLABLE_COUNT = 2, @PASSWORD_COUNT = 5
NUMBER      PASSWORD                         
----------- -------------------------------- 
1           Tis|2Fun
2           Miy5]Fib
3           Bay1|Puz
4           Tel3.Pus
5           Duq0@Roy

@PASSWORD_STRENGTH	 = 1.40999e+009
 
@SYLLABLE_COUNT = 3, @PASSWORD_COUNT = 6
NUMBER      PASSWORD                         
----------- -------------------------------- 
1           Qab@Kaz0Lan
2           Sav1Tig]Hat
3           Pah6Fic|Cic
4           Buz7Viz=Mec
5           Vig^Wah9Xuf
6           Qew2Mif^Mix

@PASSWORD_STRENGTH	 = 3.10902e+012
 
@SYLLABLE_COUNT = 5, @PASSWORD_COUNT = 7
NUMBER      PASSWORD                         
----------- -------------------------------- 
1           Mux4Zor_Jog{Vec,Bih
2           Ker1Qem[Gat,Hut|Zif
3           Red}Ciq5Ber%Son:Qej
4           Cov@Doz8Zow\Fic>Pos
5           Tad0Bek&Fug_Kiv9Rez
6           Pil1Nul$Vil~Koh_Xel
7           Zuk4Gir&Yep|Ned)Sap

@PASSWORD_STRENGTH	 = 2.29917e+022
 
@SYLLABLE_COUNT = 8, @PASSWORD_COUNT = 20
NUMBER      PASSWORD                         
----------- -------------------------------- 
1           Biz&Xak9Gew{Vuf[Tix;Qap-Bik{Vay
2           Rof<Job*Fax-Niq/Zew9Pah:Bag(Zok
3           Noh1Nor7Rul5Fon@Mig>Xod.Lay.Maq
4           Piw:Keb}Rod8Yah}Vaw\Let@Yoq9Sav
5           Hav@Qer/Met7Zig&Jiw4Pot-Fod(Zat
6           Bid_Lal+Bay3Fos9Fez\Faw!Kad4Zok
7           Qar-Kig-Lem3Yeq?Xuj7Zun,Xid=Xel
8           Biq6Jot:Caj(Xun2Kup[Fax|Gec,Xon
9           Yac7Nox^Woy~Wag0Xan\Hil3Cab/Nit
10          Pod+Kor%Fov7Vil,Dor:Xoq!Kel3Poq
11          Goc)Roz7Ruq/Pad8Jeh*Xaj&Dew{Duy
12          Sik/Ruj@Wiv9Qik[Sub=Qim,Ned:Qit
13          Les9Har&Ceb5Heg^Fov0Vaf1Fuf[Maq
14          Deg6Yiw$Peg:Wuj7Woc_Mip|Kam9Zus
15          Nix^Dev%Qoj=Seq[Jig6Lig}Day-Ric
16          Dux;Woy=Zud1Mak5Yej$Kav2Mek5Buh
17          Yuv8Mor9Wix&Giq5Zar@Nuk$Pey<Lok
18          Dem~Kof-Yoq(Xig$Tew\Fun7Meq2Kik
19          Caq1Qag{Pes{Gex|Til=Vuk7Tig1Vur
20          Miw)Law}Tun2Lop.Jix#Riq|Yat$Juc

@PASSWORD_STRENGTH	 = 1.46214e+037







CODO ERGO SUM

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 02/09/2007 :  03:23:43  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
But will those passwords be easy to remember? Unless those are easy to remember, the complete purpose of having password is defeated.



Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 02/09/2007 :  04:34:51  Show Profile  Reply with Quote
"But will those passwords be easy to remember"

I think MVJs:

"The passwords are meant to be somewhat mnemonic ..."

works well. Clearly not for a 56 character password!, but the 2 & 3 syllable ones are easy to remember. And for a machine-only interface the longer ones will be fine, of course.

Kristen
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 02/09/2007 :  04:56:55  Show Profile  Visit spirit1's Homepage  Reply with Quote
i use this and it works pretty well with some modifications:
http://vyaskn.tripod.com/code/password.txt



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 02/09/2007 :  09:38:35  Show Profile  Reply with Quote
I was trying to strike a compromise between creating strong passwords that are as easy as possible to remember, while still meeting the password complexity rules of a typical Windows domain. It’s not a trivial problem to solve, because easy to remember is a highly subjective thing.

The idea of using syllables is something I borrowed (stole outright) from the VMS operating system. It has a generate password feature that returns a list of mnemonic passwords that you can select a password from.

I think the 2 and 3 syllable passwords are fine for Windows accounts where you have lockouts after a reasonable number of failed attempts. I would recommend 4 or more syllables for SQL Server passwords where someone can make an unlimited number of attempts at guessing a password without a lockout.

If anyone has any suggestions on how to improve it while still meeting the objectives, I would be interested in hearing them.





CODO ERGO SUM
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 02/09/2007 :  09:57:26  Show Profile  Visit spirit1's Homepage  Reply with Quote
this a bit modified vyas's sproc i use.
I had to generate 5000 simple readable passwords with this and it was up to the chanllenge


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[RandomPassword]
(
@len int = 8, --Length of the password to be generated
@password_type char(7) = 'simple' 
--Default is to generate a simple password with lowecase letters. 
--Pass anything other than 'simple' to generate a complex password. 
--The complex password includes numbers, special characters, upper case and lower case letters
)
AS
DECLARE @password varchar(25), @type tinyint, @bitmap char(5)
SET @password='' 
SET @bitmap = 'uaeio' 
-- @bitmap contains all the vowels, which are a, e, i, o and u. 
-- These vowels are used to generate slightly readable/rememberable simple passwords

WHILE @len > 0
BEGIN
	IF @password_type = 'simple' --Generating a simple password
	BEGIN
		IF (@len%2) = 0  --Appending a random vowel to @password
		begin
			-- (RAND() * (4)) -> 4 = len(@bitmap) - 1, it's hardcoded for speed
			SET @password = @password + SUBSTRING(@bitmap,CONVERT(int,ROUND(1 + (RAND() * (4)),0)),1)
		end
		ELSE --Appending a random alphabet
		begin
			-- we eliminte the possibility that char from bitmap and the random one are same
			declare @char char(1)
			set @char = CHAR(ROUND(97 + (RAND() * (25)),0))
			while @bitmap like '%' + @char + '%'
			begin
				set @char = CHAR(ROUND(97 + (RAND() * (25)),0))
			end
			SET @password = @password + @char
		end
	END
	ELSE --Generating a complex password
	BEGIN
		SET @type = ROUND(1 + (RAND() * (3)),0)

		IF @type = 1 --Appending a random lower case alphabet to @password
			SET @password = @password + CHAR(ROUND(97 + (RAND() * (25)),0))
		ELSE IF @type = 2 --Appending a random upper case alphabet to @password
			SET @password = @password + CHAR(ROUND(65 + (RAND() * (25)),0))
		ELSE IF @type = 3 --Appending a random number between 0 and 9 to @password
			SET @password = @password + CHAR(ROUND(48 + (RAND() * (9)),0))
		ELSE IF @type = 4 --Appending a random special character to @password
			SET @password = @password + CHAR(ROUND(33 + (RAND() * (13)),0))
	END

	SET @len = @len - 1
END

-- here we add one number to the end of string if the password is simple type.
-- we could add it somewhere else with little recoding
IF @password_type = 'simple'
	select @password = LEFT ( @password, len(@password) - 1) + CHAR ( ROUND(48 + (RAND() * (9)),0))

SELECT @password as NewPassword




Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 02/09/2007 :  10:02:34  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
The SP you created is great, MVJ, but what I was saying that instead keeping keeping bitmap of characters, numbers and special characters, we can have a dictionary of uncommon words and may be mix those words to create simple-to-remember but difficult to guess passwords. (along with random uppercase letters and numbers for additional complexity)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Edited by - harsh_athalye on 02/09/2007 10:04:37
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 02/09/2007 :  10:25:10  Show Profile  Reply with Quote
quote:
Originally posted by spirit1

this a bit modified vyas's sproc i use.
I had to generate 5000 simple readable passwords with this and it was up to the chanllenge ...



It looks like a good procedure, but it doesn't meet my design objectives.


The simple passwords don't meet the complexity rules:
Simple password
--------------- 
amijebe2
owavega3
iwehiku2
imegebi6
ozaxafa9
alicema1
aqagapo4
inamuca6
esadeza4
ekuniga7


The strong passwords don't look very easy to remember:
Strong password
--------------- 
B4zI1-5U
hW4K6KM'
3pNkfp,G
I1Q#*E16
3Di5h0a-
4*4T1jH4
3*T8bM9R
+71Snm#5
'8bH5(WG
9WN87N1z





CODO ERGO SUM
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 02/09/2007 :  10:48:26  Show Profile  Visit spirit1's Homepage  Reply with Quote
well my version was modified for simple passwords.
Users only wanted alphanumerics non-vowel, vowel combo with a number at the end and that it could be kind of pronouncable.



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 02/09/2007 :  10:54:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote
CREATE PROCEDURE dbo.uspCreatePassword
(
	@UpperCaseItems SMALLINT,
	@LowerCaseItems SMALLINT,
	@NumberItems SMALLINT,
	@SpecialItems SMALLINT
)
AS

SET NOCOUNT ON

DECLARE	@UpperCase VARCHAR(26),
	@LowerCase VARCHAR(26),
	@Numbers VARCHAR(10),
	@Special VARCHAR(13),
	@Temp VARCHAR(8000),
	@Password VARCHAR(8000),
	@i SMALLINT,
	@c VARCHAR(1),
	@v TINYINT

-- Set the default items in each group of characters
SELECT	@UpperCase = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
	@LowerCase = 'abcdefghijklmnopqrstuvwxyz',
	@Numbers = '0123456789',
	@Special = '!@#$%&*()_+-=',
	@Temp = '',
	@Password = ''

-- Enforce some limits on the length of the password
IF @UpperCaseItems > 2000
	SET @UpperCaseItems = 2000

IF @LowerCaseItems > 2000
	SET @LowerCaseItems = 2000

IF @NumberItems > 2000
	SET @NumberItems = 2000

IF @SpecialItems > 2000
	SET @SpecialItems = 2000

-- Get the Upper Case Items
SET	@i = ABS(@UpperCaseItems)

WHILE @i > 0 AND LEN(@UpperCase) > 0
	SELECT	@v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@UpperCase) + 1,
		@c = SUBSTRING(@UpperCase, @v, 1),
		@UpperCase = CASE WHEN @UpperCaseItems < 0 THEN STUFF(@UpperCase, @v, 1, '') ELSE @UpperCase END,
		@Temp = @Temp + @c,
		@i = @i - 1

-- Get the Lower Case Items
SET	@i = ABS(@LowerCaseItems)

WHILE @i > 0 AND LEN(@LowerCase) > 0
	SELECT	@v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@LowerCase) + 1,
		@c = SUBSTRING(@LowerCase, @v, 1),
		@LowerCase = CASE WHEN @LowerCaseItems < 0 THEN STUFF(@LowerCase, @v, 1, '') ELSE @LowerCase END,
		@Temp = @Temp + @c,
		@i = @i - 1

-- Get the Number Items
SET	@i = ABS(@NumberItems)

WHILE @i > 0 AND LEN(@Numbers) > 0
	SELECT	@v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Numbers) + 1,
		@c = SUBSTRING(@Numbers, @v, 1),
		@Numbers = CASE WHEN @NumberItems < 0 THEN STUFF(@Numbers, @v, 1, '') ELSE @Numbers END,
		@Temp = @Temp + @c,
		@i = @i - 1

-- Get the Special Items
SET	@i = ABS(@SpecialItems)

WHILE @i > 0 AND LEN(@Special) > 0
	SELECT	@v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Special) + 1,
		@c = SUBSTRING(@Special, @v, 1),
		@Special = CASE WHEN @SpecialItems < 0 THEN STUFF(@Special, @v, 1, '') ELSE @Special END,
		@Temp = @Temp + @c,
		@i = @i - 1

-- Scramble the order of the selected items
WHILE LEN(@Temp) > 0
	SELECT	@v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Temp) + 1,
		@Password = @Password + SUBSTRING(@Temp, @v, 1),
		@Temp = STUFF(@Temp, @v, 1, '')

SELECT	@Password
If any of the parameters are passed onto the stored procedure as negative value, it is interpreted that you do not want a duplicate character from that specific group of characters. A positive value can get a duplicate value in that group of characters.


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 02/09/2007 11:17:25
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 02/09/2007 :  11:38:41  Show Profile  Reply with Quote
quote:
Originally posted by harsh_athalye

The SP you created is great, MVJ, but what I was saying that instead keeping keeping bitmap of characters, numbers and special characters, we can have a dictionary of uncommon words and may be mix those words to create simple-to-remember but difficult to guess passwords. (along with random uppercase letters and numbers for additional complexity)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"


Using a dictionary would be very hard to implement, especially considering things like different national languages; I don’t feel like keying in a million or so uncommon words.

Dictionary based attacks are one of the most common methods of breaking passwords, so I think it would be a big security hole. If you use combinations of words to increase the password strength, I think you would end up with a password that is at least as hard to remember (and type!) as the randomly generated passwords from my procedure.

The simple 2 syllable passwords from my procedure have 1.4 billion possible combinations, and 3 syllable passwords have 3.1 trillion possible combinations, even if you know the rules used to generate the password. Certainly not as strong as a completely random password, but harder to guess than your birthday, and easier to remember than dEOY&1nx, Wiz*R3hW, or RS*gdE9n.






CODO ERGO SUM
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 02/09/2007 :  12:12:12  Show Profile  Reply with Quote
quote:
Originally posted by Peso

CREATE PROCEDURE dbo.uspCreatePassword...]


This proc will certainly deliver complex passwords, but they don’t look easy to remember to me.
Password
---------
u#n4PesP
vYqI0tc(
XEhm1x_y
#zaqyX4P
FsAx8qn!
NnTy6_op
x7+JXycs
+UZz1hfz
(SSk4agv
My$0gdwH


CODO ERGO SUM
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 02/20/2007 :  23:21:31  Show Profile  Visit jezemine's Homepage  Reply with Quote
what I often use for passwords that are easy to remember but hard to guess is the first letter of each word of an easy-to-remember sentence. like "SQLTeam is the place to get your questions answered" would be Sitptgyqa. maybe add some punctuation for good measure: F,md,idgad! = Frankly, my dear, I don't give a damn!

might not be so useful for what you are doing here since you are looking for an algorithm though.


www.elsasoft.org
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 02/23/2007 :  12:48:04  Show Profile  Reply with Quote
Interesing challenge.
I think a good method of generating passwords which are secure, but easily remembered, would be to use 2nd-order or 3rd-order letter approximation and then substitute a few look-alike symbols for some of the characters.
This wouls be easier than storing hundreds or thousands of sample words, and might be more readable than the ones in MVJ's original post.
Martin Gardner wrote an article on word approximation, testing several methods for generating pseudo-words. One of the methods he used was MVJ's alternation consonant/vowel algorithm, but he considered this less "realistic" than 3rd-order approximation.

STAR SCHEMAS ARE NOT DATA WAREHOUSES!
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 02/23/2007 :  13:55:25  Show Profile  Reply with Quote
Also, you have to remember my requirement to meet typical password complexity requirements of a minimum of 8 characters, with at least one each of uppercase letters, lowercase letters, numbers, and special characters.

I would be interested in a link to Martin Gardner's article if you know it.





CODO ERGO SUM
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 02/23/2007 :  22:10:15  Show Profile  Reply with Quote
I looked for a link, but could find nothing. I was surprised to find very little on word approximation at all. I'll do some more searching, because it was an interesting article.

STAR SCHEMAS ARE NOT DATA WAREHOUSES!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000