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?
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.
Learn more about SQL Server security.
Get my eBook “Administering SQL Server” today!
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.
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.
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.
Check all our other related SQL Server Security articles.
Check out our latest software releases!
Subscribe to our newsletter and stay up to date!
Check out Artemakis’s eBooks!
Other SQL Server Security-Related Articles
- How to Enable SSL Certificate-Based Encryption on a SQL Server Failover Cluster
- Why You Need to Secure Your SQL Server Instances
- [DBNETLIB] [ConnectionOpen (SECDoClientHandshake()).] SSL Security Error – How to Resolve
- Should Windows “Built-In\Administrators” Group be SQL Server SysAdmins?
- SQL Server Row Level Security by Example
- Policy-Based Management in SQL Server
- The “Public” Database Role in SQL Server
- Encrypting SQL Server Databases
- Transparent Data Encryption (TDE) in SQL Server
- Encrypting a SQL Server Database Backup
- …check all
Check our latest software releases!
Easily generate snippets with Snippets Generator!
Secure your databases using DBA Security Advisor!
Convert static T-SQL to dynamic and vice versa with Dynamic SQL Generator.
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)