Upgrade SSRS SQL Server Reporting Services to 2019 for SCCM Infrastructure

Learn how to upgrade SSRS SQL Server Reporting Services to 2019 for SCCM infrastructure. The in-place upgrade to SSRS 2019 for the SCCM primary server reporting point is explained in this post.

It’s so important that you need to make sure that the Upgrade of SCCM secondary server SQL DB, Install of Reporting Services 2019, and Upgrade of SCCM primary server SQL DB to 2019 is in the correct sequence.

Uninstallation of Reporting services 2014 or prior and removal of reporting point role are some of the additional steps that you need to complete before the primary server SQL DB upgrade to 2019 in some scenarios. More details are in the below section.

If you have a standalone primary server, you must start the SQL 2019 upgrade for the secondary server SQL upgrade as the first step. You must always begin the SQL server in-place upgrade to 2019 with the CAS server if you have an SCCM (ConfigMgr) hierarchy with CAS.

SQL Server Reporting Services (SSRS) is a server-based reporting platform that provides comprehensive reporting functionality. The SCCM has tight integration with SCCM reporting services point this integration helps SSRS to produce SCCM default reports.

The following articles help you complete SQL Server in-place Upgrade scenarios for your organization.

• SQL Server 2019 Upgrade For SCCM Secondary Server (Part 1)
• Upgrade SSRS SQL Server Reporting Services to 2019 (Part 2) – This Post
• Upgrade SCCM Primary Server SQL Database to 2019 (Part 3)
• Install New SSRS Reporting Services Point in SCCM

Backup Existing SCCM SSRS Custom Reports

Let’s discuss how to backup the existing SCCM SSRS custom reports as part of the Upgrade of SCCM Reporting services to 2019. You can also use the PowerShell script to back up SCCM custom reports. It’s recommended to backup the custom reports and default reports using the PowerShell script (thanks to Garth Jones).

Try the following option (Download) from your SSRS report to download the custom report if you are like me. I have shown the default report in the screenshot below. However, the backup option is the same for both custom and default reports from SSRS.

Backup Encryption Key Before SQL Reporting Services Upgrade to 2019

You need to backup Encryption Key Before SQL Reporting Services Upgrade using the following method before the upgrade to 2019. If you have already configured the reporting service point on the site, follow the below step before the upgrade.

• Open Reporting Server Configuration Manager.
• Click on Ecryption Keys tab.
• Clcik on backup button to Encryption Keys.

Backup the key to a Password Protected file for reports server recovery in case of emergency. You need to specify the name and location of a file that will contain a copy of the key.

You must specify a password used to lock and unlock the file. Store encryption keys somewhere safe with the password. You need the same While restoring the ReportServer database and password after the SSRS upgrade to 2019.

• File Location – F:\Encrypt\ENCRYPT.snk
• Enter the Password “twice” and click on the OK.

Remove Reporting Point Site System Role

You will need to Reporting Point Site System roles are removed before the primary SQL DB server upgrades to 2019. This is only applicable for SQL Server 2014 or prior.

• Navigate to \Administration\Overview\Site Configuration\Servers and Site System Roles.
• Select the Site system server or Site System where Reporting Server role is installed.
• Click on Reporting Services Point role and Select Remove Role option as shown below.

Uninstall SQL Server Reporting Services (SSRS) 2014

Ensure the Reporting Services role is removed from the previous version of the SQL Reporting Services role if you have installed SQL Server 2014 or prior. This is required in the case of SQL server 2014 or prior.

You need to click on the Remove option to launch the Remove SQL Server 2014 wizard. Select the Reporting Services – Native Role and click on the Next button to remove the SSRS 2014 role and keep all the other SQL services intact.

NOTE! – We will upgrade all the other SQL server components shown below to 2019 as explained in the SCCM primary server SQL DB upgrade post (coming soon).

Reporting Services – Native role is uninstalled or removed successfully. It’s always better to restart the server after the uninstall of reporting services role.

Click on Close to continue with the primary server SQL DB upgrade to 2019 as explained in the below post (Coming Soon).

Upgrading SQL Server will Uninstall Reporting Services Error

If you don’t uninstall the reporting services before the primary server SQL DB upgrade (only if SQL Server Reporting services 2014 is installed), then you get the following error.

You can use this option also to uninstall the older version of SSRS during the primary server SQL DB upgrade to 2019.

Prerequisites before Upgrading to SQL 2019

Let’s check prerequisites before Upgrading to SQL 2019. The supported operating system to install or upgrade (in-place upgrade) Server 2016 (or later) and Windows 10 (or later).

• .NET Framework 4.7 or later.
• SQL Server Database Engine (2012 SP4 or later), to store the report server database.
• SQL Server Database Engine should be installed. You’ll need to have or install a Database Engine instance on this server or on a different server.
• The logged-in user must have local admin and SQL admin permission.

NOTE! – You will need to make sure the SQL server DB configurations are optimal. It’s always better to take a snapshot of the SSRS server before the in-place upgrade. Also, need to ensure the Reporting Server and Log file backups are in place. In some of the scenarios, you might even need to detach the reporting server-related DBs using SSMS.

In-place Upgrade SSRS SQL Server Reporting Services to 2019 for SCCM Infrastructure

Ensure you have completed all the prerequisites and backup-related tasks before proceeding with the in-place upgrade of SSRS to 2019. You can download the Microsoft SQL Server 2019 Reporting Services from the following Microsoft link.

Download – SSRS 2019 from Microsoft Download Page.

Log in to the remote Reporting services point server with administrative permission. You need to click on SQLServerReportingServices.exe to start the in-place upgrade process of the SSRS 2019 upgrade.

• Click on Install Reporting Services.

To find your product key for SQL reporting Services (SSRS), you need to download and install SQL Server 2019 and copy the pre-populated key, as you can see below.

Paste the SQL Reporting Services key from the above SQL server 2019 pre-populated Key.

• Select Enter the product Key option and paste the product key copied from SQL server 2019 installation wizard.
• Click on the Next button to continue.

You need to accept the Microsoft SQL Server 2019 Reporting Services license agreement. Click on the Next button to continue.

You will need to have an SQL Server Database Engine instance to store the report server database. You’ll need to have or install a Database Engine instance on this server or a different server.

• Click on Install Reporting Services only option as you see below.
• Click on the Next button to continue.

You need to specify an install location of SQL Server. I recommend installing SSRS on the drive other than C:\ drive. Don’t install SSRS on C:\ drive.

• Click on Broswe button to find the appropriate location to store SSRS 2019.
• Click on the Install button to continue.

How to Configure and Customize SSRS 2019 Manually

Let’s try and check how to Configure and Customize SSRS 2019 Manually. Setup has installed the files you need. You’re ready to configure your report server. We’re starting a report server configuration manager to configure your report server.

• Click on Configure Report Server button.

How to Configure SQL Reporting Services for SCCM

Let’s learn How to Configure SQL Reporting Services for SCCM. In this part, we will learn the configuration details of the SQL Reporting Services.

Open the SSRS Configuration page and connect to the Report Server Instance. The SQL server name and Report Server instance name got populated automatically, as you see in the below screenshot.

• Click on Connect button to continue.

Navigate to the Service Account tab from the left side menu. Click on use another account if you use a service account for SQL Reporting Services (SSRS).

• Enter the Service account and password and click on apply.

Select the Web Service tab and verify the default provided values. If you wish to make any changes to the Report Server URL, modify it according to your requirements.

• Click on Apply to save the settings.

Continue with SSRS Configuration for SCCM

Select the Database tab on the left and then click on Change Database. Select one of the following options to create an empty report server database or select an existing report server database with the content you want to use.

• Select the option Choose an Existing Report Server Database.

Click on the test connection tab to check the connectivity to the SQL database engine and proceed to the next step on success.

Select the existing Report Server Database – Report Server. Click on the Next button to continue.

Click on the Next button to continue from the Credentials tab.

Click on the Next button to continue from the Summary page.

Report Server Database Configuration wizard configures the database during this stage. The following tasks to configure the SQL Reporting Services database are completed.

• Verifying database SKU.
• Verifying instances that created database.
• Generating rights scripts.
• Applying connection rights.
• Setting DSN.

Stopping report server “SQLServerReportingServices” on SQLMEMCM.
Starting report server “SQLServerReportingServices” on SQLMEMCM.

Results SQL Server Reporting Services (SSRS) is upgraded to 2019

As you can see the SQL Server Reporting Services (SSRS) is upgraded to 2019. Even though, the primary server SQL DB is still running on SQL Server 2014.

Microsoft SQL Server Reporting Services Version 15.0.1102.932

Restore Encryption Key for SSRS

You can restore the Encryption Key of the old ReportServer Database from the Encryption Keys tab. To restore the encryption key, click the Restore button. You must know the password used to protect the encryption key file.

• File Location – F:\Encrypt\ENCRYPT.snk
• Enter the Password and click on the OK button to continue.

SQL Reporting Services Error

I had seen SQL Reporting Services Error when I clicked on the SSRS URL mentioned above; you get the following error that is it because SQL Server is not migrated to 2019?

Error: The version of the report server database is either in a format that is not valid or it cannot be read. The found version is ‘2017’. The expected version is ‘165’. (rsInvalidReportServerDatabase). SQL Server Reporting Services.

You can continue with the configuration of SQL Reporting Services 2019 and will come back to the error later (after the SQL Server 2019 installation in the next post).

FIX: The error in the below screenshot is probably because of the different versions of SQL SSRS roles available on the SQL server. Uninstall the SQL Server Reporting Services 2014 from the server as described in the above section of this post.

• Fix SCCM Report Server Cannot Open A Connection Error
• SCCM Troubleshooting Tips On Reporting Server SSRS Issues

Reinstall SCCM Reporting Services Point

If you have removed the SCCM Reporting Services point because of SSRS 2014 dependency, you can reinstall the reporting point role using the following guide.

• How to Install ConfigMgr SSRS Reporting Services Point

Author

Anoop C Nair is Microsoft MVP! He is a Device Management Admin with more than 20 years of experience (calculation done in 2021) in IT. He is Blogger, Speaker, and Local User Group HTMD Community leader. His main focus is on Device Management technologies like SCCM 2012, Current Branch, and Intune. He writes about ConfigMgr, Windows 11, Windows 10, Azure AD, Microsoft Intune, Windows 365, AVD, etc.