SharePoint 2013 SSRS: Access Denied Clicking System Settings

After installing SSRS in Integrated Mode with SharePoint 2013, you may find you receive an error when attempting to click on System Settings within the SSRS:

Before you can modify the System Settings of SSRS, you must first set the Execution Account:

Enter the Account and Password of the SSRS Service Account (make sure you also include the DOMAIN name):

After this is set correctly, you should be able to get into the System Settings!
 

SharePoint 2013 SSRS: Report Errors When Attempting to Pass Parameters via URL

I was assisting one of my co-authors and colleagues, Jim Pletscher, with Reporting Services 2012 running on our new SharePoint 2013 Farm (so integrated mode of course using the Reporting Services Service Application). We have two (2) dedicated servers that only run Reporting Services.

One issue was that when running a report we would get a connection issue but after hitting F5 (once or twice) the report would eventually render as if nothing was wrong. Therefore I thought maybe one of the Reporting Services servers was acting funny as when one server was accessed it was fine but when the other server was accessed, things weren't right.

We left that one alone as we were getting a similiar issue consistently when attempting to pass parameters into the report rendering via the URL. Investigating the logs, I found the following:

Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: , Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'REPORTS_DW'. ---> System.IO.FileLoadException: Loading this assembly would produce a different grant set from other instances. (Exception from HRESULT: 0x80131401)

There are tons of posts out there with "System.IO.FileLoadException: Loading this assembly would produce a different grant set from other instances" mostly involving .NET Framework 2.0 and Hotfixes. I was sure it couldn't be that.

I stumbled upon a related post but dealing with Excel Services 2010 here where the guy was saying to restart the services. After goofing my Search Service Application previously by trying to restart services, I only totally reboot servers now. So I was going to do that, however, I noticed in the comments of that post that some other guy said restarting the services did not work and that IISReset did the trick.

I was doubtful but decided to try IISReset first on both servers. I waited for the service to come back and I ran the original report without parameters: that worked fine. Next I clicked on the link in the report that contained the report URL with the parameters - it now worked without any issues!

I am glad it was a simple fix but I was hoping for something a little more complex as there is no explanation of why this would happen. If it happens again I am going to suspect an SSRS app pool issue.


 

SharePoint 2013: SQL Server Reporting Services Service Not Appearing in Services on Server

I previously went through the process of building a Reporting Services Server in SharePoint 2013 Integrated mode which I documented in my post here.

The database team decided they wanted another dedicated Reporting Services Server added to our staging farm. Fine. Sure. No problem. I have the steps documented and we should probably just follow the ones for the Report Server.

So we both did that and I could not see SQL Server Reporting Services Service under Services on Server in SharePoint Central Administration. So I thought something was wrong. I tried several different things and reinstalling/repairing SQL Server 2012 and SQL Server 2012 SP1. I was banging my head.

Then I realized..wait a second...didn't I run some PowerShell commands before?? Yes I did. I just forgot to include them in the original post (which I just updated anyway).

So if you do not see SQL Reporting Services Service running on the Report Server, you need to register and start the SQL Server Reporting Services on the server using PowerShell commands within the SharePoint 2013 Management Shell (on the Report Server):

Install-SPRSService
Install-SPRSServiceProxy

get-spserviceinstance -all |where {$_.TypeName -like "SQL Server Reporting*"} | Start-SPServiceInstance

 

Reporting Services 2012 Integrated Mode with SharePoint 2013 "Scale Out" in Staging Environment

We wanted to test out SQL Server Reporting Services within SharePoint 2013 but did not want to have everything running all on one server. We found some steps here but that essentially described a single server installation. Therefore we needed to figure out what we needed to do which wasn't too hard.

Our staging servers included the following:

Web Front End
Database Server
Reporting Server

The Reporting Server is essentially an Application Server that is joined to the SharePoint farm. It will run the SSRS Service Application. Running SSRS integrated mode now means that it is fully under SharePoint (thus reducing any double-hop issues).

Here is the high-level installation effort we implemented to make it work:

Web Front End

1. Install SP2013 Prereqs (aka SharePoint Preparation Tool)

2. Install SharePoint Server

3. SharePoint Products Configuration Wizard  - (Create new farm)

4. Create Root Web Application (Port 80)

5. Create Root Site Collection

6. Create all Service Applications manually (this was for other purposes - Reporting services was not one them yet)

 

Report Server

1. Install SP2013 Prereqs (aka SharePoint Preparation Tool)

2. Install SharePoint Server

3. SharePoint Products Configuration Wizard (to join to Farm)

4. Install SQL Server 2012 selecting both Reporting Services and the Reporting Services Add-In for SharePoint

5. Install SQL Server 2012 SP1  for SharePoint 2013 Support

You now need to register and start the SQL Server Reporting Services on the server using PowerShell commands within the SharePoint 2013 Management Shell (on the Report Server):

Install-SPRSService
Install-SPRSServiceProxy

get-spserviceinstance -all |where {$_.TypeName -like "SQL Server Reporting*"} | Start-SPServiceInstance

I originally did not have this documented and didn't realize I missed this part until I tried to add another Reporting Services server to the staging farm and the service did not appear in the Services for Server. Ahhh.

 

Back on the Web Front End:

1. Install SQL Server 2012 selecting ONLY the Reporting Services Add-In for SharePoint

2. Install SQL Server 2012 SP1 for SharePoint 2013 Support

 

Now in Central Admin, SQL Server Reporting Services appears as a new Service Application to create:

Create the new SSRS Service Application.

 

The Service Appears under Services on Server (only when looking at the Reporting Server):

 

To add another Report Server to the farm, follow only the steps under the Report Server section.