0

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.

Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
KlaymenDK
  • 714
  • 9
  • 31

1 Answers1

1

sa is a database user (the builtin database administrator account), not a Windows user. For running a service you need a Windows user account. Usually either a dedicated account you created for running the service (with the required permissions/privileges) or one of the builtin default accounts like NT AUTHORITY\Network Service or NT AUTHORITY\Local Service.

Pass an empty string as the second parameter when using one of the builtin accounts, since they don't have a password:

$service.SetServiceAccount('NT AUTHORITY\Network Service', '')

With that said, normally you should have specified the runas account for the database service during the installation, so there shouldn't be a need for changing it afterwards.

Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
  • Thank you for your speedy reply. I tried supplying `NT AUTHORITY\Network Service` (along with the `$sapwd`) but got the same error. I then tried the credentials for the currently-logged-in user, and that seemed(!) to work. This is probably not the best user for the task, though, and I shall ask the client about user configuration. – KlaymenDK Jan 13 '16 at 11:42
  • Uhh, thanks Microsoft, for making the account `NT AUTHORITY\Network Service` _localisation-dependent_. >_< I'm trying to understand "virtual accounts" and whether they have a role here, and if not -- how to find out the local naming of the "network service" account. – KlaymenDK Jan 13 '16 at 11:52
  • _Update:_ The localisation challenge is now [solved](https://stackoverflow.com/questions/34767058/get-localised-account-name-for-the-nt-authority-network-service-account-or-h). – KlaymenDK Jan 13 '16 at 13:31
  • The builtin accounts don't have a password, so don't use `$sapwd` for them (unless the variable is empty). See updated answer. – Ansgar Wiechers Jan 13 '16 at 13:33
  • Well, (a) if I supply a blank string for password, then I get the error message again; and (b) I don't know "normally", I just know how a (this) clean install works. I wonder if this will break horribly once it hits the Real World. :( – KlaymenDK Jan 13 '16 at 14:16
  • Hmm... weird. But like I said before, you shouldn't need to change the runas account in the first place, as it should have already been set during setup. – Ansgar Wiechers Jan 13 '16 at 14:19
  • Hmm, well then maybe I'm doing the wrong thing, and asking the wrong question. I've asked [another question](https://stackoverflow.com/questions/34769856/change-service-account-to-built-in-network-service). – KlaymenDK Jan 13 '16 at 14:58