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),
        @changedby       varchar(30),
        @cutoffdate      datetime


select @changedby = suser_name()

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

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

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

Use sp_addmessage to add the user-defined message 22001. A raiserror 22001 indicates a custom password-complexity check error and leads to a failure of sp_addlogin or sp_password.

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.