When preparing to perform an in-place upgrade of SSRS 2008 R2 SP1 (SharePoint Integrated Mode) to SSRS 2012, be sure that the current SSRS service account is *not* already a SharePoint managed account.
One of my latest tasks has been to assist a customer with an upgrade of their SQL Server 2008 R2 Business Intelligence (BI) components to SQL Server 2012. My specialty is SharePoint and not SQL Server, however with BI lines blurring with all of the integration between SQL Server BI and SharePoint 2010 we all have to stretch our muscles and dip out toes into unfamiliar waters at times.
The environment was a proof of concept (POC) so it’s not a full production environment and the configuration was such that one server hosted:
- SharePoint Server 2010 (RTM)
- SQL Server 2008 R2 (RTM)
- Database Engine
- Reporting Services – SharePoint integrated mode
- Analysis Services
Due to a variety of factors we did not have time for a full project plan to upgrade this scenario so our high-level plan consisted of the following:
- Backup the environment in case the worst happens.
- Upgrade SQL Server 2008 R2 to SP1 (this is necessary to upgrade to 2012)
- Upgrade SharePoint 2010 to SP1 (this is necessary to support a SQL Server 2012 backend)
- Install Jun 2011 CU and April 2012 CU (just to get current)
- Run PSconfig for SharePoint
- Run an in place upgrade of SQL Server from 2008 R2 SP1 to 2012
While this seems simple enough, there are a variety of things that could go wrong so I setup some VMs to run through tests before trying it out on the customer’s environment. I had setup the VMs mimicking the customer’s service configuration as closely as possible, but I did not ask about service accounts. In my test environment I used a single account (domain admin) for all the services and logins. A bad practice for sure, but for this testing environment it seemed adequate…it was not.
Everything went well until running the in place upgrade of SQL Server to version 2012. First I noticed this where it was prompting for my credentials, but notice that the username is auto-populated and disabled:
Next I received the following errors during setup:
After you have this problem, you may notice that SQL Setup recommends you uninstall all the affected components in the instance that you attempted to upgrade. In my case, I was able to simply reboot my server and it came back up successfully to continue with the next actions. It may have been possible to just restart the DBEngine, SSAS, and SSRS services from SQL Configuration Manager, but I wanted to be sure everything was cleared out.
My first thought was that SQL setup was taking my logged on user account for the SSRS 2012 Service Application configuration and was failing to create a SharePoint managed account because it already existed. I was only partially correct.
After talking with a peer who specializes in SQL BI, Adam Saxton, we decided what was more likely was that setup was taking the service account from the existing SSRS configuration and trying to create a SharePoint managed account. So in my VM environment I used Reporting Services Configuration Manager and changed the service account to a new account that I created in Active Directory:
After this I had to change the SSRS integration configuration in SharePoint in order to grant access to the new service account to the SharePoint farm. You can do this via Central Admin –> General Application Settings –> SQL Server Reporting Services –> Reporting Services Integration
NOTE: In the screenshot above you are not specifying the new account credentials. You are specifying an account that is a member of the Administrators group on the report server. This is so the new credentials that you specified in the Reporting Services Configuration Manager can be granted the appropriate rights.
After changing the SSRS service account you should see something similar to this:
At this point, re-running the upgrade told me there were no components available to upgrade.
Currently…once this problem has occurred, then the only recourse is to uninstall all of the failed SQL components and reinstall them from scratch from the 2012 media.
Being that SQL 2012 Setup has a mechanism to update itself, this might be a good candidate to use that functionally if it gets corrected.
For now, though, be sure that if you are going to perform an in-place upgrade on SSRS in SharePoint integrated mode from 2008 R2 SP1 to 2012, that your SSRS service account is not already a SharePoint managed account.