Need to change sqlserver probe authentication password through nexec scripts

[Akash Saini]

Hi Folks,

We tried to change multiple server  sqlprobe probe authentication password authentication through the nexec probe. And we tried through pu.exe but there is some issue - failed to run command.

Please help on this issue

Akash Saini
9910641662

[Akash Saini]

Hi Folks,

Any one help of this point. Thanks in advance !

[Andrew Cooper]

why no run the robot as a windows service account (or even better msa, which means that windows hands password complexirty and changes) and use windows authentication in sqlserver.  The probe supports using windows authentication as the robot account (and so no password needs to be entered for the probe connection, thus you do not have to change the password.

------------------------------
Knows a little about UIM/DXim, AE, Automic
------------------------------

[Akash Saini]

Thanks Andrew,

Without pass have login issue.  I attached the screen shot.

[Keith Kruepke]

What's the issue? The screenshot says "Connection OK".

[Akash Saini]

Hi Keith,

Connection always ok when using service account and passwords .But client requested change the sql authenidciation password around 600 devices, But is manually effort take long time.
So try to find any scripts and other option.

Note:- we also try through to pu.exe but not success.

Thanks in advance !

[Garin Walsh]

There are a lot of details necessary still to answer your question:

If the password is the same for all 600 systems, any you know the connection name and know the value of the encrypted password then you can use the probe_config_set controller callback to set the value. Then restart the sqlserver probe with a _reset call to the probe itself.

Otherwise the issue you will face is that you need to get an encrypted value of the password to insert into the cfg file and that encryption process isn't public knowledge. There was a tool on this site (the probe of things by bryan morrow) that provided encryption and decryption for passwords that might still work for this.

Otherwise there's the possibility of creating an archive package with the encrypted password in it and distributing that to these systems but that also depends on knowing the encrypted value and all the systems being the same.

[Akash Saini]

Thanks Garin for valuable information.

Could you please share any archive format. Because sql profile name is different on all 600 server.

Akash Saini

[Keith Kruepke]

The profile name doesn't matter, but the connection name does. Assuming that is also different on the servers, you won't be able to use a package to push out the changes. Then you'd have to go with the other suggestion of the probe_config_set callback on the controller, combined with the probe_config_get callback in order to identify the existing connection name(s). I've used those callbacks many times for varous purposes, and they work great. But you have to script it all to work exactly as you need.

[Garin Walsh]

Any time you do 600 unique things, it's going to take 600 efforts to change them in the future. Planning ahead for this need would have saved you this effort at this point.

Your stumbling block is going to be the password itself. Is it the same for all 600 locations, same for some large number of those sites (like 6 unique passwords across all 600 sites) or is it unique per site? If it's unique per site, there's no "official" way to generate the encrypted value except via the GUI unless there's something new in the REST interface for this.

Also consider that if your client has 600 unique SQL Server installs they're probably also not going to be OK with you putting passwords in a file.

On the other hand, you should be able to do this change manually at maybe one a minute so it's only 600 minutes to do it which is only 10 hours of effort. You're probably half way through that time in trying to figure out how to do it in an automated fashion.

[Akash Saini]

Hi Keith, 

Thanks for the information .We try but no luck. Could you please share any docs and process. Thanks in advance !

[Christopher Pearson]

For consistent password the following may help:

https://knowledge.broadcom.com/external/article/140764/uim-sql-db-connection-profiles-change-a.html

[Daniel Blanco]

@Akash Saini this all depends on how you have this setup.
Situation 1:
Is there a few machines running the sqlserver probe that have say 50 SQL Server connection profiles on each to check all 600 SQL boxes? If so then use the method suggested by Chris Pearson: https://knowledge.broadcom.com/external/article/140764/uim-sql-db-connection-profiles-change-a.html
That way the PW can be updated on the few boxes and replaced easily with cfg update manually. 

Situation 2:
If each SQL box has a robot installed and has its own instance of the sqlserver probe monitoring itself locally, then set them up to have the Robot service on the sql boxes running as the AD SQL service account that has the access to monitor/access those SQL DB's.
Then when you setup the connection you specify for the Authentication: "Robot Service Authentication"​ like this:

When the PW needs to be changed on the svc account then AD controls the PW change but you then need to update the Nimsoft Robot service Run As user PW on box. To do this there are dozen of PowerShell scripts out there than can remotely change a service run as user PW and restart the service. 

This will require the robot service on all the SQL boxes to be changed to Run As: the AD SQL svc service account. This would be a 1x PITA setup and change but then in future when the PW does change you just run the PS script to change the PW on the service on each box and restart service. Don't need to mess with probe settings.

Situation 3:
If each SQL box has their own robot / sqlserver probe with the same connection & profile name with the same svc account U/P then update one box, save the cfg in the archive then distribute this package to all the SQL Server instances to update the probes cfg. They all need the same Connection Profile so the settings are applied on each box in same profile settings so it takes.



------------------------------
Daniel Blanco
Enterprise Tools Team Architect
[e-mail]
------------------------------

[Akash Saini]

Thanks Daniel

[Luc Christiaens]

The sql_response probe has a callback: get_encrypt_password that can give you the encrypted value for a password

[Akash Saini]

Hi Luc,
Could you please share any docs and process how to use callback function.

[Luc Christiaens]

If you go in IM to a sql_response probe and use ctrl+p you will see the GUI version of PU.
(meaning; all what you can via the Probe Utility GUI, you can also do via the command line PU)
Example to get the encrypted password value:
pu -u administrator -p xxx /domain/hub/robot/sql_response get_encrypt_password "yyy"

[Garin Walsh]

As a note, get_encrypt_password is not in v1.66 of the probe.

It is in 1.80-HF1. Not sure about the intervening versions.

Nice find - this was causing me some grief not being able to do this in years past.

[Akash Saini]

Thanks Daniel,

We try through the sqlserver.cfg file but unfortunately no luck.
Could you please share any sql.cfg file package format .Thanks in advance

[Luc Christiaens]

This is a sample PU command to change the encrypted password:
pu.exe -u administrator -p xx /_domain/_hub/_robot/controller probe_config_set sql_response connections/bgbulab47_master "password" "R9eZYoihUPSaGH7VK3pcBA==" "" ""
(bgbulab47_master is the connection profile name)