Implement password expiration dates for SQL Server 2000

Introduction

This article discusses a method that you can use to implement password expiration dates for Microsoft SQL Server logins. The method uses one user-created table, and three stored procedures. This method is just one variation that you can use, and you can modify the method to fit your own needs.If you have Microsoft SQL Server running in Integrated Security mode under Microsoft Windows 2000 or a later version, you can rely on Windows for the expiration of the password. Whenever possible, we recommend that you use the Integrated Security mode for connections to SQL Server. Windows authentication helps provide security enforcement mechanisms such as stronger authentication protocols, and mandatory password complexity and expiration.The method discussed in this article applies to an instance of SQL Server that is running in mixed security mode.
1.Create a table in the master database that will hold the following:
•The user login ID.
•The date the password was last changed.
•The last password that was used

Note Only the system administrator (sa) should have access to this table

CREATE TABLE PW_DATE

(

SID VARBINARY(85),

DATE DATETIME,

OLD_PW SYSNAME NULL

)

2. Create a stored procedure that will determine whether the password has expired. If the password has expired, the procedure will then assign the login ID a new password, and the user must have the sa change it back. Additionally, you may also want to include other checks that you consider appropriate for your environment. The only two logins the procedure will not change the password for are the sa or the PROBE logins. The following example sets the password to expire after 30 days, and it lists all the users whose passwords have expired.

Note Only the sa should have EXECUTE permission.

CREATE PROCEDURE SP_PASSWORD_CHECK

(@secret nvarchar(128))

AS

DECLARE @sid varbinary(85)

DECLARE @date datetime

DECLARE @name nvarchar(128)

DECLARE crsPW

CURSOR FOR SELECT SID,[DATE] FROM PW_DATE WHERE DATEADD(DAY, 30, PW_DATE.DATE) <= GETDATE()

OPEN crsPW

FETCH NEXT FROM crsPW INTO @sid, @date WHILE (@@FETCH_STATUS=0)

BEGIN SELECT @name = [NAME] FROM syslogins WHERE sid = @sid AND syslogins.[name] NOT IN ('sa', 'probe') IF @@ROWCOUNT = 1 EXEC sp_password NULL, @secret, @name

FETCH NEXT FROM crsPW INTO @sid, @date

END

CLOSE crsPW

DEALLOCATE crsPW

3. Run the SP_PASSWORD_CHECK procedure nightly by using a Transact-SQL script that is similar to the following: USE MASTERGOEXEC SP_PASSWORD_CHECK 'gulliver'GO
To run the Transact-SQL script run nightly, add a Transact-SQL Script step to a New Job in SQL Server Agent, and then schedule the job to run nightly.

4. Instead of using the SP_ADDLOGIN and the SP_PASSWORD stored procedures, you will use two new stored procedures. The SP_CORP_ADDLOGIN stored procedure replaces the SP_ADDLOGIN stored procedure, and the SP_CORP_PASSWORD stored procedure replaces the SP_PASSWORD stored procedure.

To make sure that the old procedures are not used, revoke execute permissions for both the SP_ADDLOGIN stored procedure and for the SP_PASSWORD stored procedure.
Note You can modify the SP_ADDLOGIN and the SP_PASSWORD stored procedures. However, if you install an upgrade, make sure that you do not remove the SP_ADDLOGIN or the SP_PASSWORD stored procedures.
CREATE PROCEDURE SP_CORP_ADDLOGIN

@LOGIN_ID VARCHAR(30),

@PASSWD VARCHAR(30) = NULL,

@DEFDB VARCHAR(30) = NULL AS

EXEC SP_ADDLOGIN @LOGIN_ID, @PASSWD, @DEFDB

INSERT INTO PW_DATE (SID, DATE, OLD_PW)

VALUES

(SUSER_SID(@LOGIN_ID), GETDATE(), NULL)
GO


CREATE PROCEDURE SP_CORP_PASSWORD

@OLD VARCHAR(30) = NULL,

@NEW VARCHAR(30),

@LOGIN_ID VARCHAR(30) = NULL AS

EXEC SP_PASSWORD @OLD, @NEW, @LOGIN_ID IF (@LOGIN_ID = NULL) BEGIN

UPDATE PW_DATE SET DATE = GETDATE(), OLD_PW = @OLD WHERE SID = SUSER_SID ()

END

ELSE

BEGIN

UPDATE PW_DATE SET DATE = GETDATE(), OLD_PW = @OLD WHERE SID = SUSER_SID(@LOGIN_ID)

END
5. The final step is to initialize the system. If you have an active system with user login IDs, the sa should run the following query to load the PW_DATE table with the starting values:
INSERT INTO PW_DATE (SID, DATE, OLD_PW) SELECT SYSLOGINS.SID, GETDATE(), SYSLOGINS.PASSWORD FROM SYSLOGINS
Now, the system should be ready. You can vary this method to meet your own needs