BERJAYA

Today I’d like to share a quick script that is useful when working with a new domain of SQL servers. It’s also good for anyone who doesn’t know their environment very well or suspects there might be some hidden or rarely used servers. There are probably many ways to do this scattered around the internet, but this method is easy and as long as you have a properly designed Active Directory, you’ll be good to go.

The script isn’t anything special, but it will require the Active Directory PowerShell module. You’ll need to run the script on an existing terminal server or at least be able to successfully import the module. Importing a module is very straightforward and this one should already be available on a terminal server.

Import-Module ActiveDirectory

Once you have the module loaded, you can run the following script.

Get-ADComputer -Filter { Description -LIKE "*sql*" } -Properties Description | SELECT Name, Description

Let’s break this down since this is a short post. First of all, we are running the Get-ADComputer cmdlet. That’s going to search through your Active Directory for a list of computer names. The -Filter parameter is looking for any Description that contains the word SQL. If you want to instead search for a name prefix, you can change the filter to { Name -LIKE "Prefix*" }. The -Properties Description parameter will populate the description of the computers from AD. Without that piece, your descriptions will be blank even though we are specifically selecting them. Finally, we pipe the results to a SELECT since I’m only interested in the name of the computers and their descriptions.

This script relies on your Active Directory having useful descriptions. If your coworkers have been lazy and left those blank or put in less than useful information…it won’t be very effective.

I have a lot of servers to manage, and any downtime is bad for almost all of them. I have to regularly change service account passwords (and you should be doing that too), but with over a hundred servers and hundreds of services, manually updating was not an answer.

So I took to PowerShell and got scripting. What I created were two functions to reset passwords, first in Active Directory (you do use AD service accounts, right?) then on the individual SQL servers.

The first script is best ran on a terminal server. You need the AD PowerShell module to detect accounts and perform the password reset cmdlet. Luckily, my script has simplified everything to a cut and dried science. I’m not promising that this will work perfectly on your domain without any tweaks, but the good news is, it asks before it does anything in AD. Test it in PreProd, and you should be fine with minimal, if any, changes. Same thing goes for the second script. Test it someplace safe first.

AD Script Explanation: You need to supply two mandatory parameters, $ComputerName and $Password, while $Service is optional. The $ComputerName should be the SQL server(s) you are updating service accounts on. Active Directory commands will try to detect the service accounts you’ve used on those servers. This process has always worked flawlessly for me, but if you have duplicate service names in other Organizational Units, it could get ugly.

Next, supply the new password. The script will reset all the services to the same password, or you can supply a single service at a time and reset the password. So depending on how you supply the parameters and handle passwords, you can make mass changes or specialized changes. The script will then loop through all the computers and all the SQL services asking to reset each detected service’s password. Did AD pull a weird account? The account that SQL is running under is questionable? Hit anything but [y] and the script will skip the service and you can check it manually. Fixing 95% of your accounts remotely and manually touching the rest is a lot better than doing them all by hand on the local system. You can also supply a value for the $Service parameter if you just need to reset a single service. Otherwise it will automatically detect any default or named instance service accounts.

You will be prompted to confirm the password reset for each account on each server, for safety.

Service Account Script Explanation: You only have one mandatory parameter here. Nice huh? You have to supply the new $Password and you have to supply it as a secure string (it was the first script, give me a break) which means it needs to be typed in at runtime. I just haven’t changed this yet, sorry. The script will default to updating the services on the computer you are currently on, or you can supply the computer(s) to update. The $Service parameter is identical to the parameter from the AD script. There is also a $UserName parameter, which allows you to change the user name a service runs under. If you use this parameter, you MUST supply a single $ComputerName and $Service, otherwise you will wind up changing everything to that one account. I hope that is not your intention. The $Restart switch forces SQL services to restart then wait for a response. Obviously only use this switch if you are serious and can take the immediate downtime. It defaults off.

Password is supplied as a secure string at execution this time.

Lots of text, but this tests for existence of accounts, then reports success or failure

This script is less tentative than the AD script. It will just reset any password it finds. It will still report back the changes though, so you know what happened. I’ve had some issues where the SqlAgent restarts very slowly and reports that it is stopped, but you can verify via Get-Service or check the box manually and 10/10 times it was already started by the time I checked on it.

Disclaimer: This is a bit buggy on windows clusters. It’s best used for stand alone instances. Nothing free is perfect, right?

Script #1: Set-ADsql : Resets password in Active Directory for all SQL service accounts used on target server. Will reset all accounts to same password, or supply individual accounts to provide specific passwords.

Function Set-ADsql
{
    [Cmdletbinding()]
    Param(
    [Parameter(ValueFromPipeline=$True,Mandatory=$True)]
        [String[]]$ComputerName,
    [Parameter(ValueFromPipeline=$True)]
        [String[]]$Service=("MsSqlServer","MsSql$%","SqlServerAgent","SqlAgent%","SQLBrowser%","MsSqlFdLauncher%","MsDtsServer%","ReportServer%","SqlWriter"),
    [Parameter(ValueFromPipeline=$True,Mandatory=$True)]
        [String]$Password
)
Process {
$Host.PrivateData.VerboseForegroundColor = 'Green'
    ForEach($Computer In $ComputerName) #Loop through all computer(s)
    {
        ForEach($Account In $Service) #Loop through all services on each computer
        {
            #Get actual service name for Instanced or Default instance Servers
            $svcD=GWMI Win32_Service -ComputerName $Computer -Filter "Name LIKE '$Account'"
            If(!$svcD)
                { Write-Verbose "$Computer - $Account -> Does Not Exist -> Skipping" }
            Else
            {   #If Service Account exists, parse the name to find it in AD - if you have duplicates this may not work right!
                $Account=(($svcD.StartName).Split("@")[0]).Replace("$((Get-AdDomain).NetBiosName)\",'')
                $Ans=Read-Host "Reset $Account in AD? [y] or [n]" #Ask for confirmation to reset the password
           
                If($Ans -eq "y")
                {   #If reset approved, updates AD with the new password
                    Set-AdAccountPassword $Account -Reset -NewPassword (ConvertTo-SecureString -AsPlainText $Password -Force)
                    Write-Verbose "$Account reset!"
                }
                Else { Write-Host "Skipped reset of $Account" -ForegroundColor DarkYellow }
            }
        }#EndForEachAccount
    }#EndForEachComputer
}#EndProcess
}#EndFunction

Example Execution:

Set-ADsql -Verbose -ComputerName "Comp1" -Password "NewPass"

Script #2: Set-ServicePassword : Updates SQL service passwords on targeted computer, restarting services if instructed. Supply individual services to provide unique passwords.

Function Set-ServicePassword
{
    [Cmdletbinding()]
    Param(
    [Parameter(ValueFromPipeline=$True)]
        [String[]]$ComputerName=$Env:ComputerName,
    [Parameter(ValueFromPipeline=$True)]
        [String[]]$Service=("MsSqlServer","MsSql$%","SqlServerAgent","SqlAgent%","SQLBrowser%","MsSqlFdLauncher%","MsDtsServer%","ReportServer%","SqlWriter"),
    [Parameter(ValueFromPipeline=$True)]
        [String]$UserName='',
    [Parameter(ValueFromPipeline=$True,Mandatory=$True)]
        [SecureString]$Password,
    [Parameter(ValueFromPipeline=$True)]
        [Switch]$Restart
    )
    Process {
        $Host.PrivateData.VerboseForegroundColor = 'Green'
        $Pwd = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto([System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($Password))
        ForEach($Computer In $ComputerName) #Loop through all computer(s)
        {
            ForEach($Account In $Service) #Loop through all services on each computer
            {
                #Get actual service name for Instanced or Default instance Servers
                $svcD=GWMI win32_service -ComputerName $Computer -Filter "Name LIKE '$Account'"
                If(!$svcD)
                    { Write-Verbose "$Computer - $Account -> Does Not Exist -> Skipping" }
                Else
                {
                If($UserName -eq '') #If new User not supplied; keep the existing user
                    { $User=$svcD.StartName }
                $ChangeStatus = $svcD.Change($null,$null,$null,$null,$null,$null,$User,$Pwd) #Update User/Password
                If ($ChangeStatus.ReturnValue -eq "0")  #Error Handling
                    { Write-Verbose "$Computer - $Account -> Sucessfully Changed Password"
                         Write-Host "Remember to change AD Password: $User" -ForegroundColor DarkYellow #Nice reminder, could be Verbose, but I like it.
                        If ( $Restart )
                        {   #If Restart switch used; restart the services and wait for them to come back up
                            Write-Verbose "Restarting $($svcD.Name)"
                            Get-Service $svcD.Name -ComputerName $Computer | Restart-Service -Force
                            $Status = (Get-Service $svcD.Name -ComputerName $Computer | Select Status).Status
                            Write-Verbose "$($svcD.Name) $Status"
                            IF ( $Status -ne "running") {Write-Warning "$($svcD.Name) Failed to Start! Status: $Status"}
                        }
                    }
                Else { Write-Error "$Computer - $Account -> Failed to Change Password" }
            }
            }#EndForEach Service
        }#EndForEach Computer
    }#EndProcess
}#EndFunction

Example Execution:

#Example 1: Single Server; All Accounts updated; No Restart of Services
Set-ServicePassword -Verbose -ComputerName "Comp1"

#Example 2: Multiple Servers; All Accounts updated; Restart Services
Set-ServicePassword -Verbose -ComputerName "Comp1","Comp2" -Restart

SQL Sanctum

A Join on SQL And Everything Else

Tag Archives: Active Directory

PowerShell: Get SQL Servers From AD

PowerShell: What Computer is that IP?

Reset SQL Service Passwords using PowerShell