Having the password expiration set to ON means that SQL Server checks for all logins that use SQL Server Authentication and contained database users with a password, if their password has expired, and if so, it prompts these logins and users to enter a new password.
Password expiration is part of SQL Server’s Password Policy. SQL Server’s Password Policy can use Windows password policy mechanisms. Based on that, SQL Server can apply the same complexity and expiration policies just like in the case of Windows logins. SQL Server’s security policy can be based either in Windows, on the database server (local security policy), or on the domain.
In the below example, I’m using the Local Security Policy MMC snap-in (secpol.msc) on my database server, in order to check the password policy settings.
As you can see from the above screenshot, the maximum password age is set to 90 days. This means that if I have a login that uses SQL Server Authentication or a contained database user with a password, if the login’s or user’s password was last changed 90 days (or more) ago, then the login/user will be prompted by SQL Server to change her password.
Even though this practice was used for many years, not only in SQL Server but similarly, on Windows-level, as well as in other systems and applications, recent studies argue that it should not be a recommended practice anymore. On the contrary, these studies suggest that user passwords should not be regularly changed but rather change only when there is a specific and justified reason to do so. Furthermore, new security standards are being formulated that contain new recommendations on password change.
The concept behind the need for new recommendations on password change, is that the whole process must be more user-friendly because as it is today, prompting the user to change her password every X days with no apparent reason, it only causes frustration to the user and has as an effect the user to set similar passwords on every password change or, set passwords that are easy to guess and this is the actual risk.
So, how can you apply this concept in action? How can you ensure to the maximum possible level that you have a healthy and robust password management policy in place? This can be achieved with a combination of actions and policies.
Examples of such practices could be the below:
- Do not allow the user to set passwords that are easy to guess (you can use password dictionaries with easy-to-guess passwords for that).
- Ensure that the minimum password length is set to 8 characters but do not limit the maximum length to a small number.
- If applicable, present the user’s in an easily readable way, when her last login took place so in order if she sees a strange login date/time to contact the administrator.
- Monitor login auditing for “abnormal” login failures (i.e. consecutive login failures) and in this case prompt the user to change her password.
What are your views on the subject? Feel free to comment!
Recommended eBooks on SQL Server: