Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 Generate Password Procedure

Author  Topic 

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-08 : 23:16:18
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
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-09 : 03:23:43
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

22859 Posts

Posted - 2007-02-09 : 04:34:51
"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

11752 Posts

Posted - 2007-02-09 : 04:56:55
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)

7020 Posts

Posted - 2007-02-09 : 09:38:35
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

11752 Posts

Posted - 2007-02-09 : 09:57:26
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
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-09 : 10:02:34
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"
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-09 : 10:25:10
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

11752 Posts

Posted - 2007-02-09 : 10:48:26
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

30421 Posts

Posted - 2007-02-09 : 10:54:36
[code]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[/code]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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-09 : 11:38:41
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)

7020 Posts

Posted - 2007-02-09 : 12:12:12
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
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-02-20 : 23:21:31
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
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-02-23 : 12:48:04
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)

7020 Posts

Posted - 2007-02-23 : 13:55:25
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
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-02-23 : 22:10:15
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
   

- Advertisement -