[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 Procedu... (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:
- Configuring Password Policies
- Configuring Account Lockout Policies
- Account Passwords and Policies in Windows Server 2003
- Account Lockout Policy
- Windows Server 2008 - Fine Grained Password Policy Walkthrough
-
Appendix A: Fine-Grained Password and Account Lockout Policy Review
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.
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:
[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.
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]
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.
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.