Summary: Learn how to use Windows PowerShell to change SQL Server service accounts.

 

Hey, Scripting Guy! How can I use PowerShell to change SQL Server service accounts?

— NM

 

Hello NM, Microsoft Scripting Guy Ed Wilson here. This week I am hosting guest bloggers from the SQL side of life. This is in honor of the SQL PASS Summit 2010 that will occur next week in Seattle. Today we have Aaron Nelson.

 

 

Aaron Nelson is a Senior SQL Server Architect with over 10 years’ experience in architecture, business intelligence, development, and performance tuning of SQL Server. He has experience managing enterprise wide data needs in both transactional and data warehouse environments. Aaron holds certifications for MCITP: Business Intelligence Developer, Database Administrator, Database Developer; and MCTS: Windows Server Virtualization, Configuration (meaning Hyper-V). Aaron can be found on Twitter ( @SQLvariant  ) and on his blog.

Aaron helps run the AppDev and PowerShell Virtual Chapters of PASS, and volunteers for the local PASS Chapter AtlantaMDF. He also co-runs the Atlanta PowerShell User Group, and helps organize SQL Saturday seminars in Atlanta.

Take it away Aaron.

 

Working with SQL Server Service Accounts

After a long, hot and oppressive summer, something resembling Fall has begun to descend upon the Atlanta, GA area. However, with lots of green leaves still on trees, bright sunshine, and daily highs in the mid 70s and low 80s it is difficult to think its fall by noon.

Checking Service Accounts for SQL Server is important for many reasons. Here are a couple important things we can do with them using Windows PowerShell. 

As I did in yesterday’s Hey Scripting Guy! post, I am going to discuss several different approaches that you can use to collect your information. I’m doing this because there are lots of people who are used to using Windows PowerShell a certain way and if you are used to using cmdlets to check service account information then you may have never see how to do with the Windows PowerShell SQL Provider, Windows Management Instrumentation (WMI), or  SQL Server Management Objects (SMO) and vice-versa. 

Note: To actually change a service account, you have to start Windows PowerShell with Administrator rights. One way to do this is to right-click either the Windows PowerShell console icon or the Windows PowerShell ISE icon and select the Run as Administrator option. From a safety perspective, when you are finished making your changes, close Windows PowerShell and reopen it with your usual (non-elevated) credentials. On Windows XP, use the Run As… option as discussed in this Scripting Wife article.

 

Provider

First, let us examine how we can do this by using the Provider. Start your favorite script editor and run this.  You will see that you can retrieve the name of the service and the account the service is running under for the whole machine.

Add-PSSnapin SqlServerProviderSnapin100;
cd SQLSERVER:\SQL\WIN7NetBook\;
Get-ChildItem | Select-Object ServiceName, ServiceAccount , ServiceStartMode, ServerType

 

WMI

Similarly, with the Get-WmiObject Windows PowerShell cmdlet we can obtain all same information about the service accounts, this time through WMI, and we are not limited to just the service account for the relational engine. A benefit with using the WMI cmdlet over the SQL Provider is that we can pass a comma-separated list of servers to the -computer parameter and it will come back with the services from all the machines at the same time in one nice list. In addition, we do not have to limit ourselves to just SQL Services so that can be useful also. The WMI code is shown here.

Get-WmiObject win32_service -computer WIN7NetBook, WIN7Laptop, WIN7Desktop |
Where-Object {$_.name -match “^*SQL*”} | select SystemName, Name, StartName, PathName

 

SMO

When you work with the SMO to collect the Service Account information that you may notice that we are loading a different assembly than you will see in most Windows PowerShell scripts that works with SQL Server’s SMO. In fact, trying to determine which assembly was the correct one to use was, well, let’s just call it a ‘learning experience’ and cost me four hours of my life that I will never get back. Hence the reason I am devoting an entire blog to this subject. In any case, we load up the assembly and pass it the parameter of the server name that we want to interrogate. This approach is slow compared to all the other approaches . However, it gives you back very detailed information and is important to know for changing the Service Account, as we will see later.

Note: I love what comes up under the Type column for the SQL Full-text Filter Daemon Launcher. Yes, I know that it is the Type property but because I’m a SQL person and I specified Format-Table, it is a column.

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SqlWmiManagement”) | out-null

$SMOWmiserver = New-Object (‘Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer’) “WIN7NetBook” #pull in the server you want           

$SMOWmiserver.Services | Select-Object name, type, ServiceAccount, DisplayName, StartMode, StartupParameters | Format-Table

 

Cmdlet

If you have to retrieve a list of which SQL Services are on many machines and whether those services are running, the Get-Service Windows PowerShell cmdlet is the tool to use. This is by far the fastest way to collect that kind of data, especially from a large number of machines. Here I’ve taken use of the -ComputerName parameter one step further and used the Get-Content cmdlet to obtain a list of servers out of a text file on my machine. The only drawback to this approach is that you lose the ability to see the Service Account that you have had in the other three examples. The speed with which this cmdlet runs against many servers makes it a very useful tool to have in your arsenal; especially if you are probing servers to see whether they have SQL Server installed.

Get-Service *SQL* -ComputerName (Get-Content C:\PowerShell\AllServers.txt) ‘
| Sort-Object MachineName, DisplayName | format-table -property MachineName, Status, Name, DisplayName

 

If we are looking for something specific like a SQL Server Agent on a remote machine that is not running (and should be) you can make that easier to spot by piping the output to the Where-Object cmdlet and filtering down to just the services that are not running. Of course, in most advanced environments there are many times when you may want certain services to be stopped on some machines and running on other machines . Therefore, you will probably want to control this list from a table in a centrally located database (and a backup copy of that table on your desktop, just in case).

Get-Service *SQL* -ComputerName (Get-Content C:\PowerShell\AllServers.txt) ‘
| Where-Object {$_.Status -ne “Running”} ‘
| Sort-Object MachineName, DisplayName ‘
| format-table MachineName, Status, Name, DisplayName

 

Changing Service Accounts

Now what if, in one of those scans, we see a Service Account running under someone’s personal domain account instead of a designated Service Account and want to change that?  For Computers that are running SQL Server SQL 2005 through 2008 R2, you have to use the SQL Server Configuration Manager to change the service account. To access Configuration Manager with Windows PowerShell you can start with the same bit of code from earlier and just add to it. The script assumes that you have the permissions to enable you to change the password for the service account. If you are uncomfortable with storing your password in the script, you can use command line parameters to pass the password to the script when it starts. You could also use the Read-Host cmdlet to prompt you for the new password. The code is seen here.

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SqlWmiManagement”) | out-null

$SMOWmiserver = New-Object (‘Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer’) “WIN7NetBook” #pull in the server you want           

#Check which service you have loaded first
$ChangeService | select name, type, ServiceAccount, DisplayName, Properties, StartMode, StartupParameters | Format-Table

#Specify the “Name” (from the query above) of the one service whose Service Account you want to change.
$ChangeService=$SMOWmiserver.Services | where {$_.name -eq “MSSQLSERVER”} #Make sure this is what you want changed!

$UName=”DomainName\UserName”
$PWord=”YourPassword”           

$ChangeService.SetServiceAccount($UName, $PWord)
#Now take a look at it afterwards

$ChangeService | select name, type, ServiceAccount, DisplayName, Properties, StartMode, StartupParameters | Format-Table

 

The complete Change-SqlServiceAccount.ps1 script is seen here.

Change-SqlServiceAccount.ps1

<#

    This script will change the startup account of the specified SQL Service account on

    the machine you specify.  This script requires to be run in an elevated PowerShell session

    For Details on how to elevate your shell go to: http://bit.ly/anogNt

   

    This script can be run to just tell you about the SQL services that you have running

    on a given machine by highlighting until you get down to the first $ChangeService

   

    Make sure to change: “MyServerName”, “MSSQLSERVER”(if that’s not the one you want)

    , “DomainName\UserName”, “YourPassword”

   

    This script may not work against a SQL Server that is not running PowerShell

 

    (c) Aaron Nelson

 

Warning:  I have not tested this with SSRS yet. 

#>

 

#Load the SqlWmiManagement assembly off of the DLL

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SqlWmiManagement”) | out-null

$SMOWmiserver = New-Object (‘Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer’) “WIN7NetBook” #Suck in the server you want

 

#These just act as some queries about the SQL Services on the machine you specified.

$SMOWmiserver.Services | select name, type, ServiceAccount, DisplayName, Properties, StartMode, StartupParameters | Format-Table

#Same information just pivot the data

$SMOWmiserver.Services | select name, type, ServiceAccount, DisplayName, Properties, StartMode, StartupParameters | Format-List

 

#Specify the “Name” (from the query above) of the one service whose Service Account you want to change.

$ChangeService=$SMOWmiserver.Services | where {$_.name -eq “MSSQLSERVER”} #Make sure this is what you want changed!

#Check which service you have loaded first

$ChangeService

 

$UName=”DomainName\UserName”

$PWord=”YourPassword”

 

$ChangeService.SetServiceAccount($UName, $PWord)

#Now take a look at it afterwards

$ChangeService

 

#To soo what else you could do to that service run this:  $ChangeService | gm

 

Conclusion

So there we have it, at least four approaches with Windows PowerShell to collect Service account information for SQL Server. The choice of the best methodology depends on the information being sought. If you just need a quick list of SQL Server services that are installed on a list of machines and whether they are running, the Windows PowerShell cmdlet Get-Service is your friend. If you have to have something more like the service account that the service is running under the SQL Provider, WMI, or SMO can all be used. Be aware that only the Get-WmiObject cmdlet can quickly and easily check multiple machines for you. Nevertheless, as with everything in technology YMMV (your mileage may vary).

 

NM, that is all there is to using Windows PowerShell to retrieve information about the status of SQL Server’s services. SQL guest blogger week will continue tomorrow. Thanks Aaron for today’s blog.

I invite you to follow me on Twitter or Facebook. If you have any questions, send email to me at scripter@microsoft.com or post them on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

 

Ed Wilson, Microsoft Scripting Guy