SQL 2000 password aging and enforcement

[fredong]

Anybody knows how to setup a SQL 2000 password aging and enforcement? I need to setup user password expire in 45 days and have a minimum password of Alphanumerics with a CAP letter and a special character. Please advise. Thanks

 

[icemel]

I think this has to be done at the o/s level - and would probably only work with windows authenticated users...

 

[fredong]

Our environment is using SQL Authentication not Windows. Thus, I need to set this up for SQL logins only. Any advices are welcome. Thanks,

 

[SQLBill]

How do they create their password in the first place? Do you have them do it on Enterprise Manager or some other application? If some other application, that's where I would have the checking/validation done. Maybe create a table that maintains their name and the date they change their password. Have the application check that file.

-SQLBill

Posting advice: FAQ481-4875

 

[fredong]

The DBAs created for them. This is done through the Enterprise Manager or SQL Query Analyzer. Any ideas how this can be setup to remind the DBAs that users password has expire after 45 days? Thanks.

 

[SQLBill]

IF you can force the DBAs to NOT use Enterprise Manager, there might be a way.

You would have to create a script that uses sp_password. The script would have to have validation checks to make sure the password met your requirements. Then you would have to create a table that held login names and date of password. Then have the password script check that table for the login. If it's not there, insert the new login and add the date. If it is there, update the date. Lastly, have a job that runs once a day and checks that table for date columns that are too old.

The script could be run in Query Analyzer or via another Application (such as a Microsoft Access form).

-SQLBill

Posting advice: FAQ481-4875

 

[fredong]

That is a good idea. Do you mean I modify the current sp_password to tailor my needs or should I create another sp_password system procedures? Do you have anything in place that I can use as a guide? Thanks.

 

[SQLBill]

Never modify the original stored procedure. Copy it, give it a new name and make your changes to that procedure.

I don't do this as we don't have user logins. All of our users login via an application that has it's own login.

-SQLBill

Posting advice: FAQ481-4875

 

[Catadmin]

fredong,

According to Microsoft, there is no official way to enforce password policies on SQL logins for SQL Server 2000. I don't know if SQLBill's solution will work (though his ideas usually do), but if it doesn't, that's why.

SQL Server 2005, on the other hand, gives you the ability to enforce domain level security policies on SQL Server users regardless of whether they are using Windows or Mixed Authentication.

BTW, forgive me if you already know this, but you can't be using only SQL Server Authentication. SQL allows Windows Only or SQL & Windows together, but not SQL alone. So, even if you only have SQL logins that are not mapped to Windows Accounts, it does not prevent Windows accounts being mapped to SQL Server in the future.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SQLBill]

Here's something I've been working on.....feel free to test it out and make any changes to it. It's based on sp_password. I've been working on trying to get equipment to create a test system, but so far - no luck. So here's my disclaimer: This hasn't been tested by me.

You might want to add code to require the login name. Check the BOL, look up sp_password for help.

/* 
   Program Name:  usp_passwdtest.sql
   Programmer:    William Fleming
   Date Created:  9 June 2004
   Last Updated:  17 June 2004
   Purpose: To test validation of passwords
   Dependancies: None.
*/

CREATE PROCEDURE usp_passwdtest
  @old VARCHAR(100),
  @new VARCHAR(100)
AS
  --check that new and old passwords are different
  IF @new = @old
    BEGIN
     RAISERROR ('New password must not be the same
          as the old password.', 16,1)
     RETURN (1)
    END
  --check that the length of the new password is 8 chars or more
  ELSE
  IF LEN(@new) < 8
    BEGIN
     RAISERROR ('New password must be eight characters or longer.',16,1)
     RETURN (1)
    END
  --check that the password uses at least one number
  ELSE
  IF @new NOT LIKE '%[0-9]%'
    BEGIN
     RAISERROR ('New password must use at least one number.',16,1)
     RETURN (1)
    END
  --check that the password uses at least one letter
  ELSE
  IF @new NOT LIKE '%[a-z]%'
    BEGIN
     RAISERROR ('New password must use at least one letter.',16,1)
     RETURN (1)
    END
  --check that the password uses at least special character
  ELSE
  IF @new NOT LIKE '%[!@#$%^&*()]%'
    BEGIN
     RAISERROR ('New password must use at least one special character.',16,1)
     RETURN (1)
    END
  --if the password passes validation, accept it
  ELSE
   PRINT 'Password acceptable'

-SQLBill

Posting advice: FAQ481-4875

 

[fredong]

Thanks SQLBill.

 

[SQLBill]

No problem. I hope it works for you. Combine it with a table to track the login and date/time of last password change and you should have what you want.

Catadmin - thanks for the nice words and pat on the back.

-SQLBill

Posting advice: FAQ481-4875