RSConfig generates a Dsn Connection String doesn't work

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 loggingStart-Transcript -Path "C:\temp\rename-ssrs-computer.txt"#2. Change the SQL Server's nameWrite-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,'''GOsp_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 masterDECLARE @AccountName nvarchar(260)SET @AccountName = SUSER_SNAME(0x010100000000000514000000)if not exists (select name from syslogins where name = @AccountName and hasaccess = 1 and isntname = 1)BEGINEXEC sp_grantlogin @AccountNameEND;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 NameWrite-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" $txtregedit /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 ProcessWrite-Host $rsConfigPath $setupArgsSet-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 ServiceWrite-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

The issue is not with the RsConfigTool.exe. It works, and indeed changes the connection string properly.

The issue simply boils down to single quotes vs. double quotes and having a $ sign in the name.

From the docs:

When you enclose a string in double quotation marks (a double-quoted string), variable names that are preceded by a dollar sign ($) are replaced with the variable's value before the string is passed to the command for processing.

We can see this when we try to output the database name:

PS C:\> Write-Output "ReportServer$SSRS"ReportServer

As we can see, it returns "ReportServer" and then the contents of the $SSRS variable (which is empty).

To prove this, if we create and set a value to the $SSRS variable:

PS C:\> $SSRS = "SomethingElse"PS C:\> Write-Output "ReportServer$SSRS"ReportServerSomethingElse

We get "SomethingElse" ;-). But if we enclose it in single quotes, it does not do a variable replacement:

PS C:\> Write-Output 'ReportServer$SSRS'ReportServer$SSRS

So, the fix, is when calling the RsConfigTool.exe tool from PowerShell, simply replace the double quotes with single quotes:

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