Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Complex Random Password Generator Expand / Collapse
Author
Message
Posted Thursday, August 19, 2010 12:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 7:50 AM
Points: 26, Visits: 120
Comments posted to this topic are about the item Complex Random Password Generator
Post #971630
Posted Thursday, August 19, 2010 2:42 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, November 21, 2013 11:33 PM
Points: 1,481, Visits: 1,959
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 )
Post #971678
Posted Thursday, August 19, 2010 6:52 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, March 13, 2014 1:08 AM
Points: 53, Visits: 247
I kind of agree... it won't fly.
Post #971827
Posted Thursday, August 19, 2010 7:57 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:06 AM
Points: 2,404, Visits: 7,311
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




Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #971887
Posted Thursday, August 19, 2010 9:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 7:50 AM
Points: 26, Visits: 120
tommyh (8/19/2010)
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 )


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).
Post #971989
Posted Thursday, August 19, 2010 9:44 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:06 AM
Points: 2,404, Visits: 7,311
I think my problem is that it's 2008 and I'm not able to connect to my 2008 box to test it today


Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #972000
Posted Thursday, August 19, 2010 4:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 7:50 AM
Points: 26, Visits: 120
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.
Post #972227
Posted Sunday, November 21, 2010 8:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 29, 2010 7:39 PM
Points: 1, Visits: 16
I like it. the "FOR XML PATH('')" is very useful to transform column into a row
Post #1024129
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse