We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x
Private

Automated/scripted way to periodically set an MS SQL password (of account used by SolarWinds NPM)

High Priority
152 Views
Last Modified: 2020-03-05
Our audit mandates that an SQL account used by SolarWinds must have
its password expired periodically (eg: every 60 days) even tho we convey
it is a service account.

a) if we forget to change the password prior to expiry, service is affected

b) if we try to set it to non-interactive, will get the error in the attached

In UNIX nagios, I have a tool "changepass" that could change the password
of the nagios interactive account periodically which I could place in crontab
to set the password to an encrypted password ie if this password is seen
by an unauthorized party, he still need to decrypt it.

Thus, I plan to set this MS SQL account's password to expire every 60 days
& then set a script in task scheduler (or some sort of automated periodic
job in MS SQL/Windows) to do something like:
   net user /domain  SolarWindsOrionDatabaseUser  F1xedP@ssw0rd
(above command is for Windows, so I'll need equivalent for MS SQL).

Certainly using the scripted/automated way of changing the password
(including re-using back the password ie bypassing the password
  history should not result in the password being expired: I know this
  is against password history but I would still want it this way, pls.
  When we have time/remembers, we'll go into the script to change
  the password to be set in the script)

Certainly the script has to be non-readable or the password
F1xedP@ssw0rd   is the encrypted password so that if it's leaked/
seen, no harm.
NPMacctpasswdNonInteract.pdf
Comment
Watch Question

Author

Commented:
If the script contains plain-text password, I plan to make it non-readable using
the method suggested in link below:
https://stackoverflow.com/questions/46451344/convert-batch-script-into-base64-non-readable-format?rq=1

Author

Commented:
Btw, I'm completely newbie to SolarWinds so I don't know if the
account  SolarWindsOrionDatabaseUser   is an AD account or
an MS SQL DB account (but I reckon it's the latter)

Author

Commented:
I heard that account was created during the SolarWinds NPM installation.
Head Geek
CERTIFIED EXPERT
Commented:
First, the password is encrypted (not plaintext) within the SW system.

Second, you can use an AD account to connect to the database, instead of an MS-SQL account (if that makes a difference and/or helps.)

Regardless, the process would be:
1a) update the MS-SQL account directly (using the SA account, or a script, or whatever process you normally use
1b) update the AD account that SW is using to connect to the database
2) re-run the configuration wizard on your orion server and update the password there as well.

There *might* be a way to do this via the Orion SDK (https://github.com/solarwinds/OrionSDK) but I'm not 100% sure. There is an Orion SDK community to ask on the SolarWinds forum itself.

Hope this helps.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
>> Our audit mandates that an SQL account used by SolarWinds must have its password expired periodically (eg: every 60 days) even tho we convey it is a service account.

Taking one step back, we have a new feature called as Managed Service accounts which are basically windows logins that can be used to Run Services or Scheduled tasks or other operations as listed below..
https://www.concurrency.com/blog/march-2019/managed-service-accounts
https://www.sqlshack.com/using-group-managed-service-accounts-with-sql-server/

One advantage of Managed Services Accounts is that there is no need to worry about Password expiration(automatically taken care by your DC) and then no one else can misuse this account..

If you would need this GMSA Login to connect to SQL server, then you can create it using the below command..
Note the $ sign at the last of your login name signifying it as a GMSA login instead of normal login.
CREATE LOGIN [YourDomain\YourMSA$] FROM WINDOWS

Open in new window


FYI, we have GMSA logins as Startup accounts for SQL Server services and we don't worry about it for any password expiration to meet our Audit requirements.

Author

Commented:
> GMSA logins as Startup accounts for SQL Server services and we don't worry about
> it for any password expiration to meet our Audit requirements.

So your DB account for Solarwinds expires periodically &
if so, how exactly you get the password renewed so that
it doesn't expire?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
I think you got GMSA logins wrongly..
Group MSA’s address both of these:
1. By automating the process within Active Directory for the password management. Passwords are very complex and changed automatically as often as desired (by default every 30 days). The passwords are cryptographically random and 240 bytes long. In addition, they cannot be used to interactively logon. Nor can they be locked out.
2. There is also no longer a need to restart the SQL Server service after a service account password reset, which prevents downtime, etc.
https://docs.microsoft.com/en-us/archive/blogs/markweberblog/group-managed-service-accounts-gmsa-and-sql-server-2016

In simple words, there is no need to worry about the passwords for GMSA logins at all as it is handled by AD automatically.
Kindly let me know for more details.

Author

Commented:
I recall one EE expert has suggested the link but it's only applicable
to MS SQL 2016 & newer,  correct me if I'm mistaken.

   We're on MS SQL 2012.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
Managed Services Accounts are available starting from Windows Server 2008 R2 and hence should be available for MS SQL Server 2012..
Kindly double check once..
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.