can stored procedure generate strong password with 6 characters

Question

1. can stored procedure generate strong  password  with at least  6 characters
2. how to check if in user table if he use this password the past (like the user canot use the same pssword twice) check
3. the user must change password after 30 days

(maybe combine this stored procedure  From this threads)
stored procedure check if it strong password

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/8a3791c6-5049-42c6-962e-cf4c8a77f9b8

tnx

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

Can't it just be tested against NOT LIKE '%[^a-z A-Z]%' ?

Answer 6

i it possible
to create a trigger on update
befor update
copy to PasswordHistory table
and check if he use pssword twice

TNX