I'm trying to automate the post-install configuration of an SQL+SSRS server installation (MS SQL 2012 Express) -- I think it's more or less this. However, this is my first foray into Powershell, and I have never before configured or even used MS SQL. My server is a Windows 2008 R2 virtual machine, where I'm an admin, and the SQL server is installed locally (not clustered). I'm using Powershell v2.0 to run the following snippet (as an admin):
Write-Output("- Setting service account ...")
Write-Output(" - Loading assembly ...")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null
Write-Output(" - Loading server ...")
$mc = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer "$hostname"
Write-Output(" - Loading service ...")
#$service = $mc.Services | Where {$_.Name -eq "MSSQL`$$sqlInstanceName"}
$service = $mc.Services | Where {$_.Name -eq "ReportServer`$$sqlInstanceName"}
Write-Output(" - Changing service account credentials ...")
$service.SetServiceAccount("$hostname\sa", "$sapwd")
Write-Output(" - Commit ...")
$service.Alter()
Write-Output("- OK.")
Between "Changing service account credentials" and "Commit", my code fails with this message:
Exception calling "SetServiceAccount" with "2" argument(s): "Set service account failed."
The $sapwd
is the password given during the SQL server installation. I'm never asked for a corresponding user name, but am told that there is an "SA" user is implied. I don't have a Windows user by that name on the local machine; I'm unsure of what users are defined in a (newly-installed) SQL server.
My goal here is the equivalent of using the GUI to set the "Service Account" to "Use built-in account: Network Service", The code snippet is based on this code. This post is not totally unrelated, but (a) it's not answered, and (b) I don't use clustering.
Many others have had this problem, but there don't seem to be any solid answers that work for me. How can I even find information about what this error means, and how to work around it?
Bonus
I'll also need to change the "Web Service URL" and the "Report Manager URL"; if you've any tips on how to do that you'll save me a bunch of hours.