none
can stored procedure generate strong password with 6 characters

Answers

  • Here is something I tossed together as a basis of generating the password.  You could make the used password related to the user...I only made it 5 characters, but adding a few more would be very easy.  You can mod the algorithms/requirements to get any mix of characters you would like. 

    create table usedPassword
    (
        usedPasswordId int identity primary key,
        password varchar(5) unique
    )
    Go
    create procedure password$generate
    as

        --expand this to include all characters you want to include in your password
        declare @letter table (letterId int, letter char(1))
        insert into @letter --set up ranges for letterId that you can use in where clauses later
        select 1,'a'
        union all
        select 2,'b'
        union all
        select 3,'c'
        union all
        select 4,'d'
        union all
        select 5,'e'
        union all
        select 100,'#'
        union all
        select 101,'^'
        union all
        select 102,'@'
        union all
        select 201,'1'
        union all
        select 202,'2'

        declare @password char(5)

        while 1=1
        begin
            select top 1 @password = l1.letter + l2.letter + l3.letter + l4.letter + l5.letter
                    --force first letter to be a character
            from   (select letter from @letter where letterId between 1 and 99) as l1
                    --do as many cross joins as you need characters, if it is too slow, you could break down into N
                    --individual queries concatenated...shouldn't be too bad if you don't allow more than the 50 or so
                    --characters that would be expected
                     cross join @letter as l2
                     --if you want to control the
                     cross join @letter as l3
                     cross join @letter as l4
                     cross join @letter as l5

            --use a where clause to make sure the password meets criteria
            where  l1.letter + l2.letter + l3.letter + l4.letter + l5.letter like '%[0-9]%' --include numbers
              and  l1.letter + l2.letter + l3.letter + l4.letter + l5.letter like '%[#^@]%' --include special characters
              and  l5.letter like '%[a-z0-9]%' --end with a letter or number
            order by NEWID()
            if not exists(select *
                          from   usedPassword
                          where  password = @password)
                insert into usedPassword
                select   @password
                select @password as password
                break
         end       

    go
    password$generate


    http://drsql.spaces.msn.com
    Thursday, June 11, 2009 10:36 PM
  • Strong password must be at least 8 chars in length.

    You would have to store used passwords in a password log table to prevent reuse.

    You can create a SQL Agent job to check for expiring passwords and schedule it nightly.
    Kalman Toth, SQL Server 2008 Training, SSAS, SSIS, SSRS, BI: www.SQLUSA.com
    Thursday, June 11, 2009 9:52 PM
  • You have to store each password in  the PasswordHistory table.  There is no other way.

    The following T-SQL script will generate random passwords until the password check section is satisfied.

    WHILE (1 = 1) 
      BEGIN 
        DECLARE  @Password VARCHAR(8) 
         
        SET @Password = '' 
         
        WHILE (Len(@Password) < 8) 
          SET @Password = @Password + Char(33 + Round(Rand(Cast(Newid() AS VARBINARY)) * 93,0,-1)) 
         
        SELECT @Password 
         
        -- PLACE PASSWORD STRENGTH TEST HERE 
        IF (Ascii(Left(@Password,1)) BETWEEN Ascii('A') AND Ascii('Z')) 
          BREAK 
      END -- outer while 
    
    GO 
    
    -- U9Vr*u/]


    Kalman Toth, SQL Server 2008 Training, SSAS, SSIS, SSRS, BI: www.SQLUSA.com
    Friday, June 12, 2009 9:33 AM

All replies

  • Strong password must be at least 8 chars in length.

    You would have to store used passwords in a password log table to prevent reuse.

    You can create a SQL Agent job to check for expiring passwords and schedule it nightly.
    Kalman Toth, SQL Server 2008 Training, SSAS, SSIS, SSRS, BI: www.SQLUSA.com
    Thursday, June 11, 2009 9:52 PM
  • Here is something I tossed together as a basis of generating the password.  You could make the used password related to the user...I only made it 5 characters, but adding a few more would be very easy.  You can mod the algorithms/requirements to get any mix of characters you would like. 

    create table usedPassword
    (
        usedPasswordId int identity primary key,
        password varchar(5) unique
    )
    Go
    create procedure password$generate
    as

        --expand this to include all characters you want to include in your password
        declare @letter table (letterId int, letter char(1))
        insert into @letter --set up ranges for letterId that you can use in where clauses later
        select 1,'a'
        union all
        select 2,'b'
        union all
        select 3,'c'
        union all
        select 4,'d'
        union all
        select 5,'e'
        union all
        select 100,'#'
        union all
        select 101,'^'
        union all
        select 102,'@'
        union all
        select 201,'1'
        union all
        select 202,'2'

        declare @password char(5)

        while 1=1
        begin
            select top 1 @password = l1.letter + l2.letter + l3.letter + l4.letter + l5.letter
                    --force first letter to be a character
            from   (select letter from @letter where letterId between 1 and 99) as l1
                    --do as many cross joins as you need characters, if it is too slow, you could break down into N
                    --individual queries concatenated...shouldn't be too bad if you don't allow more than the 50 or so
                    --characters that would be expected
                     cross join @letter as l2
                     --if you want to control the
                     cross join @letter as l3
                     cross join @letter as l4
                     cross join @letter as l5

            --use a where clause to make sure the password meets criteria
            where  l1.letter + l2.letter + l3.letter + l4.letter + l5.letter like '%[0-9]%' --include numbers
              and  l1.letter + l2.letter + l3.letter + l4.letter + l5.letter like '%[#^@]%' --include special characters
              and  l5.letter like '%[a-z0-9]%' --end with a letter or number
            order by NEWID()
            if not exists(select *
                          from   usedPassword
                          where  password = @password)
                insert into usedPassword
                select   @password
                select @password as password
                break
         end       

    go
    password$generate


    http://drsql.spaces.msn.com
    Thursday, June 11, 2009 10:36 PM
  • tnx for all the help
    main problem is

    1. how to check if in user table if he use this password the past (like the user canot use the same pssword twice) and how to check it

    because whan the user change the password he actually use UPDATE so he erase the old value

    any idea how to deal with it

    TNX

    Friday, June 12, 2009 5:52 AM
  • You have to store each password in  the PasswordHistory table.  There is no other way.

    The following T-SQL script will generate random passwords until the password check section is satisfied.

    WHILE (1 = 1) 
      BEGIN 
        DECLARE  @Password VARCHAR(8) 
         
        SET @Password = '' 
         
        WHILE (Len(@Password) < 8) 
          SET @Password = @Password + Char(33 + Round(Rand(Cast(Newid() AS VARBINARY)) * 93,0,-1)) 
         
        SELECT @Password 
         
        -- PLACE PASSWORD STRENGTH TEST HERE 
        IF (Ascii(Left(@Password,1)) BETWEEN Ascii('A') AND Ascii('Z')) 
          BREAK 
      END -- outer while 
    
    GO 
    
    -- U9Vr*u/]


    Kalman Toth, SQL Server 2008 Training, SSAS, SSIS, SSRS, BI: www.SQLUSA.com
    Friday, June 12, 2009 9:33 AM
  • Can't it just be tested against NOT LIKE '%[^a-z A-Z]%' ?
    Friday, June 12, 2009 11:16 AM
  • i it possible
    to create a trigger on update
    befor update
    copy to PasswordHistory table
    and check if he use pssword twice

    TNX
    Friday, June 12, 2009 12:33 PM