May 7, 2021 at 4:14 pm
I'm looking to have a timer job every 90 days that runs a ps script that resets a sql account password to meet security requirements.
The idea is to auto generate a random strong password (i have this part working), then encrypt and store the password into a cryptex file, so other applications can use that file to connect but the password is encrypted, and then change the sql account password.
I was looking for guidance on how I might do this.
Thanks
May 7, 2021 at 4:53 pm
One thought (may not be possible) but what about using a windows account rather than a SQL account?
If that is not an option, SQLCMD can execute TSQL against a SQL instance. So if you have a powershell script that resets that every 90 days, that should be able to handle things for you, no?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 7, 2021 at 7:04 pm
If you're running in a Windows domain, and looking for a way to have auto-password management for app service accounts, then look into Managed Service Accounts, such as Group Managed Service Accounts (gMSA):
gMSAs are domain accounts that are designed to be used as service accounts that include automatic password rotation. When set up, no human knows the password. Admins set up services to use them through a set of domain permissions and then walk away. You will need to have full Kerberos working in your domain to use them.
We use these in our massive environment as the SQL Server and SQL Agent accounts; accounts are bound to services and their passwords auto-rotate every 30 days. No human or script intervention is needed. We also use gMSAs for all of the application service accounts running well over 100,000 services (multiple services per server, many datacenters full of servers) because password compliance would otherwise easily consume a large team. We do business on six continents and need to comply with different laws from all over, including laws that apply to different sectors. You can only do so much with a financial or healthcare client before you fall under the umbrella of their industry regulations. gMSAs are a handy tool in the 'keep everyone off our backs' toolbox.
Eddie Wuerch
MCM: SQL
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply