We all know that we should be changing the passwords for our service accounts every once and a while (ever 90-180 days or so) just in case someone gets ahold of the password who shouldn’t have it. However when it comes to SQL Server you have to be a little careful about changing those passwords depending on how you’ve setup your password schemes.
If for example you’ve got one domain account which runs all your SQL Servers, then changing that password gets pretty scary pretty fast. If you aren’t able to take an outage on all the servers at once then you’ll end up with servers which are running using the old domain account and anyone who tries to connect to the server will get a lovely error message about the SSPI Context not being valid.
Lets take a real live scenario that one of my clients ran into.
On Tuesday their let an IT worker go (a non-voluntary termination). On Friday they are notified that the password for the domain account for the SQL Server’s has changed. Panic mode hits, and all the service account passwords are changed and everyone with a domain admin account changes their passwords. Saturday hits and all the schedules jobs on a bunch of servers start to fail. Monday comes and no one is able to log into 95% of the SQL Servers.
The problem, when the password was changed it wasn’t changed on all the servers, so the machines which hadn’t been changed still had the old password stored and they couldn’t access the domain to authenticate users. Until all the servers could be taken down (which was approved pretty quickly as the systems were already down) users weren’t able to connect and do the work that they needed to do.