WMI Script for Changing SQL Server 2000 and 2005 passwords

Our domain security policy requires us to change our Domain Account passwords every once in a while. This security feature is a good practice but the change of password requires me to change my SQL Server component startup accounts to be changed also as most of them run under my Domain Account.

Tired of accomplishing this task which requires me to type out my user name and password (twice for the reconfirmation ūüôā )¬† brought forward the need for automating this task. Once I did this, I thought it would be prudent for me to share the same with the SQL community. WMI is quite powerful and a number of tasks can be achieved through it which you shall see in my future blog posts. I have created a category in my blog completely for WMI.

Addition: October 24th, 2011

If you are using SQL Server 2008 R2, then you need to have this fix applied so that SetServiceAccount can work:

2397020¬†FIX: “Access is denied. [0x80070005]” error message occurs in SSCM when you try to change the password of an account of SQL Server 2008 R2 in Windows Vista or in a later version of Windows
http://support.microsoft.com/default.aspx?scid=kb;EN-US;2397020

If you use SetServiceAccount to change the password, then this will result in a SQL Server service restart. The script provided on the Technet Gallery is for changing both the service account and the password. If only the password needs to be changed for the service account, then use SetServiceAccountPassword. This will change the password and will not automatically restart your SQL Server service.

Download the file: pwdchange file and change the name to pwdchange.vbs. This can be executed from command prompt using the following command (provided you have Windows Script Host which almost all Windows Servers have):

cscript pwdchange.vbs

The script has comments written to help you understand the same. This script makes use of the following namespaces:

\root\Microsoft\SqlServer\ComputerManagement (SQL Server 2005)

\root\CIMV2 (SQL Server 2000)

The reason I do not use SQL Server 2000 WMI namespace as it is not installed by default with a SQL Server 2000 default installation. It has to be installed separately. So, I decided to use CIMV2 which has Win32_Service for achieving the same purpose. In the script you would have to provide the following details:

strAccount1 = “Account1” ‘ For alias@domain.com
strAccount2 = “Account2” ‘ For domain\alias naming convention
strAccount3 = “Account1” ‘ To cover any other naming convention
strAlias = “Alias” ‘ Provide the alias
strDomain = “DomainName” ‘ Provide the domain name
strPassword = “Set Password Here” ‘ Put the new password here

Using WMI to Manage SQL Server 2000 Services

There are quite a few blog posts out there to manage SQL Server 2005 services using the :\\.\root\Microsoft\SqlServer\ComputerManagement
However, WMI Admin Provider is not pre-installed for SQL Server 2000. It needs to be installed separately using the WMI Admin Provider Setup available along with the SQL Server 2000 Setup CD under x86\other\wmi folder. 

Sample script to change SQL Server 2005 service startup account and password using WMI:
http://blogs.msdn.com/mwories/archive/2006/11/03/wmi_5F00_change_5F00_password.aspx
MSDN Documentation on Win32_Service class
http://msdn.microsoft.com/en-us/library/aa394418.aspx 

Sample Script to change a SQL Server 2000 instance startup account using root\MicrosoftSQLServer namespace: 

strComputer = "." 

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\MicrosoftSQLServer") 

' Obtain an instance of the the class 

' using a key property value. 

Set objShare = objWMIService.Get("Win32_Service.Name='MSSQL$SQL2000'") 

' Obtain an InParameters object specific 

' to the method. 

Set objInParam = objShare.Methods_("Change"). _ inParameters.SpawnInstance_() 

' Add the input parameters. 

objInParam.Properties_.Item("StartName") = "LocalSystem" 

objInParam.Properties_.Item("StartPassword") = "" 

' Execute the method and obtain the return status. 

' The OutParameters object in objOutParams 

' is created by the provider. 

Set objOutParams = objWMIService.ExecMethod("Win32_Service.Name='MSSQL$SQL2000'", "Change", objInParam) 

' List OutParams 

Wscript.Echo "Out Parameters: "Wscript.echo "ReturnValue: " & objOutParams.ReturnValue

WMI Tracing equivalent of SQL Server Profiler Traces

A lot of companies find the need to monitor all DDL and DML activity on the server for Compliance reasons. Though SQL Profiler Traces provides this functionality by using Stored Procedures and Functions. These system stored procedures can be used from within your own applications to create traces manually, instead of using SQL Server Profiler. This allows you to write custom applications specific to the needs of your enterprise. However, you might choose not to use this approach due to the overhead of running profiler traces on the server. You can use server side profiler traces which tend to have a lesser performance impact on the server. 

Another method is to use WMI Events to monitor SQL Server Events. Consider the following example, let’s say you want to monitor the SQL Server events for all Update Statistics Events. Then you can use script below to create the WMI Alert and also the job to put the event details into a SQL Server database table.¬†

<script> 

USE TestDB 

GO 

-- Creating the table to store the DDL Events 

CREATE TABLE [dbo].[UPD_STATS_Events]( 

[AlertTime] [datetime] NULL, 

[SPID] [int] NULL, 

[DBName] [nvarchar](100) NULL, 

[TextData] [nvarchar](max) NULL 

) ON [PRIMARY] 

GO 

-- Adding the job to run when the WMI Alert is raised 

EXEC msdb.dbo.sp_add_job @job_name=N'Capture Update Statistics Events', 

@enabled=1, 

@description=N'Job for responding to DDL events' ; 

GO 

-- Adding the job step 

EXEC msdb.dbo.sp_add_jobstep 

@job_name = N'Capture Update Statistics Events', 

@step_name=N'Insert data into Update Statistics Events table', 

@step_id=1, 

@on_success_action=1, 

@on_fail_action=2, 

@subsystem=N'TSQL', 

@command= N'INSERT INTO UPD_STATS_Events 

(AlertTime, Spid,DBName,TextData) 

VALUES (getdate(), $(ESCAPE_NONE(WMI(SPID))), 

''$(ESCAPE_SQUOTE(WMI(DatabaseName)))'', 

''$(ESCAPE_SQUOTE(WMI(TSQLCommand)))'')', 

@database_name=N'TestDB' ; 

GO 

-- Set the job server for the job to the current instance of SQL Server. 

EXEC msdb.dbo.sp_add_jobserver @job_name = N'Capture Update Stats Events' ; 

GO 

-- For the named instance you need to use \\.\root\Microsoft\SqlServer\ServerEvents\<INSTANCE NAME> 

EXEC msdb.dbo.sp_add_alert @name=N'Respond to Update Statistics Events', 

@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER', 

@wmi_query=N'SELECT * FROM UPDATE_STATISTICS', 

@job_name='Capture Update Statistics Events' ; 

GO

</script> 

You will have to give the SQL Agent account WMI Token replacement rights. You need to create a registry key in SQLAgent for this.
Add a Reg_DWORD HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\MSSQL.x\SQLServerAgent\AlertReplaceRuntimeTokens
and set it to 1.
and no SQLAgent restarts needed. 

Where x = the number of the SQL instance. This can be found out by looking under the regisry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL 

Whenever an update statistics task is fired against a database, the time when the command was executed, the SPID number, the database name and the T-SQL Command command associated with the event would be put into the table in the TestDB database. 

Service Broker should be enabled for the MSDB and the database in which you are storing the event details. 

Information about the namespaces can be found on MSDN. Also, you could use WMI Code Creator to browse through the namespace and the classes available and their properties. 

Furthermore, you can also use the WMI Event Watcher Task or the WMI Data Reader Task of SQL Server 2005 SSIS to perform the same.