Until recently, enabling the frequent password expiration option in SQL Server was included in many security best practices. However, recent studies, revised this recommendation and support that it should not be further included in SQL Server’s security best practices. However, if this is the case, how can this recommendation be replaced with new, modern best practices that would take into consideration users’ behavior and habits?
Check the online course! (lifetime access).
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.
Part of SQL Server’s Password Policy
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.
Password Policy Settings in Windows
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.
Discussion
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.
Strengthen your SQL Server Administration Skills – Enroll to our Online Course!
Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!
Lifetime Access / Live Demos / Downloadable Resources and more!
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.
Some Examples
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.
I’m sure that more sophisticated recommendations will be published on the subject along the way, as this is not really a new discussion but rather something that has been thoroughly discussed for years. I believe that now the time is mature to introduce flexible, new best practices regarding password management, that will eventually replace or improve old recommendations and offer a better security framework on the subject.
See More – Presenting DBA Security Advisor
Check out DBA Security Advisor, a SQL Server security tool to assess your SQL Server instances against a rich set of security checks and get security best practice recommendations.
Artemakis Artemiou is a Senior SQL Server Architect, Author, a 9 Times Microsoft Data Platform MVP (2009-2018) and a Udemy Instructor. He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Moreover, Artemakis teaches on Udemy, you can check his courses here.
This site uses cookies for personalized content and the best possible experience. By continuing to browse this site, you agree to this use. Find out more.
Loading... 
