다음을 통해 공유

How to change the SQL Server password programmatically

  • 아티클

Every now and the question comes up how to programmatically modify the service account or password of SQL Server. There basically two ways to accomplish this, using SMO, or WMI. This article shows you how to use WMI and VBScript to accomplish this task.

 

There are a couple of things you need to know.

  • SQL Server does not need a reboot when the password is modified. We (SQL Server Team) spent quite a bit of time ensuring you do not suffer any unnecessary downtime when performing an operation that has to happen regularly, when password are expiring. Everyone likes a ‘no reboot’ clause, right?
  • When changing the account and password, the service does need a reboot, but the WMI Provider does this for you. In fact, if the service is not started it will be brought up for a short moment. If it already running it will be restarted. The reasons are as follows:
    • The service master key needs to be re-encrypted
    • Various other security settings are done
  • Because the WMI Provider performs various operations besides changing the service account, other account change methods like ‘sc’ or the standard service control panel are not supported. You may see that the service will not start in some cases.
  • Changing the password only tells the service that the password has changed. If you want to change the account’s password, use a command like NET USER. You need to run that first.

 

The following scripts allow you to change the service account + password, or only the password:

 

setaccount.vbs:

‘ Set the account and password

set svr = GetObject("WINMGMTS:\\.\root\Microsoft\SqlServer\ComputerManagement:SqlService.ServiceName='MSSQL$YUKON',SQLServiceType=1")

svr.SetServiceAccount ".\TestUser", "NewPassword!!"

 

setpwd.vbs:

‘ Set the password

set svr = GetObject("WINMGMTS:\\.\root\Microsoft\SqlServer\ComputerManagement:SqlService.ServiceName='MSSQL$YUKON',SQLServiceType=1")

svr.SetServiceAccountPassword "", "NewPassword2!!"

 

You need to change 'MSSQL$YUKON' to match your instance name (replace ‘YUKON’ with the instance name, or the entire string with MSSQLSERVER for the default instance). Of course the account and password need to be changed as well.

 

Try this on a test server first before running this on a production server.

 

I hope this is helpful,

 

Michiel

Comments

  • Anonymous
    January 11, 2007
    PingBack from http://joesack.com/WordPress/?p=38

  • Anonymous
    March 05, 2009
    There are quite a few blog posts out there to manage SQL Server 2005 services using the :.rootMicrosoftSqlServerComputerManagement