asked on

VBScript for changing SQL service account (almost works)

Hello,

I snagged a script off the web to modify the MS-SQL server service account and it works great as long as I am modifying the SQL Server service. When I try to use it for the Server Agent or Integration Services it does not work (no error is given).

In theory, it should be as simple as modifying just one number in the code. What am I missing?

Site that had script: http://blogs.msdn.com/mwories/archive/2006/11/03/wmi_5F00_change_5F00_password.aspx
Site that has service types: http://msdn.microsoft.com/en-us/library/ms179591.aspx

Thanks.

'Service types'
SQL_SERVER_SERVICE = 1
SQL_SERVER_AGENT = 2
INTEGRATION_SERVICES = 4
ANALYSIS_SERVICES = 5
REPORTING_SERVICES = 6
 
strComputer = "."
strInstance = "MSSQLSERVER"
intServiceType = ANALYSIS_SERVICES
 
set svr=GetObject("WINMGMTS:{impersonationLevel=impersonate}!\\" & strComputer _
	& "\root\Microsoft\SqlServer\ComputerManagement:SqlService.ServiceName='" _
	& strInstance & "',SqlServiceType=" &intServiceType )
svr.SetServiceAccount ".\UserName","ReallyGoodPassword"

Open in new window

ASKER CERTIFIED SOLUTION

William Elliott

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

zabu99

ASKER

Awesome, thank you.
I was looking at just the correct number for the services, but didn't think that the instance name changed slightly. Thanks weellio!

zabu99

ASKER

I finished the code to modify the service accounts of SQL servers and included it here for those that want it. This script should be saved with an .hta extension.

The actual code to change the service account is pretty easy (once I had help), the extra stuff is just to allow you to pick the SQL server, instance and error checking.

Let me know if you have any questions.
Have a great day.


<HEAD>
<TITLE>DEL SQL Service Account Modifier ver 1.0</TITLE>
<hta:application
	applicationname="SQL Account Modifier"
	scroll="yes"
	singleinstance'"yes"
	>
</HEAD>
 
<SCRIPT language="vbscript">
 
'-----------------------------------------------------------
'Set up variables and constants
'-----------------------------------------------------------
 
'Adjust the size of this array depending on the number of servers
DIM arrServerNames(2)
 
DIM arrInstanceName(50)
DIM intInstanceNumber
DIM strServer
DIM bolSQLServer
DIM bolNoErrors
DIM strResultsMsg
 
strResultsMsg = ""
 
'List all of the servers that we are interested in
'Add to this list depending on what servers you have
	arrServerNames(0) = "Local Machine"
	arrServerNames(1) = "SQLServer 1"
	arrServerNames(2) = "SQLServer 2"
 
'Service types
	SQL_SERVER_SERVICE = 1
	SQL_SERVER_AGENT = 2
	INTEGRATION_SERVICES = 4
	REPORTING_SERVICES = 6
 
'-----------------------------------------------------------
 
SUB Window_onLoad
	ListServers
	StatusArea.InnerHTML = ""
END SUB
 
'-----------------------------------------------------------
 
SUB ListServers
	
	'This sub displays the list of servers in a dropdown box
	
	strDisplay = "<select size=" & chr(34) & "1" & chr(34) & "name=" & chr(34) & "ServerChooser" & chr(34)
	strDisplay = strDisplay & " onChange=" & chr(34) & "ListInstances" & chr(34) & ">"
		
	FOR i = 0 TO UBound(arrServerNames)
		strDisplay = strDisplay & "<option value=" & i+1 & ">" & arrServerNames(i) & "</option>"
	NEXT
	strDisplay = strDisplay & "</select>"
	ServerListArea.InnerHTML = strDisplay
END SUB
 
'-----------------------------------------------------------
 
SUB ListInstances
 
	'Find out what server to look at	
	FOR Each objOption in ServerChooser.Options
		IF objOption.Selected THEN
			strServer = objOption.InnerText
		END IF
	NEXT
 
	'If the server selected is the local machine then set the server name,
	'otherwise try and ping the server to see if it is available.
	IF strServer = "Local Machine" THEN
		strServer = "."
		bolServerFound = TRUE
	ELSE
	'Ping the server
		On Error Resume Next
		Set objWMIService = GetObject("winmgmts:\\" & strServer & "\root\cimv2")
		IF Err <> 0 THEN
			bolServerFound = FALSE
			bolSQLSERVER = FALSE
			Err.clear
		ELSE
			Set colPings = objWMIService.ExecQuery("Select * From Win32_PingStatus where address = '" & strServer & "'")
			FOR Each objStatus in colPings
				IF objStatus.StatusCode = 0 THEN
					bolServerFound = TRUE
				ELSE
					bolServerFound = FALSE
				END IF
			NEXT
		END IF
	END IF
	
	'Reset error checking
	'this means that erros will crash the script, but that's a good
	'thing as we want to catch those prgramatically.
	On Error GoTo 0
 
	'If the server is available we will display its instances
	IF bolServerFound = TRUE THEN
		bolSQLServer = FALSE
		'Enumerate the instances for this server
		Const HKEY_LOCAL_MACHINE = &H80000002
		Set objRegistry = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strServer & "\root\default:StdRegProv")
    	strKeyPath = "SOFTWARE\Microsoft\Microsoft SQL Server"
    	strValueName = "InstalledInstances"
    	objRegistry.GetMultiStringValue HKEY_LOCAL_MACHINE,strKeyPath, strValueName,arrValues
    	IF IsNull(arrValues) = 0 THEN
	    	bolSQLSERVER = TRUE
		
			FOR Each strValue In arrValues
				arrInstanceName(i) = strValue
				i = i + 1
			NEXT
			intInstanceNumber = i - 1
		
			'Format display for screen
			strDisplay = "<select size=" & chr(34) & "1" & chr(34) & "name=" & chr(34) & "InstanceChooser" & chr(34) & ">"
			FOR i = 0 TO intInstanceNumber
				strDisplay = strDisplay & "<option value=" & i & ">" & arrInstanceName(i) & "</option>"
				strDisplay2 = strDisplay2 & "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" & arrInstanceName(i) & "<BR>"
			NEXT
			strDisplay = strDisplay & "</select>"
		Else
			strDisplay = "none"
			strDisplay2 = "No instances found on this server."
		END IF
	
		InstanceListArea.InnerHTML = strDisplay
		AllInstanceListArea.InnerHTML = strDisplay2
		MessageArea.InnerHTML = ""
	ELSE
		InstanceListArea.InnerHTML = ""
		AllInstanceListArea.InnerHTML = "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; none<BR>"
		MessageArea.InnerHTML = "Server not found, please choose another."
	END IF
	
END SUB
 
'-----------------------------------------------------------
 
SUB OnButtonClick
 
	'This subroutine was created becuase of the need to clear the old error
	'messages and also to display a "please wait" message
	
	MessageArea.InnerHTML = ""
	StatusArea.InnerHTML = "Processing, please wait...."
 
	'This line wait for 1/100th of a second and then goes to the next subroutine
	'For some reason it allows the hta to update the message areas
    idTimer = window.setTimeout("ChangeAccount", 10, "VBScript")
 
END SUB
 
'-----------------------------------------------------------
 
SUB ErrorChecking
 
	bolNoErrors = TRUE
	'No instance found for selected server
	IF bolSQLSERVER = FALSE THEN
		strErrorMsg = "&nbsp;&nbsp;&nbsp; No valid SQL server and instance selected.<BR>"
		bolNoErrors = FALSE
	END IF
	'Check to see if any services were selected
	IF Checkbox1.Checked THEN
		intCounter = intCounter + 1 
	END IF
	IF Checkbox2.Checked THEN
		intCounter = intCounter + 1 
	END IF
	IF Checkbox3.Checked THEN
		intCounter = intCounter + 1 
	END IF
	IF Checkbox4.Checked THEN
		intCounter = intCounter + 1 
	END IF
	IF intCounter = 0 THEN
		strErrorMsg = strErrorMsg & "&nbsp;&nbsp;&nbsp; No services selected.<BR>"
		bolNoErrors = FALSE
	END IF
	'Check to see if a password was entered
	IF UserPassword.Value = "" THEN
		strErrorMsg = strErrorMsg & "&nbsp;&nbsp;&nbsp; No password entered.<BR>"
		bolNoErrors = FALSE
	END IF
	'Check to see if the passwords match
	IF UserPassword.Value <> UserPasswordConfirm.Value THEN
		strErrorMsg = strErrorMsg & "&nbsp;&nbsp;&nbsp; Passwords do not match.<BR>"
		bolNoErrors = FALSE
	END IF
	
	IF strErrorMsg <> "" THEN
		strErrorMsg = "<FONT COLOR=red FACE=Verdana><U>ERROR</U><BR>" & strErrorMsg & "</FONT>"
	END IF
	
	MessageArea.InnerHTML = strErrorMsg
	
END SUB
 
'-----------------------------------------------------------
 
 
SUB ChangeAccount
	
	'First look to see if there will be any errors
	ErrorChecking
	IF bolNoErrors = TRUE THEN
		
		'Did you select a particular instance or all
		'intInstanceOption = 1; a particular instance
		'intInstanceOption = 2; all instances
		FOR each objButton in InstanceChoice
			IF objButton.Checked THEN
				intInstanceOption = objButton.value
			END IF
		NEXT
		'If you selected to pick an instance then let's see what you selected!
		IF intInstanceOption = 1 THEN
			FOR Each objOption in InstanceChooser.Options
				IF objOption.Selected THEN
					strInstance = objOption.InnerText
					intLowerInstance = i
					intUpperInstance = i
				ELSE
					i = i + 1
				END IF
			NEXT
			IF strInstance = "" THEN
				intInstanceOption = 2
			END IF
		END IF
		'If you selected all instances then let's open it up to all
		IF intInstanceOption = 2 THEN
			intLowerInstance = 0
			intUpperInstance = intInstanceNumber
		END IF
		
		'
		strResultsMsg = ""
		FOR i = intLowerInstance TO intUpperInstance
			IF Checkbox1.Checked THEN
				strDisplayMsg = "SQL Server service"
				IF UCase(arrInstanceName(i)) = "MSSQLSERVER" THEN
					strRealInstance = "MSSQLSERVER"
				ELSE 
					strRealInstance = "MSSQL$" & arrInstanceName(i)
				END IF
				ApplyChanges strServer, strRealInstance, SQL_SERVER_SERVICE, UserNameBox.Value, UserPassword.Value, strDisplayMsg
			END IF
			IF Checkbox2.Checked THEN
				strDisplayMsg = "SQL Server Agent"
				ApplyChanges strServer,"SQLSERVERAGENT" , SQL_SERVER_AGENT, UserNameBox.Value, UserPassword.Value, strDisplayMsg
			END IF
			IF Checkbox3.Checked THEN
				strDisplayMsg = "SQL Integration Services"
				ApplyChanges strServer, "MsDtsServer", INTEGRATION_SERVICES, UserNameBox.Value, UserPassword.Value, strDisplayMsg
			END IF
			IF Checkbox4.Checked THEN
				strDisplayMsg = "SQL Reporting Services"
				ApplyChanges strServer, "MSSQLServerOLAPService", REPORTING_SERVICES, UserNameBox.Value, UserPassword.Value, strDisplayMsg
			END IF
		NEXT
		
	MessageArea.InnerHTML = strResultsMsg 
	
	END IF
	StatusArea.InnerHTML = "DONE!"
	
END SUB
 
'-----------------------------------------------------------
 
SUB ApplyChanges(strServer, strInstance, intServiceType, strUserName, strPassword, strDisplayMsg)
	
	On Error Resume Next
	
	IF strServer = "." THEN
		strDisplayServer = "Local Machine"
	ELSE 
		strDisplayServer = strServer
	END IF
	
	'This next line can be uncommented for error checking
	'MsgBox strServer & chr(13) & strInstance & chr(13) & intServiceType & chr(13) & strUserName & chr(13) & strPassword & chr(13) & strDisplayMsg
	
	'Connect to SQL server object
	set svr=GetObject("WINMGMTS:\\" & strServer _
		& "\root\Microsoft\SqlServer\ComputerManagement:SqlService.ServiceName='" _
		& strInstance & "',SqlServiceType=" & intServiceType )
	IF Err <> 0 THEN
			strResultsMsg = strDisplayServer & ": Not able to connect to " & strInstance & " provider.<BR>"
			Err.Clear
	ELSE
		'Change username and password
		svr.SetServiceAccount strUserName, strPassword
	
		IF Err <> 0 THEN
			strResultsMsg = strResultsMsg & strDisplayServer & ": Password <I>NOT</I> changed for " & strDisplayMsg & ", instance " & strInstance & "<BR>"
			Err.clear
		ELSE
			strResultsMsg = strResultsMsg & strDisplayServer & ": Password changed for " & strDisplayMsg & ", instance " & strInstance & "<BR>"
		END IF
	END IF
	
	MessageArea.InnerHTML = strResultsMsg
	
	On Error GoTo 0
 
END SUB
 
'-----------------------------------------------------------
 
</SCRIPT>
 
<BODY STYLE="FONT:10 pt verdana; COLOR:black; filter:progid:DXImageTransform.Microsoft.Gradient(GradientType=0, StartColorStr='#660099', EndColorStr='#FFFFFF')">
<BR><BR><B>
Server Name: 	
<SPAN ID = "ServerListArea"></SPAN>
<BR><BR>
Instances:
<BR>
&nbsp;&nbsp;&nbsp; <INPUT type="radio" name="InstanceChoice" value="1" >Select an instance:
<SPAN ID = "InstanceListArea"></SPAN>
<BR>
&nbsp;&nbsp;&nbsp; <INPUT type="radio" name="InstanceChoice" value="2" checked>Choose all instances
<BR></B>
<SPAN ID = "AllInstanceListArea"></SPAN>
<BR>
<B>
Services
<BR>&nbsp;&nbsp;&nbsp;
<input type="checkbox" name="Checkbox1" checked> SQL Server
<BR>&nbsp;&nbsp;&nbsp;
<input type="checkbox" name="Checkbox2" checked> SQL Server Agent
<BR>&nbsp;&nbsp;&nbsp;
<input type="checkbox" name="Checkbox3" checked> Integration Services
<BR>&nbsp;&nbsp;&nbsp;
<input type="checkbox" name="Checkbox4" checked> Reporting Services
<BR><BR>
Enter Service Account Name:
<input type="text" name="UserNameBox" size="30" value="domain\DomainUser">
<BR><BR>
Enter Password: &nbsp;&nbsp;&nbsp;&nbsp; <input type="password" name="UserPassword" size="40">
<BR>
Confirm Password: <input type="password" name="UserPasswordConfirm" size="40">
<BR><BR>
<input type="button" value="  Run  " name="RunScript" onClick="OnButtonClick"> 
<BR><BR><BR>
<SPAN ID = "MessageArea"></SPAN>
<BR><BR>
<SPAN ID = "StatusArea"></SPAN>
<BR><BR>
</BODY>

Open in new window