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 ««12

Generate a random password Expand / Collapse
Author
Message
Posted Monday, November 17, 2008 8:27 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 28, 2014 9:20 AM
Points: 810, Visits: 2,130
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 ). 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
Post #603703
Posted Monday, November 17, 2008 8:37 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 28, 2014 9:20 AM
Points: 810, Visits: 2,130
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
Post #603709
Posted Monday, November 17, 2008 5:58 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #604053
Posted Tuesday, November 18, 2008 8:49 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, August 11, 2014 9:39 AM
Points: 1,932, Visits: 656
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
Post #604470
Posted Tuesday, February 10, 2009 10:11 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 28, 2014 9:20 AM
Points: 810, Visits: 2,130
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
Post #653960
Posted Tuesday, February 10, 2009 10:18 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 28, 2014 9:20 AM
Points: 810, Visits: 2,130
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
Post #653978
Posted Wednesday, July 22, 2009 8:26 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 29, 2014 3:07 AM
Points: 200, Visits: 128
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

Post #757471
Posted Wednesday, May 15, 2013 8:37 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 7:54 AM
Points: 548, Visits: 168
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!
Post #1453133
Posted Wednesday, May 15, 2013 9:51 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1453200
Posted Tuesday, June 4, 2013 9:36 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 28, 2014 9:20 AM
Points: 810, Visits: 2,130
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.


Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
Post #1459836
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse