Solved

VBScript for changing SQL service account (almost works)

Posted on 2008-10-24
3
674 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:zabu99
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 19

Accepted Solution

by:
weellio earned 500 total points
ID: 22805691
try this in order to find the information you need

start - run
type "wbemtest"
click connect
paste "root\Microsoft\SqlServer\ComputerManagement" into the tope field
click "enum classes"
click ok (leave it blank)
scroll down and double click on "Sqlservice"
now click on instances

this will potentially provide you the information you need for "strInstance  = xxx"
replace xxx with one of the items you find in here.

notice thisline in the script
\root\Microsoft\SqlServer\ComputerManagement:SqlService.ServiceName='" & strInstance &

this is where i pulled the information in order to find what you are looking for.







0
 
LVL 5

Author Closing Comment

by:zabu99
ID: 31509775
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!
0
 
LVL 5

Author Comment

by:zabu99
ID: 22814702
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

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question