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.
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.
Thanks man, I had to remove all the policies from all the accounts on a specific instance due to application testing. So below is a small piece of code should anyone require it that will remove it from all your users. You can execute the results to get it done and see what you are doing before you execute it... I know this goes almost every law of SQL security and best practices but it was requested by business ;)
Hello, I read your very useful article "How to configure password enforcement options for standard SQL Server login" - I had a quick question, what would password_hash mean in SQL server 2005 and is this related to password policy? Should I be auditing or reviewing this? What would it mean if it the column is 0 or 1. Please advise. Thank you!!