Enabling custom password checks

Adaptive Server allows a system security officer to write user-defined stored procedures that enable custom password checks.

For example, to implement password history checks, create a new user table to store password histories:

create table pwdhistory
(
      name varchar(30)not null,  -- Login name.
      password varbinary(30)not null,  -- old password.
      pwdate datetime not null,  -- datetime changed.
      changedby varchar(30)not null  -- Who changed.
)
go

This user-defined stored procedure (sp_extrapwdchecks) can be called when specifying a new password to save it in an encrypted form in the pwdhistory table:

create proc sp_extrapwdchecks 
( 
@caller_password varchar(30), -- the current password of caller 
@new_password    varchar(30), -- the new password of the target acct 
@loginame        varchar(30) -- user to change password on
)
as 

begin 
declare @current_time    datetime, 
        @encrypted_pwd   varbinary(30), 
        @salt            varchar(8), 
        @changedby       varchar(30), 
        @cutoffdate      datetime 

select @changedby = suser_name() 
select @salt = null 

-- Change this line according to your installation. 
-- This keeps history of 12 months only. 
select @current_time = getdate(), 
       @cutoffdate = dateadd(month, -12, getdate()) 

delete master..pwdhistory 
    where  name = @loginame 
    and    pwdate < @cutoffdate 

select @salt = substring(password, 1, 8) from master..pwdhistory 
    where  pwdate = (select max(pwdate) from master..pwdhistory where     name=@loginame)
           and name=@loginame
if @salt is null 

begin 
select @salt = substring(hash(password_random(), 'sha1'), 1, 8)
end 

select @encrypted_pwd = @salt + hash(@salt + @new_password, 'sha1') 

if not exists ( select 1 from master..pwdhistory 
      where name = @loginame 
      and   password = @encrypted_pwd ) 
begin 
      insert master..pwdhistory 
      select @loginame, @encrypted_pwd, 
@current_time, @changedby 
return (0) 
end 
else 
begin 
      raiserror 22001  --user defined error message 
end 
end 
go

Use sp_addmessage to add the user-defined message 22001. A raiserror 22001 indicates a custom password-complexity check error.

The following user-defined stored procedure (sp_cleanpwdchecks) can be used to clean-up the password history using sp_extrapwdchecks.

create proc sp_cleanpwdchecks
(
            @loginame      varchar(30)
                        -- user to change password on
) 
as
begin

delete master..pwdhistory
where name = @loginame
end
      go

Once the two procedures above are defined and installed in the master database, they are called dynamically during the password complexity checks.