CODEWITHSUNDEEP
sql-serverpowershellconfigurationautomation
KlaymenDK
KlaymenDK

Reputation: 724

Change MS SQL Reporting service account to built-in "Network Service"

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

Upvotes: 1

Views: 4539

Answers (2)

Jeremy Thompson
Jeremy Thompson

Reputation: 65534

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

Upvotes: 0

KlaymenDK
KlaymenDK

Reputation: 724

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.

Upvotes: 1

Related Questions