August 19, 2010 at 12:06 am
Comments posted to this topic are about the item Complex Random Password Generator
August 19, 2010 at 2:42 am
Very complex passwords indead. However some observations
insert @tbl values(ascii('^'), 3)
Im not sure i would classify that as being benign. Since that char combined with another can create a single char. Like ê which is ^ + e.
Also have you done any statistics on how often "Too Complex or length is too short after 10 attempts" will happen? I have run the code a few times and i get that error a tad to often for it to be good. 31 errors in 1000 passwords. So about 1/32 is bad. Not that great odds (well maybe it was the odds of winning a million $ it would be ;-))
August 19, 2010 at 6:52 am
I kind of agree... it won't fly.
August 19, 2010 at 7:57 am
Don't overly like.
Off the top of my head: -
DECLARE @caps VARCHAR(24),
@lower VARCHAR(25),
@numbers VARCHAR(8),
@string VARCHAR(250)
SET @caps = 'ABCDEFGHJKLMNPQRSTUVWXYZ'
SET @lower = 'abcdefghijkmnopqrstuvwxyz'
SET @numbers = '23456789'
SET @string = @caps + @lower + @numbers;
WITH pass_cte (the_output)
AS (SELECT Substring(@caps, ( Abs(Checksum(Newid()))%Len(@caps) ) + 1, 1)
UNION ALL
SELECT Substring(@lower, ( Abs(Checksum(Newid()))%Len(@lower) ) + 1, 1)
UNION ALL
SELECT Substring(@lower, ( Abs(Checksum(Newid()))%Len(@lower) ) + 1, 1)
UNION ALL
SELECT Substring(@numbers, ( Abs(Checksum(Newid()))%Len(@numbers) ) + 1
, 1)
UNION ALL
SELECT Substring(@numbers, ( Abs(Checksum(Newid()))%Len(@numbers) ) + 1
, 1)
UNION ALL
SELECT Substring(@string, ( Abs(Checksum(Newid()))%Len(@string) ) + 1,
1)
UNION ALL
SELECT Substring(@string, ( Abs(Checksum(Newid()))%Len(@string) ) + 1,
1)
UNION ALL
SELECT Substring(@string, ( Abs(Checksum(Newid()))%Len(@string) ) + 1,
1)
UNION ALL
SELECT Substring(@string, ( Abs(Checksum(Newid()))%Len(@string) ) + 1,
1)
UNION ALL
SELECT Substring(@string, ( Abs(Checksum(Newid()))%Len(@string) ) + 1,
1)
UNION ALL
SELECT Substring(@string, ( Abs(Checksum(Newid()))%Len(@string) ) + 1,
1)
UNION ALL
SELECT Substring(@string, ( Abs(Checksum(Newid()))%Len(@string) ) + 1,
1))
SELECT TOP 1 (SELECT ( CAST (the_output AS VARCHAR(8000)) )
FROM pass_cte s2
FOR XML PATH('')) AS the_output
FROM pass_cte s1
August 19, 2010 at 9:35 am
tommyh (8/19/2010)
Very complex passwords indead. However some observationsinsert @tbl values(ascii('^'), 3)
Im not sure i would classify that as being benign. Since that char combined with another can create a single char. Like ê which is ^ + e.
Also have you done any statistics on how often "Too Complex or length is too short after 10 attempts" will happen? I have run the code a few times and i get that error a tad to often for it to be good. 31 errors in 1000 passwords. So about 1/32 is bad. Not that great odds (well maybe it was the odds of winning a million $ it would be ;-))
Yes, it started out as an exercise in selecting random values within a range, and I applied to password generation. Random passwords have been done several different ways, I just haven't seen one that tries to match AD complexity rules in t-sql form. This is an attempt that works for me and my organization, and it's a handy template we use probably 30 to 50 times a week.
As to the special characters, benign is in the eye of the beholder ... you can categorize your own degrees of dangerous or take special chars out altogether.
The "too complex or too short" message isn't an error. I just didn't want to spin too many cycles trying to come up with a password if it could never meet the criteria. You can take that governor off, but if you let it spin with criteria that can never be true, you'll end up killing it anyway. For example, if you set the password length to 3 and require 4 categories, you'll never get a password that meets your criteria. So I said "try 10 times and let me know." Set the password length to 4 and you'll see what I mean. My personal version of the script has retries as a variable (more complexity ... so I actually left that out).
August 19, 2010 at 9:44 am
I think my problem is that it's 2008 and I'm not able to connect to my 2008 box to test it today 😛
August 19, 2010 at 4:26 pm
skcadavre (8/19/2010)
Don't overly like.Off the top of my head: -
DECLARE @caps VARCHAR(24),@lower VARCHAR(25),
@numbers VARCHAR(8),
@string VARCHAR(250)
...
This isn't the first generator ever written, nor will it be the last. I just wanted to apply AD rules for password policy so password generation would pass before it was attempted. I'm going for slightly more elegant than banging a hammer on your keyboard, but less elegant than compiled code. There are many pathways. This is mine. I put in a lot of options because AD rules can be complex.
November 21, 2010 at 8:51 pm
I like it. the "FOR XML PATH('')" is very useful to transform column into a row
May 18, 2016 at 1:04 pm
Thanks for the script.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply