Adding enhanced password constraints to SQL Server

Article
03/29/2011

[Prior Post in Series] [Next Post in Series]

In my prior posts on applying PCIDSS standards to protect your company’s data I showed now a DDL trigger (FOR LOGON) may be used to enhance logon security. In this post I will look at a different pattern to improve password constraints.

In this post I will look at these facets of Payment Card Industry (PCI) Data Security Standard Requirements and Security Assessment Procedures Version 2.0 (PCIDSS)

Change user passwords at least every 90 days.
Require a minimum password length of at least seven characters.
Use passwords containing both numeric and alphabetic characters.
Do not allow an individual to submit a new password that is the same as any of the last four passwords he or she has used.
The ability to set password complexity.

What is in the box?

Enforce password policy

This results in the password passing to the NetValidatePasswordPolicy API for validation against windows policies set on the box that SQL Server is located on. Starting with Windows 2003, the windows password policy checks:

Password minimum length,
Password history (password can’t be used if it is the same as previous N passwords),
Minimum and Maximum Password Age
Complexity Requirements

Since this is a SQL Server blog, I will not go into details, apart from showing the dialogs located at Control Panel / Administrative Tools/ Local Security Policy.

For further information see:

Enforce password expiration

To enable this, password policy must be checked.

The Coding Solution

After setting Windows policy, the only issue remaining is enforcing this policy. Enforce password policy and Enforce password expiration must be selected whenever a change of password occurs. This is done with a DDL trigger such as the one shown below.

 CREATE trigger [t_PasswordChange]
ON ALL server for ALTER_LOGIN, CREATE_LOGIN
As
DECLARE @EventData XML
DECLARE @CommandText nvarchar(max)      
SET @EventData=EVENTDATA()
SET @CommandText =  @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')
IF CHARINDEX('CHECK_EXPIRATION=ON, CHECK_POLICY=ON', @CommandText ) < 1
BEGIN
   RAISERROR('Expiration and Policy must be on',16,1)
   ROLLBACK
END

The result of trying to change a password or create a user without both Expiration and Policy on is:

The RAISERROR message does not bubble through. This means that you need to document well what you have done. I should also mention where you can find these triggers in SSMS, they are under Server Objects as shown below.

SQL Only Password Change Enforcement

It is possible to force a password change at the SQL level for SQL Login (Not local or domain logins) but things can get a little complex depending on how SQL Logins are used.

First, we create a table in the Security database cited in earlier posts:

 CREATE TABLE [dbo].[PasswordChange](
    [LoginName] [nvarchar](128) NOT NULL,
    [LastPasswordChange] [datetime] NOT NULL Default(GetDate()),
 CONSTRAINT [PK_PasswordChange] PRIMARY KEY CLUSTERED 
(
    [LoginName] ASC
))

Capturing When a Password is Changed

Next we set up a trigger to record when SQL Server passwords are changed.

 CREATE trigger [t_PasswordChangeWhen]
ON ALL server for ALTER_LOGIN, CREATE_LOGIN
As
DECLARE @EventData XML
DECLARE @CommandText nvarchar(max)      
DECLARE @LoginName SysName      
SET @EventData=EVENTDATA()
SET @CommandText =  @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')
SET @LoginName = RTRIM(
 LTRIM(
  Replace(
   Replace(
    SubString(@CommandText ,
     charindex('LOGIN',@CommandText)+5,
     charindex('WITH',@CommandText)- charindex('LOGIN',@CommandText)-5)
   ,'[','')
  ,']','')
 )
) 
IF CHARINDEX('PASSWORD=N''******''', @CommandText ) > 0
BEGIN
    IF NOT EXISTS(SELECT 1 FROM [Security].[dbo].[PasswordChange]            WHERE [LoginName]=@LoginName)
    BEGIN
   INSERT INTO [Security].[dbo].[PasswordChange]
           ([LoginName],[LastPasswordChange])
     VALUES(@LoginName,GetDate(),0)
   END
   ELSE
   BEGIN
    UPDATE [Security].[dbo].[PasswordChange]    
        SET [LastPasswordChange]=GetDate()
        WHERE [LoginName]=@LoginName
   END
END

Some manipulation of the TSQLCommand is needed to get a consistent login name.

The following trigger disables an account with an expired password.

 CREATE TRIGGER t_Logon_ChangePasswordCheck
ON ALL SERVER 
FOR LOGON
AS
BEGIN
Declare @DenyAt int
Declare @Data Xml
DECLARE @UserName varchar(120)
Set @Data=EVENTDATA()
SET @DenyAt=37
SET @UserName=@Data.value('(EVENT_INSTANCE/LoginName)[1]','varchar(120)')
IF NOT EXISTS(SELECT 1 FROM [Security].[dbo].[PasswordChange] WHERE [LoginName]=@UserName)
   INSERT INTO [Security].[dbo].[PasswordChange]
           ([LoginName],[LastPasswordChange],[Warnings])
     VALUES(@UserName,GetDate(),0)
   END
IF EXISTS(SELECT 1 FROM [Security].[dbo].[PasswordChange] 
    WHERE [LoginName]=@UserName 
    AND DateAdd(dd,@DenyAt,[LastPasswordChange]) < GetDate())   
    ROLLBACK TRAN

The problem is that it is not possible to display a message to the user advising them to change the password (RAISERROR, PRINT and SELECT do not bubble up).

Summary

There is little native support in SQL Server to support PCI password policies. SQL Server uses Window’s password policy and this is where you should implement passwords policy. The trigger above prevents any SQL Logins being exempt from the Window’s password policy unless you explicitly white-list in the trigger.