Solved

VBScript for changing SQL service account (almost works)

Posted on 2008-10-24
3
659 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
  • 2
3 Comments
 
LVL 19

Accepted Solution

by:
weellio earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now