Complex Random Password Generator

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Gordon Klundt » Complex Random Password Generator

Complex Random Password Generator

Rate Topic

Display Mode

Topic Options

Author

Message

gklundt

Posted Thursday, August 19, 2010 12:06 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, March 04, 2013 3:42 PM
Points: 26, Visits: 110

Comments posted to this topic are about the item Complex Random Password Generator

Post #971630

tommyh

Posted Thursday, August 19, 2010 2:42 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Friday, May 10, 2013 3:15 AM
Points: 1,476, Visits: 1,943

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 Wink

)

Post #971678

Kristian Ask

Posted Thursday, August 19, 2010 6:52 AM

Valued Member

Group: General Forum Members
Last Login: Monday, May 13, 2013 3:13 AM
Points: 53, Visits: 237

I kind of agree... it won't fly.

Post #971827

Cadavre

Posted Thursday, August 19, 2010 7:57 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:16 AM
Points: 2,236, Visits: 6,486

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" Wink

Post #971887

gklundt

Posted Thursday, August 19, 2010 9:35 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, March 04, 2013 3:42 PM
Points: 26, Visits: 110

tommyh (8/19/2010)

)

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

Cadavre

Posted Thursday, August 19, 2010 9:44 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:16 AM
Points: 2,236, Visits: 6,486

I think my problem is that it's 2008 and I'm not able to connect to my 2008 box to test it today Tongue

LinkedIn | Blog coming soon (for sufficiently large values of "soon" Wink

Post #972000

gklundt

Posted Thursday, August 19, 2010 4:26 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, March 04, 2013 3:42 PM
Points: 26, Visits: 110

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

Richard Cleland

Posted Sunday, November 21, 2010 8:51 PM

Forum 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 »

Permissions