Reporting Services Upgrade from SSRS 2008 to SSRS 2012 Lessons Learned

Let me preface this article. Sperry Hope is my SharePoint + BI engineer. He’s awesome. He pulled off some amazing feats in our Reporting Services upgrade this last week. I asked him to summarize his lessons learned. He’s included the blogs he used as resources as well as videos and downloads.

High level Details

1. SQL 2012 SSRS requires SP1+ or W2K R2 or Above (The error was quite descriptive here.)
2. Cannot communicate with web service: It was the new port communication requirements (blocked by firewall between SharePoint and SQL SSIS servers.)
3. Cannot convert Claims to Windows Token: Special Rights required including local policies as noted below

 

Here’s an overview of the lessons from Sperry in his own words…

"I wanted to get this out before the end of day today to document what happened during the SSRS Upgrade to 2012. We were finally able to get the SSRS install working in a multi-server farm environment with Kerberos authentication. 

Here are a few things we ran into and how we managed to get around them.

Installing the SSRS SharePoint integrated bits on the SQL 2012 Server worked great.  After running the PowerShell commands install-SPRSService
and install-SPRSServiceProxy
and starting the service with get-spserviceinstance -all |where {$_.TypeName -like "SQL Server Reporting*"} | Start-SPServiceInstance everything looked fine.  I then went to add the SSRS Service Application to SharePoint via Central Admin and it didn’t show up in the list of service applications available to install.  I figured we would need to install the SSRS SharePoint bits on every server in the farm so I hooked up the SQL CD to the WFE server and went to run the install.  ERROR – Install requires SP1 on Windows 2008R2 or above to run.  This is my developer’s primary box and I didn’t want to run windows updates on him while he was on vacation so I started digging and found I could download the rsSharePoint.msi directly and install without the full SQL install running.  This worked like a charm and the SQL Report Server Service Application was now available to install on the farm. I figured since the Service App was only going to be running officially on the SQL server that met all OS level patches then I would probably be OK. 

 

When we went to install the Service Application we wanted to try and upgrade our 2008R2 SSRS databases so we pointed to them during the New Service Application settings.  It found the databases and told us they would be upgraded so we thought we were good to go.  After a couple of minutes we got an error saying the upgrade on the ReportServerTempDB failed.  We decided to point to a new database instead of the existing database and the Service Application finally installed.  Knowing that the report files were safe in SharePoint we knew we would lose some history and subscription information but felt like that would be OK to get us up and running.

 

We loaded up a test report and Data Source and got an error. Cannot communicate with web service at http://ssrsserver:32843….  That is the SharePoint Web Service Port which ssrs is now running as a service application so I checked to make sure the IIS pieces were running and then hit the full URL of the service app on port 32843 from the SSRS Server and it was working fine but from my front end server it was not.  Turns out the SSRS Server was in our Database network zone and the front end was in our Application network zone.  Firewall request in and applied and now we are talking to the web service for the Service Application. 

 

Went to test the Data Source link and got the error Cannot convert claims identity to windows token.  Started digging around on the web and found numerous articles and blog posts about making sure the Claims to Windows Token Service (C2WTS) is set up and running in SharePoint.  We had set this up for performance point in our production environment but not in our test environment.  Got out the notes and set up a new service account in test to run the service.  Made sure it was given the correct rights on the SSRS server of act like a part of the OS and able to impersonate another identity.  Set up the delegation to the SQL server we were trying to hit and started the service.  Still got the error Cannot convert claims identity to windows token.  Went through this video and tried to verify we had everything set up correctly.  Got Microsoft Support on the case and they started looking at the issues as well.   I finally found this blog and started focusing on the bz71 error which I found.  Tying the bz71 error to the Process ID I noticed that the User Account running the process was the SSRS Service Account.  I thought the process of converting a claims identity to a windows token would be handled by the user running the C2WTS and the account associated with that service.  It appeared that the process doing that conversion or at least the process throwing the error is actually the service account running the SSRS Service Application.  I then set the permissions for the SSRS Service account in the local policy on the SSRS Server to be the same as the C2WTS Account.  I restarted the Service Applications via central admin and things started working. 

 

Summary: 

1. rsSharePoint.msi needs to be run on every machine in the farm even if they won’t be running the SSRS Service App.
   
2. SSRS Service Application now runs in IIS as a SharePoint Service on port 32843 not port 80 or 443 like in 2008.
   
3. SSRS Service Account needs to be granted the same rights on the SSRS Server as a typical Claims to Windows Token Service Account.  (Local Administrator, Act as part of the Operating System, Impersonate a client after authentication, Run as a Service, member of WSS_WPG)
   
4. You can use separate accounts for SSRS and C2WTS as long as each have the above rights.
   
5. Restarting application pools with IIS manager doesn’t always pick up new settings.  Make sure you stop/start things via Central Admin."
   

 

Sperry Hope