Freigeben über

HOW TO MANAGE SQL SERVER 2000 Services USING WMI

  • Artikel

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.

 

Reference
======

Sample script to change SQL Server 2005 service startup account and password using WMI:
https://blogs.msdn.com/mwories/archive/2006/11/03/wmi_5F00_change_5F00_password.aspx

MSDN Documentation on Win32_Service class
https://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

The script above changes the Startup Account of an instance called SQL2000. For changing the startup account of a default instance, we need to make the following change:

Win32_Service.Name = MSSQLServer
For a named instance, you would change it to:

Win32_Service.Name = MSSQL$<instance name>
Where <instance name> = Name of the SQL 2000 named instance

The other parameters of interest are:

objInParam.Properties_.Item("StartName")
The above parameter holds the new Startup Account

objInParam.Properties_.Item("StartPassword")
The above parameter holds the password.

If you had to change it to Local System or Network Service, then you don’t need to provide a value for StartPassword parameter.

NOTE: This should be done when there is no other option. The best way to change the startup account in SQL Server 2000 is to use Enterprise Manager.

Amit Banerjee
Technical Lead, Microsoft Sql Server

Comments

  • Anonymous
    August 11, 2009
    This script is for one instance @ a time . and if there is only one instance or a few we can do it manually .In reality there are many servers .Is there a way to change the passwords for a set of SQL Servers in a domain !! Regards Abhay

  • Anonymous
    August 11, 2009
    SQL Server 2000 doesn't have WMI provider installed by default. The two methods to do this are:

  1. UDP Broadcast which is similar to osql -L
  2. Using SMO & WMI Option 1 has the following downsides:
  • Doesn't work when there's no network connection
  • Depends on what kind of firewall rules are set
  • Doesn't find SQL Servers if SQL Browser is off
  • Doesn't find SQL Servers if they are hidden
  • List contents not guaranteed to be repeatable because the servers need to respond within a timeout period Option 2 has the following downsides:
  • Only lists SQL Server 2005 and later
  • Only finds registered servers If you want to use this for SQL Server 2000, then you would have to keep an array of SQL Server instances installed on your network. This array can be iterated through in VB in another script which calls this script. This script can be modified to take an input parameter which is the service name or instance name. For examples on how VBScript can take in parameters, refer the attached script on http://blogs.msdn.com/sqlserverfaq/archive/2009/08/11/how-to-change-the-ip-all-tcp-port-to-a-static-port-from-a-dynamic-port-using-wmi.aspx

  • Anonymous
    March 08, 2010
    I need to see the 2000 namespace in wmimgmt.msc. I did run E:SQL 2000Enterprisex86otherwmiSQLWMI80.msi . But I cannot see the SQL Server 2000 in the list of namespaces ... Regards Abhay

  • Anonymous
    March 16, 2010
    Hi Abhay, This is not directly possible. You would have to use DMO along with WMI to achieve that. The WMI Admin Provider documentation for SQL Server has that information. Thanks.