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.

• My web application is currently configured for classic mode authentication. Do I need to configure my web app for claims authentication?
  The answer is no.  Communication between the web app and SharePoint service applications is almost always converted to claims by the SharePoint STS for intra-farm communication, even if the web app is using classic mode for user authentication. However, you will need to use the Claims to Windows Token Service to convert the claims token back to a Windows Identity when connecting to data sources.
• Do I need to uninstall SSRS 2008 R2 first?
  No.  The SSRS instance will automatically be upgraded when running the upgrade from the SQL installation media.
• Do I have to upgrade the SQL database server which the databases reside?
  No you do not, but it would probably be a good idea.
• Is there any additional Kerberos configuration that is needed?
  Maybe. Using the same identity for the new SharePoint SSRS service application will keep all of the previous configuration intact, but you may need to perform some additional Kerberos related tasks for the Claims to Windows Token service – more on this later.
• Do I need to run the SQL upgrade on all servers in the farm?
  You can, but I typically would just run it on only the servers that were running an SSRS instance and install the SharePoint Add-In individually on all remaining farm servers.
• Do I need to do anything to my existing reports?
  No.  The upgrade process will take care of this for you the first time that each report is opened.
• I have PowerPivot installed in my SharePoint farm will that continue to work?
  If installed on the same server as SSRS then no.  You will have to completely uninstall PowerPivot before installing SSRS.  Afterwards you can install PowerPivot again from the SQL 2012 installation media.
• Is there a difference between SSRS enterprise, BI, or standard?
  Yes. The standard edition does not include PowerView or alert subscriptions.

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.