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)
The ability to set password complexity.
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:
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:
To enable this, password policy must be checked.
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.
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.
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:
Next we set up a trigger to record when SQL Server passwords are changed.
Some manipulation of the TSQLCommand is needed to get a consistent login name.
The following trigger disables an account with an expired password.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.