I would guess that the majority of instances I’ve had to manage in my career were those that I didn’t initially install and configure. I’ve inherited more instances than I would bother to count, and I often need to double-check what’s been done in the past. As noted in the series on new jobs from Tracy and Josephine, there are a lot of settings to check and adjust to meet your standards.
While backups are often my first priority, security is second. I usually want to know who the sysadmins are and ensure systems are patched and configured to reduce the attack surface area. There is one other security check that I think I haven’t always been overly concerned about checking: password expiration.
There was a post from Steve Stedman recently that mentioned the way to alter logins and ensure they have CHECK_EXPIRATION set ot on, which ensures that passwords expire and need to get changed. This is especially important for sysadmins. I try to ensure those accounts in that role are secured with AD, but there have been times when SQL accounts are used. Usually, I disable sa, but I’ve seen other accounts, especially those used by monitoring systems who seem to think sysadmin is required. It’s not.
I don’t know that I’ve run queries to check the value in the is_expiration_checked column is appropriately set. If it’s not, then Steve’s post above will help you change those logins. That’s a handy script to have set up and use to ensure that all logins have this set. In fact, this is one of those areas where new logins could be created by junior administrators and not set the option. Perhaps this is something you want to run on a regular basis, perhaps weekly, to ensure that if any new SQL logins are created, they are done so with the password expiration set.
Ideally, no one would ever create logins without expiration set, but sometimes things happen. I’ve seen monitoring systems set up with sysadmin privileges and passwords that never expire. A surefire way to dramatically increase the risk to your database systems. It would be better to have a known, consistent process for setting up accounts. Some companies have specific scripts, or snippets, that administrators use when tickets are filed. One customer of mine had even linked a script to a Slack command in a sysadmin channel. Only admins could use this channel, but they could use Slack to kick off scripts to create logins, add roles, and force password changes.
No matter how you choose to handle security at a process level, it is important to include monitoring and remediation for issues. Mistakes will get made, settings altered, and exceptions approved. Sometimes we can fix things, sometimes we cannot, but knowing what our environment looks like and where we have potential issues is important not only for getting the work complete but getting the approvals to make changes that ensure better security. My recommendation is that you ensure you have a way to regularly check your systems, automatically fix issues where appropriate, and report on those that need additional approvals.
Steve Jones
Voice of the DBA 
Password expiration is a tricky issue. As devs we take it very seriously and have no problem with having to change our password frequently however users are another matter. What is the goal of enforcing frequent password changes? To reduce the risk of exposure/attack. The problem is that when you push this to users they inevitably will be forced to do something we don’t want them to do; store their passwords somewhere that is digitally accessible. The users do this because they don’t like having to remember the new complex password that is changed every 30-45 days so they save the password either in some file, in Outlook or perhaps some password management system often a free app on their phone that is actually data mining the information without their knowledge. This is further reinforced by the fact that some sites have stricter password requirements so one password isn’t good for every site so the user is expected to remember multiple complex passwords for differing systems/sites.
The point here is that in our effort to try and promote better password security we are in fact forcing users to engage in bad practices with regards to passwords because they feel they have no choice but to use password management tools that may or may not be malware. I don’t believe there is a perfect solution to this problem but what I do know is that the harder we try and make make password security by requiring more complex passwords changed more often we are only encouraging more users to make use of potentially infected software to manage their passwords.
LikeLike
I’m a bit proponent of password managers, but I agree that too many of them have potential issues. I wish these were all OSS, mainly because of the security component.
I think passwords ought to be changed, but not too often. we used to do 90 days for users and 30 days for privileged users. This was based on some of the brute force timelines, but I think that was too frequent, for many of the reasons you mentioned.
I’d like to see 60-90 days for privileged users, and require 15+ characters, with something more like 180-360 days for others. Or perhaps a 2 factor verification after that time period rather than a pwd change.
We use a link with mobile for my company that sends a push to devices. You can “validate” a device for 30 days, but then need to have a way to verify logins. With that, we very much reduce password resets and changes.
LikeLike