Generate a random password

  • Comments posted to this topic are about the item Generate a random password

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

  • Nice !

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

  • 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?

  • woops...

    I did not try to run and test the code.

    Thanks for doing this. Very useful.

  • Works great and I actually needed something like this today! Thanks!

     

  • 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?

    --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.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • One additional thing I'd like to see is the ability to force at least one number and/or one special character.

     

  • 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 :hehe:). 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.

  • RML51:

    Unless you will not allow the users to change their password to one of their own liking -- for instance banks DO allow users to change their PIN number and others do supply a password intended to be changed by the user, then I do not see the point in enforcing a pattern to help the users to remember their password.

    If you were to allow users to change password, you could implement instead a password validation routine that would filter out the most commonly used passwords such as "Rolex", "guest", "admin", "sysadmin"" etc. Specify a minimum length, force the use of special characters like !, $, {, }, etc. and a non -zero count of upper and lower case alphabetic characters.

    You could also set a maximum length and ban the use of the : character. Would help (no guarantees) reject injection attacks and scripts if you have poorly written applications.

    The pattern enforcement scheme would be intended at rejecting non compliant user-entered passwords instead of helping users to remember a password.

    And even if you did supply a pattern, if it contains random characters, this would not help the users. Some of them out there really have a hard time with spotting patterns. If they can't remember a password, I would not bank too much on them remembering a pattern either.

  • 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.

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

  • 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 :hehe:). 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

  • 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

  • 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.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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

  • 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

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply