Best way to automate password reset of sql account

  • 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

  • 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?

  • 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):

    https://docs.microsoft.com/en-us/windows-server/security/group-managed-service-accounts/group-managed-service-accounts-overview

    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 3 (of 3 total)

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