Monday, June 8, 2009

Windows Powershell - Changing SQL services password if all sql services are running

Here We will be using powershell to change the password of sql services present on your machine. The below code is for the machine that hosts both sql 2008 and sql 2005. it is important to know the namespace and the class being used for the code to work properly. Note that, here all the sql services including sql agents are running on the service account. If any sql services are stopped and the logonas is still in the service account name, make sure that you change it to local system and the service is stopped.


$username = "Service Account"
$password = "Password"


In the above section we will set the username and the password as an object.

$sqlservices = Get-WmiObject -Namespace root\Microsoft\SqlServer\ComputerManagement10 -Class SqlService `
Where-Object {$_.StartName -eq $username}


For $sqlservices, we are getting the list of sql services objects which are running under the username.

$i = $sqlservices.Count

$i gives you the count of the sql services running on your machine. You can verify the same by going to services.msc using the RUN on the machine. Once that is confirmed, proceed further.

For ($a = 0 )
{
$sqlservices[$a].stopservice()
$sqlservices[$a].setserviceaccount($username,$password)
$sqlservices[$a].startservice()
$a++

As the sql services object are stored as a array in $sqlservices, we need to call each object present inside the array, then stop the service, reset the password and then start the service. Whatever is mentioned above goes in a loop, reseting the password one by one.

if ($a -eq $i)
{
break
}
}

Once the object present in the array finishes i.e. it maps with the number of objects present and then it comes out of the loop.

Write-Host "SQL passwords changed completely"

The above line just notifies that the sql password has been changed.

To run the script as a whole, copy the code in italics onto powershell command and execute it.

No comments:

Post a Comment