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 