Adding enhanced password constraints to SQL Server

[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.

Denying the Login of an Expired Password

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).


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.

Comments (0)

Skip to main content