HELP! How to automate changing the SQL Service account and password?

Question

HELP! How to automate changing the SQL Service account and password?

druidia

Mr or Mrs. 500

Points: 551
More actions
April 6, 2010 at 2:16 pm

#142437

Yes, there are many WMI scripts out there to do this for one server, but what about doing this on 100+ servers at one time? Does anyone have anything that steps through an inventory list or database and changes all of the services? Something that works for both default and multi-instance servers alike? We need to change several servers at one time and are having a hard time putting something together to do it all at once and minimize an outage. Connecting to each server and doing this via the SS Configuration Manager is not a workable solution.
Mucho thanks to anyone that can help us!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

Are you using the same service account on multiple SQL Servers?

That is a bad idea for many reasons, one of which is how hard it is to change on multiple servers at the same time without causing an outage.

druidia Mr or Mrs. 500 Points: 551 More actions · Answer 2

Yes, we are for a variety of reasons. And changing both the account and password can't be done without cycling the SQL Server service, so we know that there will be one large outage. That is unavoidable. We're just trying to make the change as quick and painless for us as possible.

Douglas Elwood SSC Eights! Points: 997 More actions · Answer 3

Hi,

I needed to do the same thing quite a while back. We had servers running Windows 2000 and SQL 2000 and needed to update our service passwords on a regular basis.

The script at this URL appears to be something that could be used. You might need to fashion a script that called it with each server along with the new credentials.

http://www.jasemccarty.com/blog/2006/03/vbs-update-services-password-on.html

If more then one service uses these credentials (multiple SQL instances), this script will update all services that are impacted.

The only thing this script does not do is recycle the service after the password change.

You would need to add logic to do that, it is pretty easy:

objService.Stop and objService.Start will start and stop and the object objService.Status will reveal the current status.

Here are the Constants for the service status:

' define ADSI status constants

Const ADS_SERVICE_STOPPED = 1

Const ADS_SERVICE_START_PENDING = 2

Const ADS_SERVICE_STOP_PENDING = 3

Const ADS_SERVICE_RUNNING = 4

Const ADS_SERVICE_CONTINUE_PENDING = 5

Const ADS_SERVICE_PAUSE_PENDING = 6

Const ADS_SERVICE_PAUSED = 7

Const ADS_SERVICE_ERROR = 8

These values are courtesy of Microsoft from the page at URL:

http://support.microsoft.com/kb/247557

Wrap some logic around the stop and start service to check the status and possibly a short call to a sleep routine. (3-4 seconds should do)

Some services with dependencies can take a few moments to stop. Attempting to start them if they are not completely stopped may result in a script failure.

Good luck, scripts like this can come in quite handy. Knowing that they exist is the best part. 🙂

druidia Mr or Mrs. 500 Points: 551 More actions · Answer 4

Thanks for that, but we are trying to change accounts and passwords on SQL 2005. This has changed from the easy days of SQL 2000. Now you must change the account or password using SQL Server Configuration Manager or you may encounter serious decryption errors, linked server errors, etc because the Service Master Key gets out of whack with the service account.

I know the changes are made through SSCM with just a WMI call, but we're trying to avoid sending a separate script to every server to make the change. One large script looping through every service and instance on a list of servers would be great.

Still looking if anyone has anything...

Thanks.