Run password changes on multiple servers within central management server

Question

Run password changes on multiple servers within central management server

caz100

SSCommitted

Points: 1827
More actions
September 24, 2017 at 6:31 am

#349228

Hi
I have a large sql instance estate with all instances registered withing a central management server.
I would like to update a coupe of hundred passwords for different sql logins on different sql instances.
Apart from connecting to each instance individually and running my specific update login commands for the specified list of users on that particular instance is there any way I can make use of CMS to do this quicker.
On each instance different logins will be updated with password changes or accounts deactivated or indeed similar logins will be updated but with different passwords. So for example the sa account might be disabled on one sql instance but have the password changed on another.
thanks in Advance

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

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

cyrusbaratt Old Hand Points: 346 More actions · Answer 1

Greetings,

Well, you could run your individual scripts on each server as you stated.
The other option is to run a power shell script, which would connect to each server, and perform the password changes. I have seen this done before. I do not have the code for it. Please check this Web site.

https://social.technet.microsoft.com/wiki/contents/articles/22767.sql-server-change-the-login-password-using-powershell.aspx

Cheers

DBASupport

caz100 SSCommitted Points: 1827 More actions · Answer 2

@cyrusbaratt thanks but this wont work in my situation. There are hundreds of logins to be changed, different logins on different servers and I wouldn't have time to be entering in the parameters of passwords for those on the command line.

At the moment the quickest way to do this seems to be pre create the scripts in excel and individually run each server script (for required password changes and disabling logins) by connecting to each server 1 at a time in CMS. Note I need to keep a record of each password change also as they will be stored in a secure area.

gerald72 SSC Enthusiast Points: 191 More actions · Answer 3

hi,
what about this solution:
Query you CMS for the instance names.
Paste the result into a text-file
Make a batch file using the text-file as input for a for %%a in textfile do sqlcmd.exe -S%%a ...
and run sqlcmd with sql script containing the login changes.
you can also log the output into an extra file.

And for sure you could also use powershell to do this.

BR
Gerald

caz100 SSCommitted Points: 1827 More actions · Answer 4

Thanks @gerald72 - Anymore detail on the CMS query or powershell script? I wonder is there one out there anywhere which does similar?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Here's a quick and dirty method. Provided you know in advance the names of the servers and logins and the new passwords, you could use Excel to generate a script like this - one line for each login. You could then use CMS to connect to all servers and run the whole script.
IF @@SERVERNAME = 'SERVER1' EXEC ('ALTER LOGIN Login1 WITH PASSWORD = ''MyNewPassword''')

John