I am a believer that regular rotation of passwords is an important security measure. I worked at an organization that, when they were small, would use the SQL Server database engine service account to run SQL Agent jobs and other processes. This account was the same for all servers and was sysadmin on them all as well. When I was hired the vulnerability was already in place. Former members of the database team, as they had called it, had left the company or transferred into non-DBA roles. While I’d never advice using the service account as a sysadmin user for running all processes on the server, the real issue was that there were people who knew the password to this powerful account who should not.
Periodic password changes help keep employees honest because after they transition out of a particular role it won’t be long before any knowledge of the account passwords becomes out of date. As soon as you have more than a couple of servers it becomes important to update these passwords programmatically. When you change the password of a service account the SQL Server service will not immediately fail. Usually you will need a restart of the service for that to happen. But, the service account is used for kerberos impersonation and access to operating system resources which might begin failing immediately. So, updating the passwords on all of your servers for all of your services quickly, after changing it on your local machine or active directory, is key.
Changing the account’s password
If possible I prefer to handle an entire maintenance operation in script rather than having manual steps for certain sections and scripted ones for others. Ideally you would have modular scripts that could be referenced in an operation specific script for re-usability. So, the first part we will look at is how to change the account’s password.
We will create an ADSI object for the password change which can be accomplished with a simple cast to the object type from a string but there are three pieces of information you will need to know first. You will need to know if the account is a local account or domain account, what the computer name or domain controller name is, and the account or distinguished name. Then the object can be created like so.
[sql][adsi]$user = “WinNT://local-or-remote-PC/accountname”;[/sql]
[sql][adsi]$user = “LDAP://www.domain.com/distinguishedname”;[/sql]
You might be asking, but how do I get the distinguished name? The easiest method is to use PS remoting and use Get-ADUser from the ActiveDirectory module. I’ll go over PS remoting in another blog post, however. For the moment we are going to assume that you don’t have PS remoting enabled and we still need to search Active Directory for your user’s distringuished name. A distinguished name looks like this, “CN=derik,CN=Users,DC=hammer,DC=com”, if my user was derik@hammer.com.
Borrowed, with minor changes, from the Scripting Guys‘ post on Searching Active Directory; we will use this to find the distinguished name.
[sql]$filter = “(&(name=accountname)(objectCategory=User))”
$domain = New-Object System.DirectoryServices.DirectoryEntry
$searcher = New-Object System.DirectoryServices.DirectorySearcher
$searcher.SearchRoot = $domain
$searcher.Filter = $filter
$searcher.PropertiesToLoad.Add(“distinguishedname”)
$results = $searcher.FindAll()
foreach ($result in $results) {$item = $result.Properties; $distinguishedname = $item.distinguishedname;}
[adsi]$user = “LDAP://www.domain.com/$distinguishedname”;[/sql]
Finally, all we have to do is set the password like so.
[sql]$user.SetPassword(“n3wSTr0ngPw”);[/sql]
Updating the service with the new password
So now that we’ve updated our user account password, it’s time to update the password on all of the services. Likely you will pull in list of services and run through this part in a loop. I will just be covering the code necessary to update the services on a single server, however.
First we will retrieve the Win32_Service objects. If you are targeting a specific service you can use this script.
[sql]$services = Get-WmiObject win32_service -computer “DEV-01” | Where-Object { $_.Name -eq “MSSQL$SQLEXPRESS” }[/sql]
While the single service approach is useful, it is more likely that you will simply want to update all services that currently are running as the user that you have updated the password on.
[sql]$services = Get-WmiObject win32_service -computer “DEV-01” | Where-Object { $_.StartName -eq “domain\administrator” }[/sql]
And now we change the password and then restart the service so that the change will take affect.
[sql]foreach($service in $services)
{$service.change($null,$null,$null,$null,$null,$null,”domain\administrator”,”n3wSTr0ngPw”)
if ($service.State -eq “Running”) {Restart-Service $service.Name}}[/sql]
Full script
Local account password change
[sql]#Change user account password
[adsi]$user = “WinNT://local-or-remote-PC/accountname”;
$user.SetPassword(“n3wSTr0ngPw”);
#Update user account password for all services using that account
$services = Get-WmiObject win32_service -computer “DEV-01” | Where-Object { $_.StartName -eq “.\administrator” }
foreach($service in $services)
{$service.change($null,$null,$null,$null,$null,$null,”domain\administrator”,”n3wSTr0ngPw”)
if ($service.State -eq “Running”) {Restart-Service $service.Name}}[/sql]
Domain account password change
[sql]#Change user account password
$filter = “(&(name=accountname)(objectCategory=User))”
$domain = New-Object System.DirectoryServices.DirectoryEntry
$searcher = New-Object System.DirectoryServices.DirectorySearcher
$searcher.SearchRoot = $domain
$searcher.Filter = $filter
$searcher.PropertiesToLoad.Add(“distinguishedname”)
$results = $searcher.FindAll()
foreach ($result in $results) {$item = $result.Properties; $distinguishedname = $item.distinguishedname;}
[adsi]$user = “LDAP://www.domain.com/$distinguishedname”;
$user.SetPassword(“n3wSTr0ngPw”);
#Update user account password for all services using that account
$services = Get-WmiObject win32_service -computer “DEV-01” | Where-Object { $_.StartName -eq “domain\administrator” }
foreach($service in $services)
{$service.change($null,$null,$null,$null,$null,$null,”domain\administrator”,”n3wSTr0ngPw”)
if ($service.State -eq “Running”) {Restart-Service $service.Name}}[/sql]
Leave a Reply