Changing SQL Server service account passwords enterprise-wide

Question

In the event of a DBA leaving an organization, what options can the surviving team members pursue to change passwords for service accounts portfolio-wide? While using Configuration Manager appears to be the de-facto method, can PowerShell or another scripting or batch language be used to reset them in bulk? I figure if you're changing the login name for the service accounts, Configuration Manager is essential as registry permissions and the like need to be propagated. For just a password change to the existing account however, is a scriptable method OK as long as it's performed during approved maintenance windows for the service restarts?

Answer 1

Option 1

I would probably take advantage of using a Central Management Server to run the password change script across all the different servers at once. This should provide an easy method for verifying the output from each server while keeping it easy to manage by executing once for all target servers.

Option 2

If I was doing this in a tighter change management system, then I might consider scripting it as a batch file and having a sqlcmd execution output a separate log file for each server. This would verify the changes and keep record of them more reliably than doing it in SQL Management Studio.

I don't think there is necessarily a "right" method here, just depends on what meets your requirements best.

Answer 2

You can see in this blog post that it is possible to do via powershell using WMI. I also saw this post on stack exchange. I'd definitely try it out on a few test servers if you can, but otherwise I don't see why you can't do it via script. You'll probably want to ensure you add logging into your script so you can validate that the service restarts or add another script to go back and check the service is running. Also ensure that you account for named instances when searching the services. As you probably know, default services are named MSSQLSERVER/SQLSERVERAGENT, while named instances have the nomenclature of MSSQL$instance/SQLAGENT$instance.