By: K. Brian Kelley | Comments (6) | Related: > Security
Problem
I understand that starting with SQL Server 2005, SQL Server could enforce password complexity and password expiration. But I don't understand how that works or what the parameters are for either. I want to be able to explain to an auditor what my SQL Server is enforcing. How do I figure this out?
Solution
Yes, starting with SQL Server 2005, SQL Server is able to support both password complexity requirements as well as password expiration. These are nice security features that make SQL Server more secure than in previous versions. However, within SQL Server, there are no parameters you can set with regards to password length, when a password gets locked out, and how long until the password expires. That's because SQL Server pulls that information from the operating system. You can see what the values are by checking the local security policy on your server.
To do this, go to Start >> Control Panel >> Administrative Tools >> Local Security Policy if you're using the Classic View for Control Panel. Otherwise, go to Start >> Control Panel>> Performance and Maintenance >> Administrative Tools >> Local Security Policy.
Once inside the Local Security Policy console, expand Account Policies and you should see two subfolders, Password Policy and Account Lockout Policy, like in Figure 1.
Figure 1:
Password Policy
The Password Policy subfolder contains the password complexity settings like:
- Password History - number of old passwords remembered
- Minimum Password Age - how long before another password change can be attempted
- Maximum Password Age - how old a password can be before it is expired
- Minimum Password Length - how many characters a password must be to be acceptable.
- Password must meet complexity requirements - enforces mixed case, etc., for the password
These values tell you the basics of what is acceptable for a password. They do not, however, tell you what would cause a password to be locked out and how long that lockout would last. That information is contained in the Account Lockout Policy folder.
Account Lockout Policy
The Account Lockout Policy folder has the following settings:
- Account Lockout Duration - how many minutes before a locked account is unlocked again
- Account Lockout Threshold - how many failed login attempts can occur before the account is locked out
- Reset Account Lockout Counter After - how long before the failed login attempts are reset to zero (assuming no successful logins have occurred since, since that automatically resets the failed logic count to zero).
Now if you go to change a setting, you may notice that it's grayed out. What this means is that the setting is being pushed down via Group Policy. It is being centrally managed by Active Directory and the setting cannot be overridden. If a SQL Server based login is configured to use the password policy, it will use these values.
It is entirely possible for a login not to use the password policy. Actually, there are three options for SQL Server logins:
- The SQL Server login doesn't do any password policy enforcement at all.
- The SQL Server login enforces password complexity and lockout, but not password expiration.
- The SQL Server login enforces password complexity, lockout, and expiration.
The way to see what logins fit which of these three options is easily done by query the catalog view sys.sql_logins.
The following query provides that information:
SELECT [name] , is_policy_checked , is_expiration_checked FROM sys.sql_logins;
- If the is_policy_checked column is 0, then the login fits the first category. Nothing is being enforced.
- If the is_policy_checked column is 1, but the is_expiration_checked is 0, then it fits the second category. The password complexity and lockout settings are used, but the password won't expire.
- If both columns are equal to 1, then the login fits the third and final category, and all the settings are used, including password expiration.
Next Steps
- Refer to these other login security tips:
About the author
K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
