Hello everybody, in this article we want to show you how to configure Microsoft SQL Server Password Policy, Expiration Policy and Account Lockout Policy, this is simple but has a few nuances.
First of all, What is Password Policy?
With Password Policy you can define minimum password length, maximum password age, password complexity, restrict the use of previous passwords and so on.
What is Password Expiration Policy?
This one is so simple, You should change your accounts password after a period of time.
And finally, What is Account Lockout Policy ?
Account lockout policy feature can help you to lockout SQL Server logins after some invalid logon attempts, also you can define how long an account should be remain locked or determine the number of minutes that must elapse after a failed logon attempt before the failed logon attempt counter is reset to zero bad logon attempts.
How can you configure these policies for SQL Server ?
Remember that you can not configure these Policies only by SQL Server alone. This setting is coupled to Windows Group Policy settings. To configuring these features you have to use Windows Group Policy (“gpedit.msc” or “secpol.msc” command) and then you can ready to use them on SQL Server for your selected logins. Oh, do not forget that this configurations can be applied to both SQL Server Authentication and Windows Authentication logins. OK, Let me show you how to do this:
If you are not restricted through Group Policy, you can locally use gpedit.msc command to access your server’s Local Group Policy and configure Account Locking capabilities. After running “gpedit.msc”, go to Computer Configuration > Windows Settings > Security Settings > Account Policies. Now you can configure Account Lockout Policy subsection. Additionally, you can configure the password complexity and expiration under the Password Policy subsection (unlike Account Policy, which applies to all types of logins, Password Policy only applies to SQL Logins).
By configuring these features, SQL Server will inherit these settings for both SQL users and Domain\Windows users, and these same configurations will be applied to its logins. The configurable options for Password Policy are as follows:
Password Policy Settings:
- Enforce password history:
This setting defines how many unique passwords must be used before an old password can be reused. For example, if my current password is “Th334goore0!” then I can’t reuse that password until I’ve changed my password 24 times (or whatever number the policy is set to). This setting is useful so users don’t keep reusing the same password. The default setting is 24
- Maximum password age:
This setting defines how long in days a password can be used before it needs to be changed. The default setting is 42 days
- Minimum password age
This setting determines how long a password must be used before it can be changed. The default setting is 1 day
- Minimum password length
This setting determines how many characters a password must have. The default is 7. This means my password must contain at least 7 characters.
- Password must meet complexity requirements
If enabled passwords must meet these requirements:
Not contain the user’s account name or parts of the user’s full name that exceed two consecutive characters
Be at least six characters in length
Contain characters from three of the following four categories:
- English uppercase characters (A through Z)
- English lowercase characters (a through z)
- Base 10 digits (0 through 9)
- Non-alphabetic characters (for example, !, $, #, %)
This is enabled by default
- Store passwords using reversible encryption
This setting determines if the operating system stores passwords using reversible encryption. This is essentially the same as storing plain text versions of passwords. This policy should NEVER be set to enabled unless you have some very specific application requirements.
Account Lockout Policy Settings:
- Account Lockout Threshold:
The number of failed sign-in attempts (with an incorrect password) before the user’s account is locked;
- Account Lockout Duration:
The duration of the account lock, during which the user will not be able to log into the domain;
- Reset account lockout counter after:
The number of minutes after which the Account Lockout Threshold counter will be reset.
Now you can go to SQL Server Management Studio, choose your login and enable your policies:
Remember that, To enable Login Policy and Password Policy for your SQL instance logins, at least Enforce Password Policy must be activated (and for password expiration control, the Enforce Password Expiration option must be enabled for SQL Logins). Otherwise, configuring Group Policy alone will have no effect. It’s worth noting that after enabling Login Policy and Password Policy for existing logins, if the passwords for those logins are simple and do not comply with Password Policy rules, there will be no issues for logins and applications (in other words, the policy control will not jeopardize the current state). However, at the first password change, the user will be forced to comply with the Password Policy. Activating the Password Expiration feature can immediately pose a risk to users because the password change period is not calculated from the time of activation but rather retroactively, which could immediately affect the user’s status. Therefore, before enabling Password Expiration, make sure to check which users might be at risk.
To check if a login has been locked or expired, you can use the following query:
SELECT [myLogins].[name], [myLogins].[type_desc], [myLogins].[is_disabled], [mySqlLoginsOnly].[is_policy_checked], [mySqlLoginsOnly].[is_expiration_checked], LOGINPROPERTY([myLogins].[name],'IsLocked') AS [IsLocked], LOGINPROPERTY([myLogins].[name],'LockoutTime') AS [LockoutTime], LOGINPROPERTY([myLogins].[name],'BadPasswordCount') AS [BadPassCount], LOGINPROPERTY([myLogins].[name],'BadPasswordTime') AS [BadPasswordTime], LOGINPROPERTY([myLogins].[name],'PasswordLastSetTime') AS [PasswordLastSetTime], LOGINPROPERTY([myLogins].[name],'DaysUntilExpiration') AS [DaysUntilExpiration], LOGINPROPERTY([myLogins].[name],'IsExpired') AS [IsExpired], LOGINPROPERTY([myLogins].[name],'PasswordHashAlgorithm') AS [PasswordHashAlgorithm], LOGINPROPERTY([myLogins].[name],'IsMustChange') AS [IsMustChange], LOGINPROPERTY([myLogins].[name],'HistoryLength') AS [HistoryLength], CASE WHEN [myLogins].[type]='S' THEN 'ALTER LOGIN ['+ [myLogins].[name] +'] WITH DEFAULT_DATABASE=['+ [myLogins].[default_database_name] +'], DEFAULT_LANGUAGE=['+ [myLogins].[default_language_name] +'], CHECK_EXPIRATION=ON, CHECK_POLICY=ON' END AS Command FROM master.sys.server_principals AS myLogins LEFT OUTER JOIN master.sys.sql_logins AS mySqlLoginsOnly ON [myLogins].[sid]=[mySqlLoginsOnly].[sid]
To unlock a login before its automatic unlock time, you can either change the password of that login or temporarily disable the Enforce Password Policy for the login and then re-enable it to remove the lockout status.
To change the password, you can use the following command:
EXEC sp_password NULL, 'NewPassword', 'sa'
Database administration is an exciting job combining knowledge, art and experience; you must live with it to love it, despite all the difficulties.
Currently I'm working as a Microsoft SQL Server DBA and BI consultant.
I make SQL Server faster and more reliable. Database administration isn’t about passing a certified exam, or about pointing and clicking your way through a crisis. Database administration is about applying the right solution at the right time, avoiding risk, and making robust choices that get you home each night in time for dinner with your family .
My areas of specialty are:
• SQL Server 2019-2008 performance tuning.
• Designing high availability and disaster recovery solutions.
• Securing SQL Server
• SSIS , SSAS
• Designing Data warehouse
SQL Server Database Administrator Consultant