or
Service Accounts for Something

Changing SQL Service Credentials and Restarting with T-SQL

It could be that one is speaking hypothetically, as DBAs so often do, and do so well; but it may be that one is responding to a true-to-life emergency change in a Daliesque paranoiac frenzy, but there could easily come a time when you will need to change those service credentials under which your SQL Server services normally run so happily, day in and day out.

The reasons for changing service credentials are obvious, but must be stated in bullet form for therapeutic reasons so that other DBAs, who may find themselves in the same situation as I am, can plead anonymity. I will take the heat for you:

• Some DBAs often hop from one company to another.
• There are times when DBAs know the password for the service credentials that run SQL Services
• There are myriad services on a plentitude of servers that all use the same account.
• Sometimes DBAs actually login to servers via RDP with this service password
• Active Directory administrators do not always set a specific group policy to prevent account lockouts for the service accounts for SQL Services

Remember those surveys that you fill out when you want to download new/free/trial software that ask “How many SQL Servers do you have in your organization?” If you answer 1-5, you are ok because you have time on your side. You can change the service accounts when a DBA leaves, assuming that with 5 servers you even have a position of “DBA”. In this case, you ARE the DBA and network admin and mail administrator and…However, if you answered 100-500 on the survey it may be time to consider the automation of your management of service accounts, using some simple T-SQL code and some common command line tools.

Before I dive headlong into long strings of code that makes the task of changing service credentials for SQL Services semi-automated, I should post a disclaimer:

My partiality to SSIS and aversion to linked servers would be the only reason for the recommendation. For those interested, I have written several articles on using variables and expressions with SSIS to dynamically control connection strings. But that is a topic for the future.

I use four commands (five, if you count SQLCMD which I don’t) to programmatically control (via T-SQL) the reporting, modification and restarting of SQL Services. I know there are many other ways, using SMO or DMO; but I have not made the leap to being a developer and up until recently pronounced C# as C “Pound”. ‘Recently’ was about 4 years ago -I am not that naive, but I’m sure that you get my point.

The four commands are:

• Xp_CmdShell (SQL)
• WMIC (Windows)
• SC.EXE (Windows)
• Net Stop and Net Start

The T-SQL code that incorporates each command is straightforward. In an effort to adhere to Simple-Talk’s motto – SIMPLE – I should outline the logic before giving a full listing. The goals for the code are:

1. Display a report of all service account credentials for any SQL-related service. This could include MSSQLServerADHelper or SQLBrowser as well as the database engine itself (MSSQLSERVER) and the SQL Server Agent (SQLSERVERAGENT).
2. Display only the primary SQL service credentials. I have included the database engine, the agent and the SQL Backup Agent, which is a Red Gate service.
3. Display only services that run under an account value that I can specify.
4. Change the account information (account name and password) for the primary services.
5. Change just password for the primary service accounts.
6. Restart the services.

This type of logic is easily coded and can appear redundant when looking at the full code with varying options. I should also note that I would generally produce a parameterized stored procedure to handle the input criteria to control the flow of the logic in the query. Since, my goal is to roll this ad-hoc query into SSIS, I chose not to use a stored procedure as this would require creating it on each server I would run the code against. For the time being I am comfortable with an ad-hoc query.

Let’s begin by looking at each piece of the T-SQL code, the first being the nine parameters that control the flow and set the credential values.

These nine parameters, documented in code, are set with values prior to executing the T-SQL code. As I’m a DBA of a large organization, my first objective was to programmatically change the service account password when necessary. I have added other features that may be of use; such as simply reporting the service credentials for all SQL services, reporting only for a specified account, or changing the service credentials (account and password or both) for the primary services. I chose to allow service credential changes only for primary services, because the triad (Engine, Agent and Red Gate) are the ones that I know run under specific service credentials. This may not be the case for you and your organization, so modification is expected and encouraged, especially when we get to restarting these services. As we walk through the code, the parameter values will become evident. It is enough for the time being to be aware that the only combination of parameters that will cause a restart would be @Change, @Restart and @Primary_SQL all with a value of 1. When any of these parameters are set to 0 the query will only report on or modify the service credentials. Figure 2 shows sample values.

After setting the combination of parameters, we can begin to break apart the three commands that drive the query, starting with xp_cmdshell. As most of us know, xp_cmdhsell is an extended stored procedure that allows us DBAs to run OS level commands as if we were in a CMD window. By default, xp_cmdshell is not enabled on SQL 2005 for good reason. We will have enable it as part of the query for SQL Server 2005. In SQL 2000, xp_cmdshell is not a configuration and is on by default for system administrators. The code to enable xp_cmdshell for SQL Server 2005 at the start of the query, in Listing 1.

So what are we going to use xp_cmdshell for? We are going to populate a temp table with output from the second command, WMIC. WMI, which stands for ‘Windows Management Instrumentation’, is a framework for monitoring and managing many aspects of Windows and the hardware on which it runs, such as disk subsystems, CPU, memory, and for our use, services. WMIC is a command line tool that taps into WMI. We will use WMCI to populate the table with service information specific to SQL services. The code to create the temp table is in Listing 2. The output of the WMIC command will be inserted as one big string, into the column ironically named Big_String.

The code to execute the xp_cmdshell WMIC command, looking for SQL services only, is in Listing 3. Notice that the WMIC command is piped into another command line tool (I know I said only four) called findstr. findstr is similar to ‘grep’ on UNIX system and searches for matching text patterns. In this case we are looking through the WMIC output for the string “SQL”.

Listing 3.

The output of this command is indeed one long string. With some basic text maniuplation, however, we can clean up the out put and feed it into the remainder of the code which uses the parameters to control the flow adhering to the goals of reporting on and modifying the service accounts.

I use several string functions to display the results into formatted output such as Substring(), charindex() and len(). The same code to generate the reports are repeated with IF…BEGIN…END clauses based again on the parameter values. For example if @Primary_SQL = 1 and @Acct is NULL the code only shows the three primary services mentioned previously, Engine, Agent and Red Gate SQL Backup, see Listing 4.

Notice I also use the @@Servername value to add in the server on which the code is being run. For global changes executed via linked servers or Integration Services packages, this is useful. The code follows logic to report similarly on non-primary SQL services, such as SQL Browser, and also for specific accounts. By adding a value to the @Acct value, for example, ‘.\LocalSystem’ the code only returns records for services that run under that account. Figure 3 shows the ouptut of a sample report using all services, so a @Primary_SQL value of 0 and @Acct value of NULL.

Figure 3

After the reporting iterations, comes the actual modifications and restarting of the primary SQL services if the parameter values that control modifications are set correctly. The parameters that control this modification logic are combinations of the following:

@Primary_SQL
@Change
@NEW_Acct
@PWD
@Restart
@Change_PWD_Only

Knowing that the code will only ever change the primary services, @Primary_SQL will always need to be set to a value of 1 for any modifications of the services. If not set to 1, the code will be used for reporting only. @Change is another variable that must be set to a value of 1 for any modification to be made. This is a secondary safeguard because this query has, by its very nature, some ominous potential. Time for another disclaimer? @New_Acct is used to set the new account if necessary and @PWD is the new password that goes with the new account. It is possible, as you will see to change the password only without changing the account, hence the @Change_PWD_Only variable. And finally, the almighty @Restart variable. For fear of stating the obvious, @Restart will indeed, like in the film Flatline, kill and then resucitate the primary SQL services with its new guardian angel service account.

Let’s look now at how the code works with these parameters to control the configuration and restarting of the primary services. This is where the third and fourth commands come into play, “sc.exe” and “net stop” and “net start“.

sc.exe is a command line utility that controls the services that run on a Windows system. With “sc” you can query, modify, start and stop any service on a system. The code uses “sc” to change the account or password or both. I chose to use “net stop” and “net start” instead of “sc” to change the configuration as these are more common command line service management tools that administrators are familiar with. Plus I needed them so I could use 4 commands instead of just 3. Good practice. Not “best” practice, but it keeps the knife blade skill set honed to a razor sharp edge.

Listing 5 shows a snippet of code that modifes the service credentials to change both the account and password for the primary SQL services.

Notice that this code uses the @Change_PWD_Only parameter with a value of 0. The code, therefore, expects that the @PWD parameter will contain a value, even if blank (”). (When seting an account to run as Local System, the values for @NEW_Acct and @PWD would be “.\LocalSystem” and ” respectively as LocalSystem does not require a password).

Another important part of the code at this point will be the use of the #HoldErrors temp table. This table is used to store the output of the executed T-SQL code, stored in the @TSQL variable. Later, we will interrogate the values in the #HoldErrors table to determine if the changes were successful. There is no point (and it would be ludicrous) restarting services if the configurations were not successful. #HoldErrors will let us know if the changes worked and if we should therefore restart. For output we select the top 2 records simply for reporting purposes.

Similar code drives the changing of the password only. This code checks the @Change_PWD_Only variable and if it is 1, then we execute the “sc” command without the “obj” option but pass in the new password stored in the @PWD variable.

NOTE: There are a couple of caveats when changing the account information for services. The user will have to exist, otherwise there will be an error and they have to have been given “Logon as Service” rights.

Now for the scary part….

The code that initiate the restarting of the primary services has the built-in safeguards I mentioned previously plus the error checking of the output of the #HoldErrors table. Listing 6 displays the logic of when the services will be restarted.

As you can see, the three controlling variable need to be set to 1 and if there were errors in the output of the sc command inserted into #HoldErrors, we will fall out to End_On_Failure and the next bit of code, see Listing 7, that does the dirty work of restarting will not execute.

If we assume that the changes to the service accounts were made successfully and the parameters are set to restart, the code will shell out to a batch file. By using the term “shell out” I mean I then need to spawn the batch file to another process outside of the SQL database engine proper. I do this by using the “START” command. Without using “START”, the services would not be able to come back up, due to this little thing I like to call a paradox.

I chose to create the batch file manually and place it in a location on each server. I did not do this merely because I am lazy, but because…yeah, well I am lazy. I know code could be enhanced to create the batch file or, for that matter, build a string and then execute it, but a batch file gives me one advantage: we know that SQL is going to “join the choir invisible” after we initiate the restart. In the batch I can, also add a command, which I indeed have done, to send me an email to let me know that the services have come back up. I use sqlcmd and sp_send_dbmail to send the notification that services have resumed. I also add the data via the @Query option of sp_send_dbmail by selecting @@Servername and GetDate(). Listing 8 shows the steps in the batch file, “C:\utilities\Restart_SQL.CMD”

Listing 8

NET STOP /Y MSSQLSERVER
NET STOP SQLBackupAgent
NET START MSSQLSERVER
NET START SQLSERVERAGENT
NET START SQLBackupAgent
sqlcmd -Q “msdb..sp_send_dbmail @Profile_name = ‘DB_Mail’,@recipients = ‘MyName@MyEmail.com’, @Subject = ‘Restarted’, @Query = ‘Select @@Servername + ” restarted at ”, GetDate()'”

I know now, after working on this script, that there is always room for enhancement and this may not be for everyone. You’ll want to get familiar with how this script runs with many different parameter options, maybe even commenting out the restart section and testing in a QA environment where the accounts and passwords can be changed at will and services restarted. I believe I have filled my event log with the frequent restarts on my local laptop while testing. It took a while to get over the fear of execution, so to speak, which is exactly what it would have been if there was an inadvertent run against a production system. I am now confident enough though, to roll this script into an SSIS package, the outcome of which I hope to post here soon.

The full code follows: