1

When I have just installed MS SQL Server 2012 Express, then the Reporting Services Configuration Manager's "Service Account" page states that I'm not using a "built-in account" but rather "another account" (and that the used account is NT Service\ReportServer$<MyServerName>). My installation script states that I need to change this to the situation pictured below.

If I manually open the Reporting Services Configuration Manager GUI and select the "Built-in" option, then close and re-open it, that option is still set. However, if I use Powershell to call SetServiceAccount() (from Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer.Service) to set a specific account, then the "Use another account" option remains set in the GUI. This is what I want to avoid.

The challenge: How can I programmatically (via Powershell, or something I can call from PS) toggle this option (as well as specify a given account, unless I can rely on "Network Service" being the default)?

MS SQL Reporting Services GUI

Community
  • 1
  • 1
KlaymenDK
  • 714
  • 9
  • 31
  • 2
    Presumably the Reporting Services Configuration Manager is an NT service. (Suggest you double check with `services.msc`). If so then there is a registry value that contains the account name. Something like `HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\\ObjectName`. You could change that value using PS' built in registry support. – Χpẘ Jan 13 '16 at 15:31
  • I meant it the other way around -- I don't want to change the account that's being used, I want to "toggle the radio button" as in the image (where it won't even need an account name). I should not need to provide an account name, as there isn't any, but I image a method needs to be called or a registry bit flipped. – KlaymenDK Jan 13 '16 at 20:15
  • What are you talking about? You *must* specify an account name. Either of a built-in account (without a password) as in your screenshot, or of local/domain account with that account's password. See [here](https://msdn.microsoft.com/en-us/library/windows/desktop/aa384901.aspx) and [here](http://stackoverflow.com/a/4370900/1630171). – Ansgar Wiechers Jan 13 '16 at 22:25
  • Look, I know I'm talking about things I know next to nothing about, except what I have seen in the UI while installing. Sorry, I'm trying my best to understand these arcane (to me) Windows/SQL things. – KlaymenDK Jan 14 '16 at 06:52
  • I suggest you browse through the services (via services.msc for example) and find one that uses Network Service (or whatever you want) and then look in registry to see what values are defined. Then place the applicable values on your service. (I also don't know anything about configuring SQL - this is just based on my knowledge of NT servcies) – Χpẘ Jan 14 '16 at 18:39

2 Answers2

1

So it turns out there is actually some very simple code to do this. My coworker found it, by which arcane magic I do not know.

Here's the code:

# Init
$ns = "root\Microsoft\SqlServer\ReportServer\RS_$sqlInstanceName\v11\Admin"
$RSObject = Get-WmiObject -class "MSReportServer_ConfigurationSetting" -namespace "$ns"
# Set service account
$builtInServiceAccount = "Builtin\NetworkService"
$useBuiltInServiceAccount = $true
$RSObject.SetWindowsServiceIdentity($useBuiltInServiceAccount, $builtInServiceAccount, "") | out-null
# Set virtual directory URLs
$HTTPport = 80
$RSObject.RemoveURL("ReportServerWebService", "http://+:$HTTPport", 1033) | out-null
$RSObject.RemoveURL("ReportManager", "http://+:$HTTPport", 1033) | out-null
$RSObject.SetVirtualDirectory("ReportServerWebService", "ReportServer", 1033) | out-null
$RSObject.SetVirtualDirectory("ReportManager", "Reports", 1033) | out-null
$RSObject.ReserveURL("ReportServerWebService", "http://+:$HTTPport", 1033) | out-null
$RSObject.ReserveURL("ReportManager", "http://+:$HTTPport", 1033) | out-null
# Restart service
$serviceName = $RSObject.ServiceName
Restart-Service -Name $serviceName -Force

All done. So simple. I really don't want to think about how many heartbeats of my life I've wasted on this.

KlaymenDK
  • 714
  • 9
  • 31
0

I reused the accepted answer to set a Service Account (using SQL 2016):

$ns = "root\Microsoft\SqlServer\ReportServer\RS_SSRS\v13\Admin"
$RSObject = Get-WmiObject -class "MSReportServer_ConfigurationSetting" -namespace "$ns"
# Set service account
$serviceAccount = "domain\SRV-ACCOUNT"
$servicePW = "password"
$useBuiltInServiceAccount = $false
$RSObject.SetWindowsServiceIdentity($useBuiltInServiceAccount, $serviceAccount, $servicePW) | out-null

$HTTPport = 80
$RSObject.RemoveURL("ReportServerWebService", "http://+:$HTTPport", 1033) | out-null
$RSObject.RemoveURL("ReportManager", "http://+:$HTTPport", 1033) | out-null
$RSObject.SetVirtualDirectory("ReportServerWebService", "ReportServer", 1033) | out-null
$RSObject.SetVirtualDirectory("ReportManager", "Reports", 1033) | out-null
$RSObject.ReserveURL("ReportServerWebService", "http://+:$HTTPport", 1033) | out-null
$RSObject.ReserveURL("ReportManager", "http://+:$HTTPport", 1033) | out-null

$serviceName = $RSObject.ServiceName
Restart-Service -Name $serviceName -Force

Using a Service Account you can use NTML (default config): https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/cc281253(v=sql.105)

Then SetSPN for Report Server.

eg:

C:\>setspn –F -S HTTP/MachineName domain\SRV-ACCOUNT

C:\>setspn –F -S HTTP/MachineName.domain.com.au domain\SRV-ACCOUNT
Jeremy Thompson
  • 61,933
  • 36
  • 195
  • 321