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


Generate a random password


Generate a random password

Author
Message
GabyYYZ
GabyYYZ
SSC Eights!
SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)

Group: General Forum Members
Points: 925 Visits: 2336
RML51 (11/14/2008)
No knock on the script, but I question the value of truly random passwords.

I think I have a pretty good memory. I have my credit card number memorized (okay - maybe that's a bad thing :heheSmile. For passwords I think there should be some sensible pattern.

I tend to build passwords (at least 9 char) using the user's initials mixed-up, or a misspelling of their name with odd capitalization. I usually include part of they phone number, and/or birth date, and I always include a special character or two. The point is that it's something that forms a memorable pattern to the user. I think I can do this and still maintain a high level of security.


The perfect use, at least for us, is for any SQL instance we install. All SA accounts need a different password, otherwise if only a few or one were used, one compromised server would quickly become many. We store these passwords in another location, a secured flat file with password, and anytime we need to access that server we look it up.

Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein

GabyYYZ
GabyYYZ
SSC Eights!
SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)

Group: General Forum Members
Points: 925 Visits: 2336
nelsonj (11/13/2008)
The password generator does a nice job, but while testing the code in QA, I noted that the proc does seem to return an "abundant" number of 'repeats' in any given password. In some generated passwords, I count up to three pairs of the same letters or characters. Can this be considered acceptable for 10 character passwords?


Hmmm...that's strange. Obviously, you get the occassional repeating character due to chance, even in 10 character passwords. But if you run the script repeatedly, you feel you are getting more than a normal number of repeating characters (not including case differences)? I'm not sure why that would occur.

Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88100 Visits: 41128
Gaby A. (11/17/2008)
Jeff Moden (11/13/2008)
Pretty nice, Gaby... your article and some of the comments above gave me a couple of ideas for an article with just a pot-wad of tricks in it... Ok if I reference your article?


Hey Jeff, sorry about the delayed response. Please feel free to use this.


Thanks, Gaby.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
nelsonj-902869
nelsonj-902869
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2835 Visits: 778
Gaby: The repeating characters, either caps or lower case aren't a big deal. The first time I tested the proc, it just seemed to have an inordinate number of paired repeats. I went back this morning and tested again, and now there are much fewer "paired" characters. I also tried just the alpha setting and I personally like that result much better as a 'password'. Character representations are easier for me to remember, instead of having the mixed special characters in the phrase as well.
No big deal, but I was just curious if perhaps having the routine NOT use a character that has already been used in the password would make the resulting phrase a "better" password or not. I realize the code to do this would be really intense and may not be worth the effort. I guess it all depends on how you look at password construction - with or without repeating characters.
Thanks for the proc. I will use it in the future. JT Nelson
GabyYYZ
GabyYYZ
SSC Eights!
SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)

Group: General Forum Members
Points: 925 Visits: 2336
nelsonj (11/18/2008)
Gaby: The repeating characters, either caps or lower case aren't a big deal. The first time I tested the proc, it just seemed to have an inordinate number of paired repeats. I went back this morning and tested again, and now there are much fewer "paired" characters. I also tried just the alpha setting and I personally like that result much better as a 'password'. Character representations are easier for me to remember, instead of having the mixed special characters in the phrase as well.
No big deal, but I was just curious if perhaps having the routine NOT use a character that has already been used in the password would make the resulting phrase a "better" password or not. I realize the code to do this would be really intense and may not be worth the effort. I guess it all depends on how you look at password construction - with or without repeating characters.
Thanks for the proc. I will use it in the future. JT Nelson

Here's a modified form of the script. It takes an argument @uniquechars. If set to 1, gradually shrinks the @string variable by removing the chosen character. The exception is if your @string is less than your @passlen, it won't work (so for large passwords, you have no choice but to use repeating characters).


-- This generates a random password, defaulting to 10 characters
create procedure msdb.dbo.GetPass
@passlen int = 10, @uniquechars int = 0, @charset int = 0 -- 2 is alphanumeric + special characters,
-- 1 is alphanumeric, 0 is alphabetical only
as
set nocount on
if (@passlen > 8000 or @passlen < 1) -- Let's not go crazy here
select @passlen = 10

declare @password varchar(8000), @string varchar(256), @numbers varchar(10), @extra varchar(50),
@stringlen int, @index int, @passval varchar(1)

-- no 1, l, I, 0, O which can cause confusion
select @string = 'ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnopqrstuvwxyz' -- same as @charset = 0
select @numbers = '23456789'
select @extra = '>_!@#$%&=?<' -- add more special characters if you want

if @charset = 2
select @string = @string + @numbers + @extra
else if @charset = 1
select @string = @string + @numbers
-- else assume @extra is 0 and @string is just letters. Feel free to modify these criteria as you see fit

select @stringlen = len(@string)

select @password = ''

-- This check is if your password length exceeds the number of unique characters. If so, the @uniquechars setting
-- you turned on (1) is turned back off.

if ((@stringlen < @passlen) and (@uniquechars = 1))
select @uniquechars = 0

while (@passlen > 0)
begin
-- For the random part here, use rand() or, preferably, newid()
select @index = (abs(checksum(newid())) % @stringlen) + 1
select @passval = substring(@string, @index, 1)
if @uniquechars = 1 -- modified section that removes repeating letters
select @string = replace(@string, @passval, '')
select @password = @password + @passval
select @passlen = @passlen - 1
end
select @password
go



Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein

GabyYYZ
GabyYYZ
SSC Eights!
SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)

Group: General Forum Members
Points: 925 Visits: 2336
J (11/13/2008)
Nice !

There also might be confusion between letter "G" and number 6...


Definitely, feel free to update the script to do so. However, I've also submitted an updated script that includes that. It should be posted to the main topic window soon.

Cheers!

Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein

met-rx
met-rx
SSC Veteran
SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)

Group: General Forum Members
Points: 200 Visits: 162
very nice, although id suggest one thing

link to a dw to hold all of the previously generated passwords, and then check if the generated password has been generated before:

if so, run the statement again

if not, store the password in the dw and output the result
Mithrandir
Mithrandir
SSChasing Mays
SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)

Group: General Forum Members
Points: 614 Visits: 184
Nice and handy script!

So far I simply used part of a New_Id() as a password, but having more complex scenarios is better.

Thanks!
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88100 Visits: 41128
I'll warn again that such pasword generators have a pretty good potential for creating some pretty nasty swear words. At least take out the vowels to make it a little safer in English. No guarantees on vowel removal helping at all in languages other than English.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GabyYYZ
GabyYYZ
SSC Eights!
SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)

Group: General Forum Members
Points: 925 Visits: 2336
Jeff Moden (5/15/2013)
I'll warn again that such pasword generators have a pretty good potential for creating some pretty nasty swear words. At least take out the vowels to make it a little safer in English. No guarantees on vowel removal helping at all in languages other than English.

I definitely agree...but then again, in our shop, we'd probably not think twice about swear-sounding words. :-D

Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein

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