RSConfig generates a Dsn Connection String doesn't work

Question

TL;DR.

Repro steps, take a backup of your C:\Program Files\Microsoft SQL Server\MSRS13.SSRS\Reporting Services\ReportServer\RsReportServer.config

Run this command to update the connection string in SSRS's config:

C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn>rsconfig -c -s <ServerName> -i <instanceNameIfNotDefault> -d "reportserver$ssrs" -a SQL -u sa -p "YourSAPassword" -t

Now browse to the SSRS website and it doesn't work! To fix it either restore your config file or run through the SSRS GUI tool and it works!

How does the RsConfig utility work?

---

Background
After I install SSRS on an Windows 2016 Server and restore the 2 databases I need to change the Connection String in SSRS configuration file to point to the new SQL server name/instance.

Problem
When I try to change the encrypted Connection String in C:\Program Files\Microsoft SQL Server\MSRS13.SSRS\Reporting Services\ReportServer\RsReportServer.config file using the RSConfig utility:

C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn>rsconfig -c -s Server0012 -i SSRS -d "reportserver$ssrs" -a SQL -u sa -p "P@ssw0rd!" -t

It changes the Dsn Connection String in the RsReportServer.config.

Before: <Dsn>AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAE+tJc/4Vs0a0fdH0tCY8kgQAAAAiAAAAUgBlAHAAbwByAHQAaQBuAGcAIABTAGUAcgB2AGUAcgAAABBmAAAAAQAAIAAAAC2DBxZFsfVB16r0e3...... *

After: <Dsn>AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAE+tJc/4Vs0a0fdH0tCY8kgQAAAAiAAAAUgBlAHAAbwByAHQAaQBuAGcAIABTAGUAcgB2AGUAcgAAABBmAAAAAQAAIAAAAO2nOjFDJMo........ *

However after this change, browsing to the SSRS Website results in the error:

The report server can’t connect to its database. Make sure the database is running and accessible. You can also check the report server trace log for details.

If I run the SQL Reporting Services Configuration Tool (GUI) and change the Dsn Connection String browsing to the SSRS Website works!

Obviously it changes the Dsn but I can't work out what else it does whilst the GUI tool is running. I've used ProcessMonitor and I've seen that the GUI tool does NOT use RSConfig.exe utility, it uses itself RsConfigTool.exe! So I can't even capture command-line arguments of what the actual command/connection string should be. Also each time we change the connection string a new random one is generated so not sure how to do comparison's of actual vs expected.

I did a WinDiff of Registry keys and apart from some encrypted hexadecimal diffs, nothing stood out.

I run SQLProfiler and there were a bunch of grants that I have emulated in my PowerShell script, eg:

$sqls += @"
USE [ReportServer`$SSRSTempDB]
if not exists (select * from sysusers where issqlrole = 1 and name = 'RSExecRole')
BEGIN
 EXEC sp_addrole 'RSExecRole'
END;
GO

My hunch is the $ sign in the SQL Database Name and the @ in the "made up/simulated" password are not getting escaped when I run the commands, eg:

$MachineName = "server0012"
$instanceName = "SSRS"
$saPassword = "P@ssw0rd!"

$rsConfigPath = "C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\rsconfig.exe"
$setupArgs = -join('-c -s "', $MachineName,'" -i "', $instanceName,'" -d ','"ReportServer`$SSRS" -t -a SQL -u "sa" -p "', $saPassword,"""")

Set-ExecutionPolicy -ExecutionPolicy Unrestricted -Scope Process
Write-Host $rsConfigPath $setupArgs
$args = $setupArgs.Split(" ")
& "$rsConfigPath" $args

Restart-Service -Force "SQL Server ($instanceName)"

When I run these vanilla commands in Command Prompt (no need to escape PowerShell characters):

rsconfig -c -s Server0012 -i SSRS -d "reportserver$ssrs" -a SQL -u sa -p "P@ssw0rd!"

It changes the Dsn Connection String but browsing to SSRS Website gives same error (above).

How can I find out what else RsConfigTool.exe does when changing the Current Report Server Database? Or any guesses why the Connection String generated using the RSConfig Utility is out of whack - I've tried many different combinations, seems like only the RSConfigTool can actually do it?

Note 1:
I'm scripting this all up as a DevOps project and we are baking these images with packer, so nothing can be done manually.

Note 2:
The Machine is joined to the domain and renamed after SQL installed. So using a Configuration.ini file I don't think will work.

Answer 1

The trick is you need to use the Powershell Invoke-Expression command, the server name has to include the instance name without quotes server\instance, and you DO need to escape the $ sign in the RsConfig.exe command: -d ','"reportserver<tilda>$ssrs"'

<tilda> = ` The tilda key that escapes the $ sign, see in script below.

If you don't use Invoke-Expression and escape the $ sign the DatabaseName is called ReportServer not ReportServer$SSRS

You can see this in the SSRS Logs:

library!WindowsService_1!30c!05/17/2019-03:56:29:: e ERROR: Throwing Microsoft.ReportingServices.Library.ReportServerDatabaseUnavailableException: , Microsoft.ReportingServices.Library.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. ---> System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Here is the script I use to fix a broken SQL install on a server that's been renamed:

Param(
    [parameter(mandatory=$true,helpmessage="New Machine Name")]
    [string]$MachineName,

    [parameter(mandatory=$false,helpmessage="SQL Instance Name")]
    [string]$instanceName = "SSRS",

    [parameter(mandatory=$false,helpmessage="SQL SA Password")]
    [string]$saPassword = "P@ssword1"  #this is encrypted IRL
)


#1. Start the logging
Start-Transcript -Path "C:\temp\rename-ssrs-computer.txt"

#2. Change the SQL Server's name
Write-Host "Change the SQL Server Instance Name to $MachineName"


$moduleName = "SqlServer"
Import-Module $moduleName -Verbose

$sql = 'select @@SERVERNAME'
$serverNameQry = Invoke-SqlCmd -Serverinstance ".\$instanceName" -Query $sql -username "sa" -password $saPassword  -querytimeout ([int]::MaxValue)
$serverName = $serverNameQry.Column1

$sql = -join('sp_dropserver ''', $serverName,'''
GO
sp_addserver ''', $MachineName, "\", $instanceName,''',''local''
GO
')
Invoke-SqlCmd -Serverinstance ".\$instanceName" -Query $sql -username "sa" -password $saPassword  -querytimeout ([int]::MaxValue)

#3. Change the SSRS database permissions
$sqls = @()
$sqls += @"
USE master

DECLARE @AccountName nvarchar(260)
SET @AccountName = SUSER_SNAME(0x010100000000000514000000)
if not exists (select name from syslogins where name = @AccountName and hasaccess = 1 and isntname = 1)
BEGIN
EXEC sp_grantlogin @AccountName
END;
GO
"@

#..... all the SQL Profile trace outputs...#

Foreach ($sql in $sqls)
{
  Invoke-SqlCmd -Serverinstance ".\$instanceName" -Query $sql -username "sa" -password $saPassword  -querytimeout ([int]::MaxValue)
}

#4. Change all the registry key values with the AMI Original Computer Name
Write-Host "Change the SQL Server Name in the Registry to $MachineName"

$txt = -join('Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\Machines]
"OriginalMachineName"="',$MachineName,'"

[HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Microsoft SQL Server\90\Machines]
"OriginalMachineName"="',$MachineName,'"

[HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Microsoft SQL Server\140\Machines]
"OriginalMachineName"="',$MachineName,'"

[HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Microsoft SQL Server\130\Machines]
"OriginalMachineName"="',$MachineName,'"

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Services\SSIS Server]
"GroupPrefix"="SQLServerDTSUser$',$MachineName,'"
"LName"=""
"Name"="MsDtsServer"
"Type"=dword:00000004

[HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Microsoft SQL Server\Services\SSIS Server]
"GroupPrefix"="SQLServerDTSUser$',$MachineName,'"

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Services\Report Server]
"Name"="ReportServer"
"LName"="ReportServer$"
"Type"=dword:00000006
"GroupPrefix"="SQLServerReportServerUser$',$MachineName,'$"

[HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Microsoft SQL Server\Services\Report Server]
"Name"="ReportServer"
"LName"="ReportServer$"
"Type"=dword:00000006
"GroupPrefix"="SQLServerReportServerUser$',$MachineName,'$"'

)

Add-Content "C:\temp\output.reg" $txt
regedit /s "C:\temp\output.reg"


#5. Set the encrypted connection string DONT CHANGE THIS!!!
$rsConfigPath = "C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\"
$setupArgs = -join('-c -s ', $MachineName, '\' , $instanceName,' -i ', $instanceName,' -d ','"reportserver`$ssrs"', ' -t -a SQL -u sa -p "', $saPassword,'"')
Write-Host "Setup args for RSConfig $rsConfigPath $setupArgs"
Write-Host "Running RSConfig"
Set-ExecutionPolicy -ExecutionPolicy Unrestricted -Scope Process
Write-Host $rsConfigPath $setupArgs

Set-Location "$rsConfigPath"
Invoke-Expression $("rsconfig.exe " + $setupArgs) 

Write-Host "RSConfig Dsn complete, new Connection string under Dsn saved to rsconfig.config file."


#6. Restart the SQL Service
Write-Host "Restarting $instanceName"
Restart-Service -Force "SQL Server ($instanceName)"
Write-Host "Restarted $instanceName"


#7. Set regional format (date/time etc.) to English (Australia) - this applies to all users 
Import-Module International 
Set-Culture en-AU 
# Check language list for non-US input languages, exit if found 
$currentlist = Get-WinUserLanguageList 
$currentlist | ForEach-Object {if(($.LanguageTag -ne "en-AU") -and ($.LanguageTag -ne "en-US")){exit}} 
# Set the language list for the user, forcing English (Australia) to be the only language 
Set-WinUserLanguageList en-AU -Force 
Set-TimeZone -Name "AUS Eastern Standard Time"


# Lastly Stop the transcript (before the PC gets rebooted by the calling script).
Stop-Transcript