SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Complex Random Password Generator


Complex Random Password Generator

Author
Message
gklundt
gklundt
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 140
Comments posted to this topic are about the item Complex Random Password Generator
tommyh
tommyh
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1762 Visits: 2000
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 ;-))
Kristian Ask
Kristian Ask
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 251
I kind of agree... it won't fly.
Cadavre
Cadavre
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3872 Visits: 8472
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




Forever 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


Craig Wilkinson - Software Engineer
LinkedIn
gklundt
gklundt
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 140
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).
Cadavre
Cadavre
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3872 Visits: 8472
I think my problem is that it's 2008 and I'm not able to connect to my 2008 box to test it today :-P


Forever 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


Craig Wilkinson - Software Engineer
LinkedIn
gklundt
gklundt
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 140
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.
Richard Cleland
Richard Cleland
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 16
I like it. the "FOR XML PATH('')" is very useful to transform column into a row
Iwas Bornready
Iwas Bornready
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13648 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search