Upgrading SQL Server Reporting Services 2008 R2 to SQL Server Reporting Services 2012 in SharePoint | Quisitive

Upgrading SQL Reporting Services (SSRS) to SSRS 2012 should be a fairly easy task, but there are a few “bumps in the road” to watch out for.  For this discussion let’s pretend that we have a SharePoint 2010 farm with 2 WFE, 2 APP, and 1 DB servers.  SSRS 2008 R2 has been installed on both of the APP servers with a load balanced URL of spreports.contoso.local, each service uses a unique service account identity, all SPNs have been created, and Kerberos constrained delegation is configured and working properly.

Getting started I’m sure you have a few questions, hopefully this will answer a few of them.

Starting the Upgrade

Ok, now on to the upgrade. Typically what you would do is pop in the installation media for SQL server in the “CD” of each SharePoint server running an instance of SSRS. In this case it is the two App servers.  Just make sure to choose the option to Upgrade from SQL Server 2005, SQL Server 2008 or SQL Server 2008 R2.

Once the upgrade wizard starts, it will install the setup files, run the System Configuration Checker, and it will detect the existing SSRS instance for you to upgrade. You pretty much just need to just move the wizard along until you get to the point in the wizard page called Reporting Services SharePoint mode Authentication.  This is where I hit my biggest speed bump.

Error: The credentials you provided for the ” service is invalid.

Typically it is a best-practice to use a different domain account for each service instead of use the built-in accounts like Network Service.  I think though, there may be an issue with the SQL installer when upgrading SSRS in SharePoint integrated mode to 2012.  Every time that I would enter the password for the SSRS service account I would get an error that states:

The credentials you provided for the ” service is invalid.

Every time that I would enter the credentials and click Next I would see this error and then later, also realized that the service account was getting locked out because of too many bad password attempts – even though I just clicked Next just once.

The solution: Change the service account for the SharePoint SSRS instance to run using Network Service.  After the upgrade is complete, you change change the credentials of the new SharePoint service application pool back to the previously used account.  To change the credentials of the service account to Network Service, open the Reporting Services Configuration Manager and change the account. You will probably be prompted to backup your encryption keys during the process.

Now, you should be able to sail through the installation to perform the upgrade on each server running an instance of SSRS. Once the upgrade is complete, you should see the new service application listed in SharePoint Central Administration and a new service application pool will have been created as well.  Once the upgrade is complete the new service application pool for the SSRS service application should be set to execute using the original credentials that SSRS was previously running and not Network Service.  To change this, open Central Administration and click the Security link in the left menu and then Configure service accounts.

In the list of Service Accounts you should see a new service application pool that was created by the SSRS upgrade with a name similar to Service Application Pool – MSRSSHP11.0_MSSQLSERVER.  Select this application pool and in the bottom drop box, register the account that was previously used by SSRS and select it to change the service identity of the new service application from Network Service to the previously used domain account that has all of the required SPNs already created.

You should then be able to use the newly created SQL Server Reporting Services 2010 service application.

But wait, there are some additional steps that you may want to accomplish before calling the upgrade complete.

Rename the Service Application

Personally, I don’t really care for the new default name of the new service application (MSRSSHP11.0_MSSQLSERVER_Service_Application) and I want to give it a more friendly name, something like SQL Server Reporting Services.  There is not a way to rename the proxy using the GUI, but I don’t really care so much about the proxy so I just left it as is.

To do this you can click select the service application and then click the Properties button in the ribbon above to change the name.

This is where I ran into the next issue.

Grant the SharePoint Farm Account db_owner on the 3 Reporting Services databases.

When I tried to rename the service application I was presented an error that stated that it was unable to open the ReportServerTempDB and that the login had failed for the SharePoint farm account.

Now that reporting services is hosted as a SharePoint service application, the SharePoint farm account should be granted db_owner permissions on all 3 of the Reporting Services databases – yes, there are now 3 of them.

Error: Login Failed for “NT Authority\Anonymous Logon”

When I finally went to test one of the existing reports I was greeted with yet another error.  The error basically stated that it could not connect to one of the data sources in the report as an anonymous user when I would have expected it to connect as my user account.  This led me to believe that there was a Kerberos problem.

In this environment the Claims to Windows Token Service (C2WTS) had already been configured and SPNs and delegation for C2WTS had already been setup for all of the same data sources in order to support Excel Services.  If you have not yet configured C2WTS you should create a SPN (doesn’t matter what it is, I just called mine SP/C2WTS) so that the Delegation tab is exposed in Active Directory Users and Computers.  You will then need to configure constrained delegation for the services accounts and SPNs for all of the data sources that you plan to connect to.  Really though, all of the delegation settings for the account used for the SSRS service should also be replicated for the account running C2WTS.

Every time that I have to setup Kerberos in a SharePoint environment I always refer back to a great Microsoft document Configuring Kerberos Authentication for Microsoft SharePoint 2010 Products. I decided to review the section for setting up Excel services and started double checking my environment to make sure that everything was setup similarly for SSRS.  There was one statement in the guide that caught my attention:

“Select Use any authentication protocol. This enables protocol transition and is required for the service account to use the C2WTS.”

I double checked the service account for which SSRS was running and sure enough, it was set to Use Kerberos only.  Once I had this changed to “Use any authentication protocol” and restarted all of the appropriate services my reports started working again!

Reclaim the old DNS Record

Now that SSRS is running in SharePoint as a service application the friendly URL that was being used to serve reports and load-balance requests was no longer being used.  I was able to reclaim the old URL of spreports.contoso.local.